Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA 00972 error in dynamic sql

Uday_NAug 19 2010 — edited Aug 20 2010
The following is my code...I got THE FOLLOWING ERROR

'ORA-00972: identifier is too long
ORA-06512: at "VVMS_BATCH.QUEUE_DYNAMIC", line 26
ORA-06512: at line 2'

Code:

create or replace
procedure sales_dynamic is
cursor cur_sales is
select sales_CATEGORY_ID,sales_ITEM_STATUS,sales_ITEM_KEY,sales_ID from sales_ITEM_HISTORY where sales_ITEM_STATUS = 'OH' and ONHOLD_DT < sysdate;
/*v_err_code number;
v_err_msg varchar2(64);*/

type v_source_table is table of sales_category.source_table%type;
z_v_source_table v_source_table := v_source_table();
stmt long(2000);
v_ot varchar2(2);

begin
for i in cur_sales loop
begin
v_ot := 'OT';
select source_table bulk collect into z_v_source_table from sales_category where sales_CATEGORY_ID = i.sales_CATEGORY_ID;

for j in z_v_source_table.first..z_v_source_table.last loop

stmt :='begin'||'update'|| z_v_source_table(j)||q'( set sales_ITEM_STATUS = ('OT') where sales_ITEM_KEY=i.sales_ITEM_KEY)';
execute immediate stmt USING z_v_source_table(j),i.sales_ITEM_KEY;
dbms_output.put_line('executing'|| z_v_source_table(j));
end loop;
end;
end loop;
end sales_dynamic ;
This post has been answered by Warren Tolentino on Aug 19 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2010
Added on Aug 19 2010
26 comments
2,693 views