I've written a SP and it refuses to work with the CONCAT function as documented. >>Yes I have searched the web and other locales to no avail. Create Procedure
`SP_CODE`(IN search_term VarChar(200))begin if search_term is not null then begin
Select b.s_term, a.O_Number, a.S_CODE, a.LONG_DESC From JKC a INNER JOIN search_terms b ON a.S_CODE = b.S_CODE Where b.s_term like CONCAT ('%', search_term, '%'); end;end;
The editor believes that the problem is at CONCAT('%', search_term, '%');
insert all into concat_test (empno,empname,description) values ('1','pratik','want') into concat_test (empno,empname,description) values ('1','pratik','to') into concat_test (empno,empname,description) values ('1','pratik','concat') select * from dual;
I am getting numeric or value error when concatenating sql script..
Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)
V_SAMPLEQUERY VARCHAR2(2000);
firstpart:_
V_SAMPLEQUERY := ' SELECT AB.ABCDID AS ABCDID, CD.MEMBERID AS MEMBERID,
[Code]..
secondpart:_ ***ERROR PART***
V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual; --Output H S
Select Concat('H ',' S') A From Dual; --Output H S
Select Length('H '||' S') A From Dual; --Output 6
Select Length(Concat('H ',' S')) A From Dual; --Output 6
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type
I'm trying to concatenate a local phone number field. The LDAP system only has the last 5 digits but for the directory database we need all 7 digits.I've tried every combination I can think of to get the concatenation to work but every combination results in just the first two digits being imported, e.g.,
LOCAL_NUM "'20'||:local_num",
results in just 20 being imported. Every iteration I've tried that didn't result in an error imported only the 20 and ignored the ||. I've also tried calling the CONCAT directly, e.g.,
LOCAL_NUM "CONCAT('20', :local_num)",
result is the same.The problem seems to be that the loader is ignoring the concatenate statement all together. I've tried the statements outside of the loader via sqlplus with expected result so I'm confused as to why it's not working within the loader.
declare a clob; b varchar2; c clob; begin c:=a||b; end;
When execute above code in form runtime, I get error ora-32767.
"ORA-29287: invalid maximum line size Cause: An invalid maximum line size value was specified. Action: Correct the maximum line size to be in the range [1, 32767]."
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on to_char(partner_user.partner_userid,'9999')=membership.created_by select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
I'm having some minor trouble with my sqlplus in Windows. Don't know which feature I accidentally edited by cosole seems to be in debug mode. Everything is ok, except that it print the old and new values of my variables. How can't I restore it to it's original behavior.
I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day. Attendance :- To record daily attendance. Leave_appovd : To record the approved leaves. Holiday : To record the holidays.
W = Attendance R = Weekly Rest L = Leave H = Holiday A = Absent
I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.
CREATE OR REPLACE PROCEDURE deepa.myddl (p_ddl IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_ddl;
[Code].....
This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema
like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.
SELECT sno,mid,mname FROM manage_date WHERE mname IN ('KIRAN-KUMAR', 'RAHUL-RAJ', 'KAUSHAL-SONI');
IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)
If I pass one name 'KIRAN-KUMAR' from .net It's working.
If I pass multiple names from .net query not returning any records.
I went through the link given in orafaq and tried to work on my system but the things did not work.
SQL> select * 2 from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
SQL> ALTER SYSTEM SET recyclebin = ON;
System altered.
SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
VALUE -------------------------------------------------------------------------------- ON
SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);
Table created.
SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SQL> SQL> DROP TABLE TEST_RBIN;
Table dropped.
SQL> SHOW RECYCLEBIN; SQL> SELECT * FROM USER_RECYCLEBIN;
I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.
But it throws exception that database instance is unavailable?
I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.
Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.
On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.
I tried to insert date in date column from another table date column. Below is SP ans execute procedure.
CREATE OR REPLACE PROCEDURE test( start_time Varchar ) AS BEGIN DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE(start_time); Insert into test_ora (report_date) (select start_time from
following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Here's a simplified version of the query I am having problems with:
SELECT assoc.association_id FROM mentor_initiative mi LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1 LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)
Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.
In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)
SELECT assoc.association_id FROM mentor_initiative mi LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1 LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)
I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?
CODEcreate or replace trigger admin after insert on admin for each row declare bang varchar2(250):='ADMIN';
[code]...
i will try with select :new.i.column_name from dual, but not run (because i can't determination exactly column name thus i must use column_name variable)
I am developing a form using Forms [32 Bit] Version 6.0.8.11.3 (Production). In my custom form I have given a Push_Button and my idea is to invoke a File open diaglog box when I hit the button.
So, i wrote the below code under 'when-button pressed ' triger. But, the button is not invoking the File Open diaglog box in TEST environment.
i have this procedure in my form to make some checks on employee data:
Procedure Pu_Check_Emp_Positions_Prc Is Cursor vc_get_users_data_case1 Is Select A.User_No, A.User_Responsility, A.User_Position, B.User_Status From Import_Emp_Positions_Dtl A, Shows_Users B Where A.User_No = B.User_No And (Substr(A.User_Position, 1, 1) = 'R' Or Substr(A.User_Position, 1, 1) = 'r') And A.User_Responsility Is Not Null And B.User_Status In (2, 3, 4, 7, 8); --And A.User_No = 'XSER0001'; [code]....
i need to run this progress bar and finished when the procedure finish.
