RETURNING and RETURNING INTO Clause with EXECUTE IMMEDIATE

 
EXECUTE IMMEDIATE dynamic_query
[INTO user_defined_variable_1, user_defined_variable_2, ..]
[USING bind_argument_1, bind_argument_2, ..]
[RETURNING|RETURN-INTO clause];
 
 
CASE_1 : NO Value Returning
CASE_2 : Returning Single Row with Single Column Value
CASE_3 : Returning Single Row with More Than One Column Value
CASE_4 : Returning Multiple Row with Single Column Value
CASE_5 : Returning Multiple Row with More Than One Column Value
 
 
--===================================================   
• If the dynamic SQL statement is a "DML statement" without a "RETURNING INTO" clause,
other than SELECT, put all bind variables in the "USING" clause.
--===================================================
 
DROP Sequence XX_ROLL_No
 
DROP TABLE XX_TEMP_TL
 
Create Sequence XX_ROLL_No Start with 1000
 
create table XX_TEMP_TL (
Roll_NO  NUMBER (4),
SUB_1     NUMBER (3),
SUB_2     NUMBER (3),
SUB_3     NUMBER (3),
SUB_4     NUMBER (3)
 )
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CASE_1 : NO Value Returning
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
DECLARE
V_SUB_1 NUMBER := 10;
V_SUB_2 NUMBER := 20;
V_SUB_3 NUMBER := 30;
V_SUB_4 NUMBER := 40;
 
V_SQL VARCHAR2(500);
 
BEGIN
V_SQL:= 'INSERT INTO  XX_TEMP_TL VALUES (XX_ROLL_No.NEXTVAL,:x, :x, :x, :x)';
 
EXECUTE IMMEDIATE V_SQL USING V_SUB_1, V_SUB_2, V_SUB_3, V_SUB_4 ;
 
dbms_output.put_line ('total no of rows inserted with correct subject Marks is : '|| SQL%ROWCOUNT );
END;
 
 
select * from XX_TEMP_TL
 
 
--===================================================
• If the dynamic SQL statement is a "DML statement" with a "RETURNING INTO" clause,
put "in-bind variables" in the "USING" clause and "out-bind variables" in the "RETURNING INTO" clause.
--===================================================
 
TRUNCATE  TABLE XX_TEMP_TL
 
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CASE_2 : Returning Single Row with Single Column Value
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
DECLARE
V_SUB_1 NUMBER := 10;
V_SUB_2 NUMBER := 20;
V_SUB_3 NUMBER := 30;
V_SUB_4 NUMBER := 40;
 
V_RET_ROLL_NO NUMBER;
 
V_SQL VARCHAR2(500);
 
BEGIN
V_SQL:= 'INSERT INTO  XX_TEMP_TL VALUES (XX_ROLL_No.NEXTVAL,:x, :x, :x, :x) RETURNING ROLL_NO INTO :y';
 
EXECUTE IMMEDIATE V_SQL USING V_SUB_1, V_SUB_2, V_SUB_3, V_SUB_4 RETURNING  INTO V_RET_ROLL_NO;
 
dbms_output.put_line ('Roll No : '||V_RET_ROLL_NO||' | and total no of rows
 inserted with correct subject Marks is : '|| SQL%ROWCOUNT );
END;
 
 
select * from XX_TEMP_TL
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CASE_3 : Returning Single Row with More Than One Column Value
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
TRUNCATE  TABLE XX_TEMP_TL
 
 
DECLARE
V_SUB_1 NUMBER := 10;
V_SUB_2 NUMBER := 20;
V_SUB_3 NUMBER := 30;
V_SUB_4 NUMBER := 40;
 
V_RET_ROLL_NO NUMBER;
V_RET_SUB_1 NUMBER;
V_RET_SUB_2 NUMBER;
V_RET_SUB_3 NUMBER;
V_RET_SUB_4 NUMBER;
 
V_SQL VARCHAR2(500);
 
BEGIN
V_SQL:= 'INSERT INTO  XX_TEMP_TL VALUES (XX_ROLL_No.NEXTVAL,:x, :x, :x, :x)
               RETURNING ROLL_NO, SUB_1, SUB_2, SUB_3, SUB_4 INTO :y, :y, :y, :y, :y';
 
EXECUTE IMMEDIATE V_SQL USING V_SUB_1, V_SUB_2, V_SUB_3, V_SUB_4
RETURNING  INTO V_RET_ROLL_NO, V_RET_SUB_1,V_RET_SUB_2,V_RET_SUB_3,V_RET_SUB_4;
 
dbms_output.put_line ('Roll No : '||V_RET_ROLL_NO||' | and total no of rows inserted with correct subject Marks is : '|| SQL%ROWCOUNT );
dbms_output.put_line ('subject-1 Marks is : '|| V_RET_SUB_1 );
dbms_output.put_line ('subject-2 Marks is : '|| V_RET_SUB_2 );
dbms_output.put_line ('subject-3 Marks is : '|| V_RET_SUB_3 );
dbms_output.put_line ('subject-4 Marks is : '|| V_RET_SUB_4 );
END;
 
--======================================================================================
 
INSERT INTO  XX_TEMP_TL VALUES (XX_ROLL_No.NEXTVAL,:x, :x, :x, :x)
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CASE_4 : Returning Multiple Row with Single Column Value
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
select * from XX_TEMP_TL
 
DECLARE
 
TYPE NT_ROLL_NO IS TABLE OF XX_TEMP_TL.ROLL_NO%TYPE;
 
V_RET_ROLL_NO NT_ROLL_NO;
 
V_NEW_SUB_1 NUMBER := 90;
 
V_SQL VARCHAR2(500);
 
BEGIN
V_SQL:= 'UPDATE  XX_TEMP_TL SET SUB_1 = :x RETURNING ROLL_NO INTO :y';
 
EXECUTE IMMEDIATE V_SQL USING V_NEW_SUB_1 RETURNING  BULK COLLECT INTO V_RET_ROLL_NO;
 
dbms_output.put_line ('total no of rows affected are : '|| SQL%ROWCOUNT );
 
 FOR i IN V_RET_ROLL_NO.first .. V_RET_ROLL_NO.last LOOP
    DBMS_OUTPUT.put_line('Roll No =' || V_RET_ROLL_NO(i)||'  Marks Updated');
  END LOOP;
 
END;
 
 
select * from XX_TEMP_TL
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
CASE_5 : Returning Multiple Row with More Than One Column Value
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 
DECLARE
 
TYPE NT_ROLL_NO IS TABLE OF XX_TEMP_TL.ROLL_NO%TYPE;
 
V_RET_ROLL_NO NT_ROLL_NO;
 
TYPE NT_SUB_2 IS TABLE OF XX_TEMP_TL.SUB_2%TYPE;
 
V_RET_SUB_2  NT_SUB_2;
 
V_NEW_SUB_1 NUMBER := 100;
 
V_SQL VARCHAR2(500);
 
BEGIN
V_SQL:= 'UPDATE  XX_TEMP_TL SET SUB_1 = :x RETURNING ROLL_NO, SUB_2 INTO :y, :z';
 
EXECUTE IMMEDIATE V_SQL USING V_NEW_SUB_1 RETURNING  BULK COLLECT INTO V_RET_ROLL_NO, V_RET_SUB_2;
 
dbms_output.put_line ('total no of rows affected are : '|| SQL%ROWCOUNT );
 
 FOR i IN V_RET_ROLL_NO.first .. V_RET_ROLL_NO.last LOOP
    DBMS_OUTPUT.put_line('Roll No =' || V_RET_ROLL_NO(i)||'  Sub-2 Marks is = '||V_RET_SUB_2(i));
  END LOOP;
 
END;
 
 
 
 
 
 
x

Comments

Post a Comment

Popular posts from this blog

E-Text Report In Fusion | Types of E-Text reports

 WHAT IS Oracle Fusion E-Text Report ? An eText template is an RTF-based template that is used to generate text output  for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). How it generate the output for User or Bank? At runtime, BI Publisher applies this template to an input XML data file to create an output text file. that can be transmitted to a bank or other customer.  Because the output is intended for electronic communication, the eText templates must follow very specific format instructions for exact placement of data. Example :- Bank payment file , Bank MT940 File .. etc what is look and feel of this file ? Note:  There are two types of eText templates:  1. fixed-position based (EFT templates)  2. Delimiter-based (EDI templates) What IS EFT File? An EFT is an electronic transmission of financial data and payments to banks in a specific fixed-position format flat file (text). Link for download:- EFT :-  https://docs.google....
Read more

Supplier API's

API's for Create Supplier, Create Supplier Site, Create Supplier Site Contacts --======================================================================= -------------Supplier API ------------- 1. First Write a CURSOR for Required Data 2. FND_GLOBAL.APPS_INITIALIZE 3. Pass required Values in Proper way. 4. API :- For Supplier Create (pos_vendor_pub_pkg.create_vendor)    API :- For Supplier Site Create (pos_vendor_pub_pkg.create_vendor_site)    API :- For Supplier Create Contact (ap_vendor_pub_pkg.create_vendor_contact) 5. Run API 6. Checking ------------------------------  Query For APPS_INITIALIZE   --------------------------------------------- select 'USER_ID' REMARKS , user_name NAME, user_id ID from fnd_user where user_name like 'DEVELOPER' union select 'RESPONSIBILITY_ID' NAME, responsibility_name , RESPONSIBILITY_ID from fnd_responsibility_tl where responsibility_name like 'Payables Manager' union s...
Read more

How to pass default Parameter in cursor

--==================================================== --Parameterized Cursors /* Syntax of Parameterized Cursor :- CURSOR cur _ name (parameter list may be one, two, or more or Default ) IS SELECT statement; OPEN cur _ name (Parameter value) */ EMPNO ENAME HIREDATE DOB GENDER SAL DEPTNO COMM 1000 Sachin 11-Sep-85 01-Mar-20 M 25000 20 1002 Rena 01-Aug-82 11-Mar-20 F 125000 20 1008 Sonu 11-Sep-85 01-Mar-20 M 2500 10 100 1003 Chavi 25-Mar-81 15-Feb-20 F 5000 20 1006 Ravi 11-Sep-89 01-Mar-19 M 2500 10 100 1005 Vinod 18-Sep-86 01-Mar-20 ...
Read more