Link to home
Start Free TrialLog in
Avatar of diteps06
diteps06Flag for United States of America

asked on

Make a generated anonymous Oracle PL/SQL block into a stored procedure

We have an anonymous PL\SQL block generated by a Function (St_Refresh) to execute transfer of data from one schema to another.  
We would prefer the anonymous block to be executed in a stored procedure.

The signature of the Function st_refresh body is as follows:
Function st_Refresh  (
            i_destination_table IN VARCHAR2,
            i_destination_col  IN t_list_col DEFAULT t_list_col()  --A Varray list of column names,
            i_time_destination_col  IN VARCHAR2 DEFAULT NULL,
            i_source_table  IN VARCHAR2,
                            i_source_col  IN t_lista_col,
                           i_key_col  IN  t_lista_col,   --A Varray list of column names which are part of primary key
                           i_notnull_source_col   IN t_lista_col DEFAULT t_lista_col(),
                           num_commit IN NUMBER,
                            str OUT NOCOPY VARCHAR2)
RETURN NUMBER

Attached is the file which contain the definition of the function.

The function produces an anonymous PL\SQL block from str as follows :
DECLARE --- num_commit=9999
TYPE t_colA IS VARRAY(9999) OF tab.colA%TYPE;
TYPE t_colB IS VARRAY(9999) OF tab.colB%TYPE;
TYPE t_colC IS VARRAY(9999) OF tab.colC%TYPE;
v_colA colA;
v_colB colB;
v_colC colC;
CURSOR cur_ins_upd IS SELECT
colA||'|' colA,
colB||'|' colB,
colC||'|' colC,
SYSDATE timestamp
FROM tab_source
WHERE
colA IS NOT NULL
AND
colB IS NOT NULL
AND
colC IS NOT NULL

BEGIN

OPEN cur_ins_upd;
    FETCH cur_ins_upd BULK COLLECT INTO
v_colA,
v_colB,
v_colC,
v_timestamp
    LIMIT 9999;
    num := v_timestamp.COUNT;
    EXIT WHEN num = 0;
FORALL i IN v_timestamp.FIRST .. v_timestamp.LAST

INSERT INTO tab_target(
colA,
colB,
colC,
timestamp
        ) VALUES (
v_colA(i),
v_ colB(i),
v_colC(i),  
 v_timestamp(i));

----
----
----

How can I create a procedure which should contain the anonymous blocks to have the flexibility to manage different source tables and target tables.
function-str-refresh-ee.sql
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of diteps06

ASKER

The solution is exactly what I was seeking.
Thanks alot!!