Oracle merge returning into

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.

1. Merge Into doesn’t support Returning Into , so that won’t work.
2. The sequence will not always be used, because it’s only used when inserting new records.
3. 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.

1. 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.
2. 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 ?

Similar Messages:

PL/SQL :: Merge Statement — Delete Clause?

I was reading about merge statement and tried some variations,

create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);

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);
commit;

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.

s@ORCL> ed
Wrote file afiedt.buf

1 declare
2 type tp_dept is table of number;
3 arr_dept tp_dept;
4 begin
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);
8* end;
s@ORCL> /
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 (
C_USER1 CHAR(15),
C_USER9 NUMBER,
C_USER11 NUMBER,
C_USER10 NUMBER,
C_B1 NUMBER(19,2),
C_B2 NUMBER(19,2),
C_BUDGET NUMBER(19,2),
C_USER12 NUMBER(19,2));

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?

DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL(‘46227’)));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

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.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

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
AND source.BNO=target.BNO);

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:

CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,

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
(
id number,
data varchar2(20)
)
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
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;

but this seems to return more than one entrty:

table date below:
drop table employee_list;
drop table employee_historic;
[code].

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

Code excerpt
———————
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

Schema A
—————-
ProcedureA — execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
—————-
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
USING cursortest
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
[code].

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
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3

What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3

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 (
id number(10),
data varchar2(100),
constraint pk_val_00 primary key (id) validate
) organization index;
[code].

SQL & PL/SQL :: Merge And Add Rows Values Into One?

CREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
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
union
select ‘data’ field1, ‘data2’ field2, ect from dual
union

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
BEGIN
declare
v_com varchar2(5000);
BEGIN
V_COM := ‘

when matched then

update set
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)
‘;
FORMS_DDL(V_COM);
end;
END;

SQL & PL/SQL :: Match And Merge Records?

I have some records as below would like to match and merge them

ID1 ID2
101 103
101 104
205 103
206 103
205 106
151 222
157 222
151 223
156 222
134 223
134 435
156 438

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.

DECLARE
p_buid NUMBER;
p_ordernum NUMBER;

SQL & PL/SQL :: Merge Columns Of 2 Tables?

i wanted the query for the followin example

sample1
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

sample2
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)
where wallet=’01710000273′
select * from b (2 column)
where wallet=’01710000273′

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;

(APPLICATION_ID,TABLE_ID,COLUMN_ID,COLUMN_NAME,USER_COLUMN_NAME,COLUMN_SEQUENCE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
COLUMN_TYPE,WIDTH,NULL_ALLOWED_FLAG,TRANSLATE_FLAG,FLEXFIELD_USAGE_CODE) values
(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID,f.COLUMN_NAME,f.USER_COLUMN_NAME,
f.COLUMN_SEQUENCE,f.LAST_UPDATE_DATE,f.LAST_UPDATED_BY,f.CREATION_DATE,f.CREATED_BY,
f.LAST_UPDATE_LOGIN,f.COLUMN_TYPE,f.WIDTH,f.NULL_ALLOWED_FLAG,f.TRANSLATE_FLAG,f.FLEXFIELD_USAGE_CODE);
end loop;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/

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;

EMPID U_EMPID
——————
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003

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;

EMPID U_EMPID
—————————
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003
J1001 NULL
J1002 NULL
J1003 NULL

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.