Another help
Neeraj Shukla, April 27, 2017 - 3:45 pm UTC
Thanks for help answer is helpful though I am not looking for PL/SQL block but a simple update statement which can return a value
April 27, 2017 - 11:39 pm UTC
That is what has been demonstrated - we simply used PLSQL as a wrapper to define a variable into which we returned the value
A reader, March 27, 2020 - 1:55 pm UTC
Seema, March 27, 2020 - 1:58 pm UTC
I have a question, I have a update statement and which is dynamic sometimes there are scenario when it's not updating a single row also.
In that case this return statement is throwing error:
How can we handle nulls if there is not a single update of rows??
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 20
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
March 27, 2020 - 4:43 pm UTC
What exactly is the code you're running? Test case please!
Seema, March 29, 2020 - 3:08 pm UTC
Thanks for coming back!!.
My scenarios is exactly same as above:
declare
ids dbms_sql.number_table;
begin
update t
set y = 2
where y = 100
return x
bulk collect into ids;
for i in ids.first .. ids.last loop
dbms_output.put_line('Updated: ' || ids(i));
end loop;
end;
Only thing is my update statement is trying to update a row Y=100 (which doesnot exist in table) so it will return NULL .
and then the 'for loop' was throwing error, I could fix that by using COUNT.
if ids.count > 0
for i in ids.first .. ids.last loop
dbms_output.put_line('Updated: ' || ids(i));
end loop;
end if;
Thanks for your above post, it really helped me :)
Have a nice day!!
March 30, 2020 - 12:20 am UTC
Glad we could help
Seema, March 30, 2020 - 4:08 pm UTC
Same code I am trying in dynamic statement and it is not working while returning the ID.
I am trying something like,
v_sql := 'update MyTable
set <my col updates>
where <my conditions>
||v_cond
|| 'return id bulk collect into MyIDList ';
Kindly help on this.
EXECUTE IMMEDIATE v_sql ;
March 31, 2020 - 1:38 am UTC
The id's you are returning are not part of the dynamic part of the SQL, so it would be:
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;
/
Seema, March 31, 2020 - 6:43 am UTC
Thank You so much!!!!
I am able to execute the dynamic SQL with the above returning statement.
Thanks a lot again for you quick help.
March 31, 2020 - 8:39 am UTC
Great to hear.
Seema, June 03, 2020 - 2:33 pm UTC
Hi Tom,
Again need your help, my code was running with BULK collect into, but sometime it's throwing ORACLE internal error during to BULT returning I guess.
In dynamic SQL whatever rows I m updating, I need 'ID' of that rows. the ID I use to insert in other table.
declare
v_sql_m varchar2(4000);
id_v1 dbms_sql.number_table;
V_COUNT number:=0;
begin
v_sql_m := 'update /*+ parallel(8) index(TEST TEST_INDEX)*/ ABC_OWNER.TEST
set
ID_REQUIREMENT = null,
ASSESS_VALUE = null,
MODIFIED_BY =USER,
MODIFIED_TS =systimestamp,
REASON =''automatic re-assessment''
where studyid = 11111
and CONFIRM_YN =''Y''
and TYPE =''M''
and dml_action = ''NEW VALUE'''
|| ' return id into :list_abc ';
--DBMS_OUTPUT.PUT_LINE(v_sql_m);
EXECUTE IMMEDIATE v_sql_m RETURNING BULK COLLECT INTO id_v1 ;
V_COUNT := sql%ROWCOUNT;
if id_v1.count > 0 then
for i in id_v1.first .. id_v1.last loop
INSERT INTO ABC_OWNER.TEST_COMMENTS (STUDYID,Comm_ID,DESCRIPTION,CREATED_BY,COM_TYPE_ID,MODIFIED_BY)
VALUES (11111,id_v1(i),'automatic background update',USER,20,USER);
END LOOP;
end if;
end;
This code runs fine but sometime I get below error:
Connecting to the database .
ORA-00600: internal error code, arguments: [13030], [20], [], [], [], [], [], [], [], [], [], []
Process exited.
Disconnecting from the database .
Can you please let me know what is the reason and fix for this??
The same rows which are part of above SQL, I could able to update without returning clause.
June 04, 2020 - 7:36 am UTC
ORA-600 => Contact support! This is an internal error; you need their help to fix it.
That said, I don't see why you need to use executed immediate here.
You should be able to run the update as static SQL:
update /*+ parallel(8) index(TEST TEST_INDEX)*/ ABC_OWNER.TEST
set ID_REQUIREMENT = null,
ASSESS_VALUE = null,
MODIFIED_BY =USER,
MODIFIED_TS =systimestamp,
REASON ='automatic re-assessment'
where studyid = 11111
and CONFIRM_YN ='Y'
and TYPE ='M'
and dml_action = 'NEW VALUE'
RETURNING id BULK COLLECT INTO id_v1 ;
Which may resolve the issue.
seema, June 04, 2020 - 10:59 am UTC
Hi,
As you said, I have written a simple update instead of dynamic and this resolved my issue.
But in my package I have this this kind of dynamic SQL two more times, and the condition in the where clause is not direct like the given one, it's dynamically built. so I cant convert that in simple update. I need the ID of the update so doing bulk return too. Currently other two are running fine. but in future if those two errors out then I will have problem.
Shall I contact support for this ORA-00600??
June 04, 2020 - 12:23 pm UTC
If you have ORA-600s, (and no obvious ways to solve it) you need to work with support to resolve it.