Re: "Execute Immediate" problem

From: Andrew Hardy <spam.from.news_at_AdvanticaTech.com>
Date: Mon, 7 Apr 2003 12:15:45 +0100
Message-ID: <b6rmm1$rbj$1@sun-cc204.lut.ac.uk>

Jeremy,

"Jeremy" <newspostings_at_hazelweb.co.uk> wrote in message news:MPG.18fb649be73a77129896ec_at_news.cis.dfn.de...

> And the calling proc is this:
>
> declare
> l_web_site_id number := 61;
> l_url varchar2(240) := 'http://www.xxx.com';
> begin
> execute immediate
> 'begin
> zp(p_web_site_id => :l_web_site_id, p_url => :l_url);
> end;'
> using l_web_site_id, l_url;
> end;

> ERROR at line 1:
> ORA-06536: IN bind variable bound to an OUT positio
> ORA-06512: at line 22

And the error message is the clue... in the 'using' clause you need to say whether the variables are IN, OUT or IN OUT - by default Oracle assumes that they're IN. So you need to change your statement to:

USING l_web_site, OUT l_url

I assume that the real code is more complex than the example given as I can't see why you *need* to use the EXECUTE IMMEDIATE statement...

Andy Received on Mon Apr 07 2003 - 06:15:45 CDT