1

I am a newbie to stored procedure and to PL/SQL. There is an existing procedure to copy data from one table to another. I want to rewrite the stored procedure to accept table name and column names as arguments.Did googling on the solution but couldn't come up with a solid solution.

Also planning to add column names as argument so that the column names don't have to be repeatedly added in multiple stored procedures which uses the same tables and columns, helps to reduce maintenance when columns names gets added/removed. Code has been added.

Can anyone help me with this? Any sample code will be very helpful.

create or replace procedure copy_data(startDate DATE, endDate DATE, 
mainTable varchar2, subTable varchar2, cpyTbl varchar2)
IS

commit_size NUMBER :=1000;
existing_columns NUMBER;
after_deletion_columns NUMBER;
removed_columns NUMBER;


TYPE order_ids IS TABLE OF subTable.id%TYPE INDEX BY PLS_INTEGER;
removable_order_ids order_ids;
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);


CURSOR C1 is select id FROM subTable where ord_id in (select ord_id from 
mainTable where tmstmp BETWEEN startDate AND endDate);
BEGIN

open C1;
LOOP
  FETCH C1 BULK COLLECT INTO removable_order_ids LIMIT commit_size;
   forall indx in 1..removable_order_ids.COUNT 
    INSERT INTO cpyTbl (id, ord_id, name, phon_nbr) 
      select id, ord_id, name, phon_nbr from subTable
      where ord_id =  removable_order_ids(indx)  LOG ERRORS INTO 
      ERR$_cpyTbl('INSERT') REJECT LIMIT UNLIMITED;

  COMMIT;  
 EXIT WHEN removable_order_ids.COUNT < commit_size;
 END LOOP;

COMMIT;

end;

user3673985
  • 57
  • 1
  • 1
  • 7
  • 5
    Keywords: dynamic SQL and SQL Injection – Lukasz Szozda Jun 22 '19 at 05:44
  • 4
    You will need either [native dynamic SQL](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/dynamic-sql.html) or [DBMS_SQL](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SQL.html) to build all of the code dynamically at runtime. This may or may not be a good idea. – William Robertson Jun 22 '19 at 07:52

0 Answers0