CODEWITHSUNDEEP
sqloracle-databaseplsql
Indu
Indu

Reputation: 5

Create stored procedure which accepts input as stored procedure and produce results dynamically

I want to implement this below requirements:

Sample procedure #1:

Procedure Proc1(Input1  VARCHAR2,
                Input2  NUMBER,
                Input3  VARCHAR2,
                Output  Sys_RefCursor)
IS
BEGIN
    ..................
    ..................
    ..................
END;

Sample procedure #2:

Procedure Proc2(Ipval1  VARCHAR2,
                Ipval2  NUMBER,
                Ipval3  VARCHAR3
                Output  Sys_RefCursor)
IS
Begin
..................
..................
..................
End;

Now the requirement is, I want to create a stored procedure which should accept the procedure name as input.

So according to the input procedure name, it should dynamically produce the result of it.

 Procedure GenProc(InputProcedureName   VARCHAR2,
                   InputListVal         VARCHAR2,
                   OutputCur            SYS_REFCURSOR)
IS

Begin
................Dynamic Code ............... To produce result set as per input procedurename
END;

Any idea how to write dynamic code to fulfill this requirement?

Let's say here:

Upvotes: 0

Views: 147

Answers (1)

0xdb
0xdb

Reputation: 3697

Something like this:

create or replace procedure Proc1 (
                par1 varchar2,
                par2 number,
                par3 varchar2,
                rc out sys_refcursor) is
begin 
    open rc for 
        select 'executing Proc1('||par1||','||par2||','||par3||')' result from dual;
end;
/
create or replace procedure execProc (
                procname varchar2,
                args sys.odciVarchar2List,
                rc out sys_refcursor) is
begin 
    execute immediate 'begin '||procname||q'[(:s1, :n2, :s3, :rc); end;]' 
        using args(1), to_number (args(2)), args(3), out rc;
end;
/

The execution and outcomes:

set autoprint on 
var rc refcursor
exec execProc ('Proc1', sys.odciVarchar2List ('abc', 123, 'def'), :rc)

RESULT
--------------------------------
executing Proc1(abc,123,def)

Disclaimer: This answer is meant for educational purposes only!!!

Upvotes: 3

Related Questions