diteps06
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot!!