This content has been marked as final. Show 8 replies
Hope the sample help you...
DECLARE TYPE NameList IS TABLE OF VARCHAR2(15); enames NameList; bonus_amt NUMBER := 50; sql_stmt VARCHAR(200); BEGIN sql_stmt := 'UPDATE employees SET salary = salary + :1 RETURNING last_name INTO :2'; EXECUTE IMMEDIATE sql_stmt USING bonus_amt RETURNING BULK COLLECT INTO enames; END; /
There is no reason to use dynamic update statements. Why can't you simply use it as is;
This is an example using my own table:
SQL> select empno,sal,job from emp2 where job like 'A%'; 4444 2750 AUTHOR 3333 2200 ANALYST SQL> declare 2 3 type empid_tb is table of number; 4 empid_list empid_tb; 5 6 begin 7 8 update Emp2 9 set Sal = Sal * 1.1 10 where Job like 'A%' 11 returning empno bulk collect into empid_list; 12 13 14 dbms_output.put_line(empid_list.count); 15 16 end; 17 / 2 SQL> commit; SQL> select empno,sal,job from emp2 where job like 'A%'; 4444 3025 AUTHOR 3333 2420 ANALYST
declare v_ename_list sys.OdciVarchar2List; begin execute immediate 'update emp set ename = ename || ''X'' returning ename into :1' returning bulk collect into v_ename_list; for i in 1..v_ename_list.count loop dbms_output.put_line(v_ename_list(i)); end loop; end; / SMITHX ALLENX WARDX JONESX MARTINX BLAKEX CLARKX SCOTTX KINGX TURNERX ADAMSX JAMESX FORDX MILLERX PL/SQL procedure successfully completed. SQL>
Table name is known at the run-time
yes you can use your query with execute immediate.
See below for example
Note: you can also define emp_id dynamically.
create or replace procedure my_proc(p_table_name IN VARCHAR2) is .... begin ... ... ... sql_stmt := 'update :1 set salary = salary *1.1 where department_id = 100 returning employee_id BULK COLLECT into empid_list' execute immediate sql_stmt using p_table_name; .. .. end;
yep, I see now that I've re-read the question, the example given by yourself and Solomon does not however have a table parameter!
So I guess I got a bit confused!!!!
Apologies to all of you, I was kind of rush up when I post the question. I mean table name is known at runtime only.
Dudeandy, your example looks good so I'm gonna try it now. Thanks.