Re: Can someone with access to 8ir3 test this for me please?

On Tue, 24 Feb 2004 21:33:06 +1100, "Noons" <wizofoz2k_at_yahoo.com.au> wrote:

>"Andy Hassall" <andy_at_andyh.co.uk> wrote in message
>news:jmjk30dnd4h8b7jakljtgup6g7t5bacl20_at_4ax.com...
>
>>
>http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql2.htm#1004425
>>
>
>Old functionality. The example I provided (which BTW was
>not written by me but by a UK Oracle consultant) is for
>EXECUTE IMMEDIATE. The currently supported and recommended
>usage for Dynamic SQL. And (should) have
>nothing to do with V7 DBMS_SQL.

 I agree with you that it should be documented in EXECUTE IMMEDIATE's docs; if there's a note in the DBMS_SQL docs, there should be a corresponding one in its replacement. And the link above only indirectly points it out; if a parse executes immediately, then that implies there's no way to bind.

 So yes, it's under-documented. There's this bit in the docs:

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#11623

 ... which hints at the restriction, but you were trying to bind a value to a numeric parameter, not an identifier of a schema object, so it's reasonable to think that this restriction wouldn't apply to your example.

 But...

>Like I said: there is nothing written anywhere
>explaining bind variables cannot be used with DDL in
>EXECUTE IMMEDIATE.
 ... there's the Concepts guide:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c16sqlpl.htm#CNCPT216  ... and then ...
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c16sqlpl.htm#1628

 Which does that at a lower level - it states that DDL _in general_ doesn't have a Bind stage; so therefore you can't use bind variables in it, however you're executing it. Which then would apply equally to EXECUTE IMMEDIATE and any other interface?

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>