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;