ORA 00972 error in dynamic sql
Uday_NAug 19 2010 — edited Aug 20 2010The 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 ;