How to Optimize PL/SQL code using Bulk collect/FORALL?

Question

pl/sql code taking too much time to remove characters in all tables

begin
  for i in ( select TABLE_NAME,COLUMN_NAME  from all_tab_columns
              where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%' and table_name not like 'BIN%' and data_type ='VARCHAR2'
                and column_name not like '%RR_NO%' and column_name not like '%RRNO%' order by 5 desc  )
  loop
    execute immediate  'UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || '= REPLACE (' ||i.COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') WHERE INSTR('||i.COLUMN_NAME ||',CHR(10))>0';
    execute immediate  'UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || '= REPLACE (' ||i.COLUMN_NAME ||',CHR(13),'||chr(39)||chr(39)|| ') WHERE INSTR('||i.COLUMN_NAME ||',CHR(13))>0';
    dbms_output.put_line(i.TABLE_NAME||' - '||i.COLUMN_NAME||'- '||sql%rowcount);
  end loop;
  commit;
end;

pls help me by writing using Bulk collect/FORALL

Right now i am doing Manually using

select ' UPDATE '|| TABLE_NAME || ' SET ' ||COLUMN_NAME || '= REPLACE (' ||COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') WHERE INSTR('||COLUMN_NAME ||',CHR(10))>0;'
   from all_tab_columns
   where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%' and table_name not like 'BIN%'
   and data_type ='VARCHAR2' and column_name not like '%RR_NO%' and column_name not like '%RRNO%';

select ' UPDATE '|| TABLE_NAME || ' SET ' ||COLUMN_NAME || '= REPLACE (' ||COLUMN_NAME ||',CHR(13),'||chr(39)||chr(39)|| ') WHERE INSTR('||COLUMN_NAME ||',CHR(13))>0;'
   from all_tab_columns
   where owner='BILL' and data_length > 1 and table_name not like 'SYS_EXPORT_SCHEMA%'
   and table_name not like 'BIN%' and data_type ='VARCHAR2' and column_name not like '%RR_NO%' and column_name not like '%RRNO%';

copying this SQL results and executing manually.

it was taking more than 1 hour to run scripts.

DECLARE
   CURSOR c1 IS SELECT TABLE_NAME,COLUMN_NAME  FROM ALL_TAB_COLUMNS
                    WHERE OWNER='BILL' AND DATA_LENGTH > 1 AND TABLE_NAME NOT LIKE 'SYS_EXPORT_SCHEMA%' AND TABLE_NAME NOT LIKE 'BIN%' AND DATA_TYPE ='VARCHAR2'
                    AND COLUMN_NAME NOT LIKE '%RR_NO%' AND COLUMN_NAME NOT LIKE '%RRNO%';
        TYPE RecList IS TABLE OF c1%ROWTYPE;
        recs RecList;
BEGIN
    BEGIN
        OPEN c1;
            LOOP
                FETCH c1 BULK COLLECT INTO recs LIMIT 500;
                EXIT WHEN c1%NOTFOUND;
                    FOR i IN recs.FIRST .. recs.LAST
                        LOOP
                            EXECUTE IMMEDIATE  'UPDATE '|| recs(i).TABLE_NAME || ' SET ' ||recs(i).COLUMN_NAME || '= REPLACE (' ||recs(i).COLUMN_NAME ||',CHR(10),'||CHR(39)||CHR(39)|| ') WHERE INSTR('||recs(i).COLUMN_NAME ||',CHR(10))>0';
                            dbms_output.put_line(recs(i).TABLE_NAME||'  -  '||recs(i).COLUMN_NAME||'  -  '||sql%rowcount);
                            EXECUTE IMMEDIATE  'UPDATE '|| recs(i).TABLE_NAME || ' SET ' ||recs(i).COLUMN_NAME || '= REPLACE (' ||recs(i).COLUMN_NAME ||',CHR(13),'||CHR(39)||CHR(39)|| ') WHERE INSTR('||recs(i).COLUMN_NAME ||',CHR(13))>0';
                            dbms_output.put_line(recs(i).TABLE_NAME||'  -  '||recs(i).COLUMN_NAME||'  -  '||sql%rowcount);
                        END LOOP;
            END LOOP;
            exception when others then
            dbms_output.put_line(sqlcode||sqlerrm);
        CLOSE C1;
    END;
commit;
END;
/

i written one query Is's working,Is there any tips to optimize this query further.

its working but taking too much time. i want to speed up the querie. present its taking more than 1 hour. — Gowtham Pranavesh Naidu, Aug 02 '18 at 07:47
have you try to read documantion about `forall` and `bulk collect` ? have you tried to use it? — hotfix, Aug 02 '18 at 07:51
i am reading documentation and viewing some you tube videos. still not tried any code. i have to try. i am new to stack over flow i want to know how this will work. — Gowtham Pranavesh Naidu, Aug 02 '18 at 07:54
written one query,its working fine check that any further ways to optimize or any tips? — Gowtham Pranavesh Naidu, Aug 03 '18 at 06:22
@gowthampranaveshnaidu as i told you before prepare the statement then add them in the update — Moudiz, Aug 03 '18 at 08:58

score 0 · Answer 1 · answered Aug 02 '18 at 07:55

The below is not tested but it just to give you an idea, try to prepare the values first send add them to the update. then make sure you have an index on the table covering the column. when you use functions it will make harder for the DML to read indexes. also in such way you are using bind variable method (soft parsing) which it will enhance the performance much better.

  select REPLACE (' ||i.COLUMN_NAME ||',CHR(10),'||chr(39)||chr(39)|| ') into R_COL from dual;

  select INSTR('||i.COLUMN_NAME ||',CHR(10)) into I_COL from dual;

UPDATE '|| i.TABLE_NAME || ' SET ' ||i.COLUMN_NAME || ' = R_COL WHERE I_COL >0;

How to Optimize PL/SQL code using Bulk collect/FORALL?

1 Answers1