i trying execute below procedure says me use bulkcollect , forall refactor code. warning or should not use below procedure. if so, how should modify code iterating through array , need each value update table.
create or replace procedure schema.pr_validate ( filearray in strarray, duparray out strarray, passarray out strarray, failarray out strarray, missarray out strarray ) dupcount number; stacode varchar2 (10); filename varchar2 (50); dupfilename varchar2(50); fileid number; id varchar2(20); begin in 1 .. filearray.count loop filename := filearray(i); select count (t.fileid), t.fileid,t.status,t.id dupcount,fileid,stacode,id tb_table_name t, tb_table_name s t.fileid=s.fileid , t.status=s.status , t.filename = filename , t.status in ('pass', 'fail) group t.fileid,t.status,t.id; if dupcount>1 select filename dupfilename tb_table_1 status in ('pass','fail') , fileid=fileid , filename != filename; dbms_output.put_line(dupfilename); update tb_table_1 set status='dup' filename=dupfilename; duparray(duparray.last +1 ) :=dupfilename; dbms_output.put_line(dupfilename); end if; if stacode = 'fail' failarray(failarray.last+1) :=filename; elsif stacode = 'pass' passarray(passarray.last+1) :=filename; end if; end loop; exception when no_data_found stacode :=null; missarray(missarray.last +1 ) :=filename; when others pr_errors('pr_validate', sqlerrm); rollback; end; /
as mentioned in comment forall here out of context business logic incorporated here again can try bulk collect loop iterate. data volume not large can still go row-by-row approach. hope below snippet helps. there single quotes missing have given compile time error.
create or replace procedure schema.pr_validate( filearray in strarray, duparray out strarray, passarray out strarray, failarray out strarray, missarray out strarray ) dupcount number; stacode varchar2 (10); filename varchar2 (50); dupfilename varchar2(50); fileid number; id varchar2(20); begin in 1 .. filearray.count loop filename := filearray(i); select count (t.fileid), t.fileid, t.status, t.id dupcount, fileid, stacode, id tb_table_name t, tb_table_name s t.fileid =s.fileid , t.status =s.status , t.filename = filename , t.status in ('pass', 'fail') group t.fileid, t.status, t.id; if dupcount>1 select filename dupfilename tb_table_1 status in ('pass','fail') , fileid =fileid , filename != filename; dbms_output.put_line(dupfilename); update tb_table_1 set status='dup' filename=dupfilename; duparray(duparray.last +1 ) :=dupfilename; dbms_output.put_line(dupfilename); end if; if stacode = 'fail' failarray(failarray.last+1) :=filename; elsif stacode = 'pass' passarray(passarray.last+1) :=filename; end if; end loop; exception when no_data_found stacode :=null; missarray(missarray.last +1 ) :=filename; when others pr_errors('pr_validate', sqlerrm); rollback; end; 