[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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
0
diteps06
Asked:
diteps06
1 Solution
 
flow01Commented:
basicaly  you can create a procedure base on an anonymous block as

create or replace procedure proc1
is
begin
    .. code of your anonymous block
end;


so creating 1 of the desired procedures could look like

begin
   execute immediate 'create or replace proc1 is begin ' ||  st_refresh body (...,...) || end;';
end;
/

If you want to gather more procedures in a package  look at the general package structure : you can do in in the same way

If you want to immediatly execute the anonymous block created by the function calling with different argument create a procedure that you can execute

  CREATE OR REPLACE
  PROCEDURE  pstr_refresh(i_target_table IN VARCHAR2 DEFAULT NULL,
                       i_target_col IN t_lista_col DEFAULT t_list_col(),
                       i_time_target_col IN VARCHAR2 DEFAULT NULL,
                       i_oper_target_col IN VARCHAR2 DEFAULT NULL,
                       i_source_view IN VARCHAR2,
                       i_source_table IN VARCHAR2,
                       i_source_col IN t_list_col,
                       i_key_col IN t_list_col,
                       i_notnull_source_col IN t_list_col DEFAULT t_list_col()
                       )
 IS
    v_statement varchar2(32767);
    rsl number;
 BEGIN
     rsl := str_refresh(
                     (i_target_table ,
                       i_target_col ,
                       i_time_target_col ,
                       i_oper_target_col,
                       i_source_view,
                       i_source_table,
                       i_source_col,
                       i_key_col,,
                       i_notnull_source_col ,
                       v_statement
                       ) ;

     EXECUTE IMMEDIATE v_statement;
END;
0
 
diteps06Author Commented:
The solution is exactly what I was seeking.
Thanks alot!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now