Execute Immediate - more problems/ comprehension failure!

From: Jeremy <newspostings_at_hazelweb.co.uk>
Date: Mon, 7 Apr 2003 13:17:56 +0100
Message-ID: <MPG.18fb7cc2a07c2bdc9896ef@news.cis.dfn.de>

Could do with some more hints here. Target SQL is execution of a stored procedure, in this case called "extauthproc":

12:56:10 SQL> desc extauthproc
PROCEDURE extauthproc

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_WEB_SITE_ID                  NUMBER                  IN
 P_USER                         VARCHAR2                IN/OUT
 P_URL                          VARCHAR2                IN
 P_RETURN                       VARCHAR2                OUT

This is to be called using the following "execute immediate" statement:

declare

  l_url		varchar2(2000) := 'http:.....';
  l_ext_auth_proc	varchar2(240)  := 'extauthproc';
  l_return		varchar2(30);
  l_username		varchar2(30);
  l_web_site_id	number := 61;
  

begin
  execute immediate

  'begin extauthproc(p_web_site_id => :web_site_id, 
                     p_url=>          :url, 
                     p_user =>        :username, 
                     p_return =>      :return_code); 

   end;'
   using IN l_web_site_id, IN l_url, IN OUT l_username, OUT l_return ; end;
/

Upon execution, I get

ORA-06537: OUT bind variable bound to an IN position

Sort of the inverse of the error I encountered before and on which Andy Hardy put me right. So now I am explicitly stating, in the USING clause, the mode of each variable being passed to 'extauthproc'.

What am I doing wrong?

STOP PRESS! This is on an 8.1.6 devt database - I just tried on an 8.1.7.4 and it works OK... anyone know any workarounds for 8.1.6 or is strictly no go with this concept prior to 8.1.7?

cheers

-- 

jeremy

Received on Mon Apr 07 2003 - 07:17:56 CDT