Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Execute Immediate help

Re: Execute Immediate help

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 2 Jun 2001 01:03:23 -0500
Message-ID: <8j%R6.496$yt.10274@nnrp1.sbc.net>

comments inline.

"Phil Shields" <pshields_at_klmicrowave.com> wrote in message news:thduv2qn8ct196_at_corp.supernews.com...
> I am trying to use a generic method to call procedures from a procedure.
> All of the procedures have 3 out variables, 1 IN OUT and the word 'DO' as
 an
> IN variable. Below is an example of a procedure I would like to call
>
> Customer_Info_Api.New__ (info_, objid_,objversion_, attr, 'DO');
>
> The first 3 variables have no value (they are out variables),

the caller will need to declare variables to receive the values returned.

> the procedure
> creates a value for the variable attr.
> How can I use Execute Immediate to allow me to use a variable for the
> procedure name.

the procedure name cannot be supplied as a separate bind variable. the procedure name is included in the string passed as sql statement.

the names of the variables to hold the OUT arguments cannot simply be passed through as part of the sql statement string. you must use placeholders in the sql statement string, and then bind the variables.

> I have tried several things and nothing works. I have
> tried
>
> api := 'Customer_Infor_Api.New__'
> attr := 'long text to pass to procedure';
>
> plsql := 'Begin'||api||'(info_,objid_,objversion_,'||attr||',DO); END;';
> Execute Immediate plsql;
>
> I seem to be having troubles because some of the variables are out
> variables. Does anyone have a suggestion.

my first suggestion is that you carefully record any error messages you are seeing, or any unexpected behavior. are you not getting an error message? or are you having trouble understanding what the PL/SQL syntax is doing ?

my next suggestion would be that you declare the variables that are going to receive the arguments that the procedure is going to return, and reference those variables in your call to the procedure.

my third suggestion is that there is no compelling reason for you to use an EXECUTE IMMEDIATE. just declare the variables and call the procedure, like this:

DECLARE

  ls_info VARCHAR2(2000);
  ls_objid VARCHAR2(2000);
  ls_objversion VARCHAR2(2000);
  ls_attr VARCHAR2(2000);

  lcs_DO CONSTANT VARCHAR2(2) := 'DO';
BEGIN
  ls_attr := 'long text to pass procedure';   Customer_Info_Api.New__ (ls_info, ls_objid,ls_objversion, ls_attr,lcs_DO); END;
> Thanks
> Phil
>
 

>
Received on Sat Jun 02 2001 - 01:03:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US