- 2 Answers 2
- PL/SQL :: Merge Statement — Delete Clause?
- SQL & PL/SQL :: Dynamic SQL Returning Clause Into Collection
- PL/SQL :: Model Clause Returning Phantom Value
- SQL & PL/SQL :: Function Returning A Table Inside Where Clause?
- PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?
- SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause
- SQL & PL/SQL :: OrderBy Clause Before From Clause?
- Merge Operation By ROW-ID?
- SQL & PL/SQL :: Merge Two Table Into One
- SQL & PL/SQL :: Merge Rows
- SQL & PL/SQL :: Returning Old Value During Update?
- SQL & PL/SQL :: Returning Row With Same Values
- PL/SQL :: Returning Same Value Set In A Group
- PL/SQL :: Returning A Single Row
- Merge Over Multiple Schema
- SQL & PL/SQL :: Merge Into Table Name Using Cursor
- SQL & PL/SQL :: Merge Statement In Oracle
- SQL & PL/SQL :: Merge Multiple Rows Into One Row
- SQL & PL/SQL :: Merge Rows On View
- SQL & PL/SQL :: Merge And Add Rows Values Into One?
- SQL & PL/SQL :: Executing Merge Statement?
- Forms :: Using Merge Statement In 6i?
- SQL & PL/SQL :: Match And Merge Records?
- SQL & PL/SQL :: DML Statements — How Does Merge Works
- SQL & PL/SQL :: Merge Is Not Working Properly?
- SQL & PL/SQL :: Merge Columns Of 2 Tables?
- SQL & PL/SQL :: Merge Two Table With Distinct Value
- SQL & PL/SQL :: Using Table Type With Merge?
- SQL & PL/SQL :: Merge Vs Cursor Processing
I’m using Oracle 10g Version
I tried the below code using UPDATE with Returning Clause & MERGE with Returning Clause .
I found NO errors while working with UPDATE statement . The following is the code with UPDATE statement
But getting the error PL/SQL: ORA-00933: SQL command not properly ended when working with MERGE Statement
Please suggest me
- 8282 Просмотров
- Метки: нет (добавить)
1. Re: Returning clause in MERGE statement
Probably because the RETURNING INTO clause doesn’t belong to MERGE statement. It’s available only for INSERT, UPDATE and DELETE. Here is the quote from Oracle Documentation:
The static RETURNING INTO clause belongs to a DELETE , INSERT , or UPDATE statement. The dynamic RETURNING INTO clause belongs to an EXECUTE IMMEDIATE statement.
I want to return the primary key from an oracle merge query. I’m using a single statement to insert if not exist and I don’t want to use procedure or function to do so..
this is the sample query
and I need to get another primary key field of the myTable. the primary key is inserted using sequence and trigger
I tried adding RETURNING empID into :empId but it gives error
2 Answers 2
There’s a problem.
- Merge Into doesn’t support Returning Into , so that won’t work.
- The sequence will not always be used, because it’s only used when inserting new records.
- Getting the existing value of a sequence won’t work, because you get an error if you want to query Sequence.currval when the sequence wasn’t used in the current session yet.
- Use a procedure or anonymous program block to try to update the value. If sql%rowcount return 0 after the update, perform the insert instead.
- Use the selection (query for UPPER(TRIM(name)) ) to find the record that was updated.
You can try this. You will need to declare a package to capture your id, otherwise it will not be visible to the SQL statement and you will get a error:
pls-00231: function name may not be used in SQL
So, first create the package with functions to capture and then later access the ID from the merge statement:
Given a simple table and sequence generator defined as:
You can then use the package in an anonymous block with your merge statement to capture the id and return it. Note that this is a very simple example and it will not work with array-bound variables unless you rework the package to capture the IDs into a table type. If I get a chance, I may try to put together an example that shows this.
Does the Oracle 11g supports ‘RETURNING INTO’ clause in MERGE statement? if it was not available is there any alternate to achieve the same ?
PL/SQL :: Merge Statement — Delete Clause?
I was reading about merge statement and tried some variations,
create table MERGE_TEST(
C2 varchar2(10 char),
insert into MERGE_TEST values(1, ‘Name 3’, 300);
insert into MERGE_TEST values(1, ‘Name 2’, 200);
insert into MERGE_TEST values(1, ‘Name 1’, 100);
why is result different in this querys?
SQL & PL/SQL :: Dynamic SQL Returning Clause Into Collection
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about ‘dynamic sql returning clause into collection’
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC
———- ————— ————-
10 comp NEW YORK
20 Compt DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 Comp Ahaaa
80 data ab
80 data ab
80 data ab
80 data ab
9 rows selected.
Wrote file afiedt.buf
2 type tp_dept is table of number;
3 arr_dept tp_dept;
5 execute immediate q'[‘update t_dept set dname = ‘Pointers’ where deptno = 80 returning deptno into :out]’
6 returning into arr_dept;
7 dbms_output.put_line(‘The count is ‘||arr_dept.count);
returning into arr_dept;
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00597: expression ‘ARR_DEPT’ in the INTO list is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
PL/SQL :: Model Clause Returning Phantom Value
I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.
DROP TABLE MORTGAGE_FACTS
CREATE TABLE mortgage_facts (
INSERT INTO mortgage_facts VALUES (‘Jones’, 131828.81, 3.348, 72, 0, 0, 0, 0)
The first two results look like this:
P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55
As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.
SQL & PL/SQL :: Function Returning A Table Inside Where Clause?
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL(‘46227’)));
MY_PACK.STR2TBL() is a function which takes ‘|’ delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with ‘46227’).
PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause
In the following merge statement in the USINg clause. I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
I thought that using UNIONALL for select statement of the schemas as below.
SQL & PL/SQL :: OrderBy Clause Before From Clause?
can we use something like this
"select . order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
Merge Operation By ROW-ID?
run down of the implications of MERGE by ROWID in such a fashion:
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID’s to change during the execution of this statement — meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on — in cases where you dont have a WHEN NOT MATCHED condition?
SQL & PL/SQL :: Merge Two Table Into One
i HAVE TWO TABLES
NOW I WANT TO MERGE/COMBINE THIS TWO TABLES INTO ONE FLAT TABLE SO MY REPORTS WILL RUN ONLY FROM ONE TABLE INSTEAD OF TWO.
MY DESIRED OUTPUT TABLE WOULD BE
EMP_ID EMPNAME EMPCITY
SQL & PL/SQL :: Merge Rows
Below are the codes
create table testing1
insert all into testing1 values (1,’100′)
into testing1 values (2,’Ram’)
I want the output as
100 Ram Pune
200 Shyam Mumbai
300 Kamal bangalore
SQL & PL/SQL :: Returning Old Value During Update?
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
SQL & PL/SQL :: Returning Row With Same Values
create table testing
( id number (10),
key number (10)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)
My requirement is to return
because both their id and key are same
PL/SQL :: Returning Same Value Set In A Group
I have the below requirement,
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
PL/SQL :: Returning A Single Row
I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee >
select employee_id from
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
group by employee_id;
but this seems to return more than one entrty:
table date below:
drop table employee_list;
drop table employee_historic;
What is the best way to return a single row, if the primary key is not available / applicable.
Merge Over Multiple Schema
Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line . USING (SELECT * FROM B.VIEWB) D
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealer >WHEN MATCHED THEN UPDATE SET
C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fed >C.oldate = D.di_online_dte, .
Permissions given are as follows
ProcedureA — execute on User A
TableA- Insert, Delete, Update, Select on User A
ViewB — Select on User A, Select on UserARole
I believe appropriate permissions have been given.
SQL & PL/SQL :: Merge Into Table Name Using Cursor
I am trying to merge into test table using a cursor. If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table.
MERGE INTO test
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
SQL & PL/SQL :: Merge Statement In Oracle
how to update or insert another (third table ) table with merge statement
SQL & PL/SQL :: Merge Multiple Rows Into One Row
How am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus.
My current result set:
user id|user name|group name
What i wish to achieve:
user id|user name|group name
SQL & PL/SQL :: Merge Rows On View
I have problem with merging rows into view. I have created "INSTEAD OF" triggers on the view for insert/delete/update of rows. However, the merge is not working and it needs rowid to work. Is there any way how to make MERGE working here ? I must use merge, because I am changing the structure of database used for large java application. And it uses tons of merge commands,changing them to insert/update is inefficient because of development-time and of course resulting execution-time.
create table val_00 (
constraint pk_val_00 primary key (id) validate
) organization index;
SQL & PL/SQL :: Merge And Add Rows Values Into One?
CREATE TABLE emp
dept number (3)
9 rows selected.
Required Output is
CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
———- ————— ———- ———- ——— ———- ————
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176
Merge 02 rows of same department having sum(salary)
SQL & PL/SQL :: Executing Merge Statement?
how to use the MERGE Statement. actually I’ve used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select ‘data’ field1, ‘data2’ field2, ect from dual
select ‘data’ field1, ‘data2’ field2, ect from dual
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
Forms :: Using Merge Statement In 6i?
in Toad I am using this Merge statement , working accordingly.I want to use this merge statement in my form where can I use it.
I created a Procedure in Form Named Proc_merge and call it when validate item of :bankcode
PROCEDURE proc_merge IS
V_COM := ‘
when matched then
trg.v_chq = src.cheque_no, trg.v_bankcode = src.bank_code ,trg.v_debit=src.debit, trg.v_credit=src.credit, trg.v_narration=src.narration
when not matched then
insert (trg.v_type, trg.v_no, trg.v_date, trg.v_chq, trg.v_bankcode, trg.v_debit, trg.v_credit, trg.v_narration)
values (src.voucher_type, src.voucher_no, src.voucher_date, src.cheque_no, src.bank_code, src.debit, src.credit, src.narration)
SQL & PL/SQL :: Match And Merge Records?
I have some records as below would like to match and merge them
Output should be. All matching records should be grouped together and generate result by new seq
ID1 ID2 Result
101 103 1
101 104 1
205 103 1
206 103 1
205 106 1
151 222 2
157 222 2
151 223 2
156 222 2
134 223 2
134 435 2
156 438 2
SQL & PL/SQL :: DML Statements — How Does Merge Works
My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
SQL & PL/SQL :: Merge Is Not Working Properly?
I am using the following merge statement it’s not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
SQL & PL/SQL :: Merge Columns Of 2 Tables?
i wanted the query for the followin example
A B C D E F
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5
G H I J
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
consider the above tables what i want is some thing like this
this can also be done through a select statement through choosing the columns which you want i don want thatbut what i want is columns of entire table sample2 and only one column in sample1
SQL & PL/SQL :: Merge Two Table With Distinct Value
select * from a (6 column)
select * from b (2 column)
when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?
SQL & PL/SQL :: Using Table Type With Merge?
Can i use table type in the using clause of merge statement ?
Attached is the code of the procedure
create or replace procedure fnd_sample as
cursor fnd_c is select * from fnd_columns;
type test_t is table of fnd_columns%rowtype;
when others then
I am getting an error saying that
/19 PL/SQL: SQL Statement ignored
8/60 PL/SQL: ORA-00942: table or view does not exist
SQL & PL/SQL :: Merge Vs Cursor Processing
My requirement is ..
SQL> CREATE TABLE EMP (EMPID VARCHAR2(100), U_EMPID VARCHAR2(100));
SQL> INSERT INTO EMP VALUES (‘DUMMY1′,’J0001’);
SQL> INSERT INTO EMP VALUES (‘DUMMY2′,’J1002’);
SQL> INSERT INTO EMP VALUES (‘DUMMY3′,’J1003’);
SQL> SELECT * FROM EMP;
in this example empid is dummy empid (which will be issued for contractors), once the contractors are considered to be taken into payroll that u_empid will be updated by HR manually, we need to replace empid with u_empid. then data will become like this.
SQL> SELECT * FROM EMP;
now my requirement is there are approximately 60 child tables are there for EMP table, and EMPID is the foreign key for all of them with "on delete cascade" defined on those. Now first i need to create records in all the child tables with actual employee id’s (which is u_empid). if the actual id is already exists then just need to update rest of the columns in that table except the EMPID.
Once the records are created with actual empid, then i will issue a delete command to remove dummy employee id’s in emp table which will take care of deletion in child tables as well.
I was thinking of two options, one is go with Merge other is write a cursor and handle the update in exception block using when duplicate value on index feature.