Indirect Procedure call

Indirect Procedure call

Post by R.A.Collin » Fri, 22 Feb 2002 23:22:10



Hi, I am new to Oracle and am porting some stored procedures from another
DB. The problem I am having is this - Some of the procedures take in
parameters and use those parameters as calls for other procedures. Is there
a way to use a parameter as a indirect procedure call in the body of a
procedure??

e.g.

CREATE PROCEDURE proc1
(
    p_value1    in varchar2,
    p_subproc    in varchar2
)
as
BEGIN

    p_value1 := 3;

   -- redirection here such that the routine calls the procedure in the
parameter p_subproc.
    p_subproc (p_value1);

END;

Ray Collins

 
 
 

Indirect Procedure call

Post by damorga » Sat, 23 Feb 2002 02:06:40


The same way any procedure passes any parameter to any other procedure.

PROC xyz (param) IS

BEGIN
   another_proc(param);
END;

Daniel Morgan


> Hi, I am new to Oracle and am porting some stored procedures from another
> DB. The problem I am having is this - Some of the procedures take in
> parameters and use those parameters as calls for other procedures. Is there
> a way to use a parameter as a indirect procedure call in the body of a
> procedure??

> e.g.

> CREATE PROCEDURE proc1
> (
>     p_value1    in varchar2,
>     p_subproc    in varchar2
> )
> as
> BEGIN

>     p_value1 := 3;

>    -- redirection here such that the routine calls the procedure in the
> parameter p_subproc.
>     p_subproc (p_value1);

> END;

> Ray Collins


 
 
 

Indirect Procedure call

Post by Ray » Sat, 23 Feb 2002 04:59:17


I'm sorry, I must not be clear... What I want to do is actually a call
totally new procedure using the value "param" as the name of the procedure
rather than to pass the param through. I want to be able to call different
procedures indirectly depending upon the value of the initial procedure's
param value (which contains the exact name of the procedure to call)...

Thanks for your help..Ray


> The same way any procedure passes any parameter to any other procedure.

> PROC xyz (param) IS

> BEGIN
>    another_proc(param);
> END;

> Daniel Morgan


> > Hi, I am new to Oracle and am porting some stored procedures from
another
> > DB. The problem I am having is this - Some of the procedures take in
> > parameters and use those parameters as calls for other procedures. Is
there
> > a way to use a parameter as a indirect procedure call in the body of a
> > procedure??

> > e.g.

> > CREATE PROCEDURE proc1
> > (
> >     p_value1    in varchar2,
> >     p_subproc    in varchar2
> > )
> > as
> > BEGIN

> >     p_value1 := 3;

> >    -- redirection here such that the routine calls the procedure in the
> > parameter p_subproc.
> >     p_subproc (p_value1);

> > END;

> > Ray Collins

 
 
 

Indirect Procedure call

Post by damorga » Sat, 23 Feb 2002 06:23:58


There may be a way but every attempt I just made returned ORA-00900 invalid SQL
statement. Even an attempt to wrap it in native dynamic SQL.

Here's what I would do.

1. Evaluate whether you really need to do it this way (likely you don't)
2. If you do ... look at the feature in packages called overloading which
allows you to put many procedures inside of a package with the same name.

Daniel Morgan


> I'm sorry, I must not be clear... What I want to do is actually a call
> totally new procedure using the value "param" as the name of the procedure
> rather than to pass the param through. I want to be able to call different
> procedures indirectly depending upon the value of the initial procedure's
> param value (which contains the exact name of the procedure to call)...

> Thanks for your help..Ray



> > The same way any procedure passes any parameter to any other procedure.

> > PROC xyz (param) IS

> > BEGIN
> >    another_proc(param);
> > END;

> > Daniel Morgan


> > > Hi, I am new to Oracle and am porting some stored procedures from
> another
> > > DB. The problem I am having is this - Some of the procedures take in
> > > parameters and use those parameters as calls for other procedures. Is
> there
> > > a way to use a parameter as a indirect procedure call in the body of a
> > > procedure??

> > > e.g.

> > > CREATE PROCEDURE proc1
> > > (
> > >     p_value1    in varchar2,
> > >     p_subproc    in varchar2
> > > )
> > > as
> > > BEGIN

> > >     p_value1 := 3;

> > >    -- redirection here such that the routine calls the procedure in the
> > > parameter p_subproc.
> > >     p_subproc (p_value1);

> > > END;

> > > Ray Collins

 
 
 

Indirect Procedure call

Post by R.A.Collin » Sat, 23 Feb 2002 07:07:38


Thanks Daniel, that's what I will check out next...The originals were from

This way we could add details by calling specific procedures. I thought
there might be some sort of indirect calling convention but ... oh
well........... Thanks for your time. Ray C.


> There may be a way but every attempt I just made returned ORA-00900
invalid SQL
> statement. Even an attempt to wrap it in native dynamic SQL.

> Here's what I would do.

> 1. Evaluate whether you really need to do it this way (likely you don't)
> 2. If you do ... look at the feature in packages called overloading which
> allows you to put many procedures inside of a package with the same name.

> Daniel Morgan


> > I'm sorry, I must not be clear... What I want to do is actually a call
> > totally new procedure using the value "param" as the name of the
procedure
> > rather than to pass the param through. I want to be able to call
different
> > procedures indirectly depending upon the value of the initial
procedure's
> > param value (which contains the exact name of the procedure to call)...

> > Thanks for your help..Ray



> > > The same way any procedure passes any parameter to any other
procedure.

> > > PROC xyz (param) IS

> > > BEGIN
> > >    another_proc(param);
> > > END;

> > > Daniel Morgan


> > > > Hi, I am new to Oracle and am porting some stored procedures from
> > another
> > > > DB. The problem I am having is this - Some of the procedures take in
> > > > parameters and use those parameters as calls for other procedures.
Is
> > there
> > > > a way to use a parameter as a indirect procedure call in the body of
a
> > > > procedure??

> > > > e.g.

> > > > CREATE PROCEDURE proc1
> > > > (
> > > >     p_value1    in varchar2,
> > > >     p_subproc    in varchar2
> > > > )
> > > > as
> > > > BEGIN

> > > >     p_value1 := 3;

> > > >    -- redirection here such that the routine calls the procedure in
the
> > > > parameter p_subproc.
> > > >     p_subproc (p_value1);

> > > > END;

> > > > Ray Collins

 
 
 

Indirect Procedure call

Post by damorga » Sat, 23 Feb 2002 07:44:33


There may be ... I just don't know of it if it exists.

But it is very easy to overload a package or to call a single proc that then
distributes the load to other procedures based on the value of a passed
parameter. I don't see it as an issue requiring more than a few lines of code.

Daniel Morgan


> Thanks Daniel, that's what I will check out next...The originals were from

> This way we could add details by calling specific procedures. I thought
> there might be some sort of indirect calling convention but ... oh
> well........... Thanks for your time. Ray C.



> > There may be a way but every attempt I just made returned ORA-00900
> invalid SQL
> > statement. Even an attempt to wrap it in native dynamic SQL.

> > Here's what I would do.

> > 1. Evaluate whether you really need to do it this way (likely you don't)
> > 2. If you do ... look at the feature in packages called overloading which
> > allows you to put many procedures inside of a package with the same name.

> > Daniel Morgan


> > > I'm sorry, I must not be clear... What I want to do is actually a call
> > > totally new procedure using the value "param" as the name of the
> procedure
> > > rather than to pass the param through. I want to be able to call
> different
> > > procedures indirectly depending upon the value of the initial
> procedure's
> > > param value (which contains the exact name of the procedure to call)...

> > > Thanks for your help..Ray



> > > > The same way any procedure passes any parameter to any other
> procedure.

> > > > PROC xyz (param) IS

> > > > BEGIN
> > > >    another_proc(param);
> > > > END;

> > > > Daniel Morgan


> > > > > Hi, I am new to Oracle and am porting some stored procedures from
> > > another
> > > > > DB. The problem I am having is this - Some of the procedures take in
> > > > > parameters and use those parameters as calls for other procedures.
> Is
> > > there
> > > > > a way to use a parameter as a indirect procedure call in the body of
> a
> > > > > procedure??

> > > > > e.g.

> > > > > CREATE PROCEDURE proc1
> > > > > (
> > > > >     p_value1    in varchar2,
> > > > >     p_subproc    in varchar2
> > > > > )
> > > > > as
> > > > > BEGIN

> > > > >     p_value1 := 3;

> > > > >    -- redirection here such that the routine calls the procedure in
> the
> > > > > parameter p_subproc.
> > > > >     p_subproc (p_value1);

> > > > > END;

> > > > > Ray Collins

 
 
 

Indirect Procedure call

Post by Terje Tyss » Sat, 23 Feb 2002 19:28:12




Quote:>Hi, I am new to Oracle and am porting some stored procedures from another
>DB. The problem I am having is this - Some of the procedures take in
>parameters and use those parameters as calls for other procedures. Is there
>a way to use a parameter as a indirect procedure call in the body of a
>procedure??

Tit depends on the Oracle-version. In 8i and up (I'm not sure in 8) you can do
it like this :

PROCEDURE proc1
(
        p_subproc IN VARCHAR2 ,
        p_value IN VARCHAR2
)
IS
BEGIN
        EXECUTE IMMEDIATE ( 'BEGIN '||p_subproc||'('''||p_value||'''); END;' ) ;
END ;

PROCEDURE proc2
(
        p_value IN VARCHAR2  
)
IS
BEGIN
        dbms_output.put_line ( p_value ) ;
END ;

This will execute like this in SQL*Plus :

C012HOME:orcl>exec proc1 ( 'PROC2' , 'TESTING' ) ;
TESTING

Regards,
Terje Tysse

 
 
 

Indirect Procedure call

Post by R.A.Collin » Sun, 24 Feb 2002 02:08:35


THANKS Terje;
    This is exactly what I was looking for!
        Ray Collins


Quote:> On Thu, 21 Feb 2002 09:22:10 -0500, "R.A.Collins"



> >Hi, I am new to Oracle and am porting some stored procedures from another
> >DB. The problem I am having is this - Some of the procedures take in
> >parameters and use those parameters as calls for other procedures. Is
there
> >a way to use a parameter as a indirect procedure call in the body of a
> >procedure??

>*depends on the Oracle-version. In 8i and up (I'm not sure in 8) you
can do
> it like this :

> PROCEDURE proc1
> (
> p_subproc IN VARCHAR2 ,
> p_value IN VARCHAR2
> )
> IS
> BEGIN
> EXECUTE IMMEDIATE ( 'BEGIN '||p_subproc||'('''||p_value||'''); END;' ) ;
> END ;

> PROCEDURE proc2
> (
> p_value IN VARCHAR2
> )
> IS
> BEGIN
> dbms_output.put_line ( p_value ) ;
> END ;

> This will execute like this in SQL*Plus :

> C012HOME:orcl>exec proc1 ( 'PROC2' , 'TESTING' ) ;
> TESTING

> Regards,
> Terje Tysse