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

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<va9n30d3rad7d82bc2kch9duutsm1mtgln_at_4ax.com>...

> 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.

Or maybe the functionality just be fixed? There is no reason why dynamic DDL cannot use bind variables.

> 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,

not even remotely.

> 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.

Precisely. In fact as far as I know, the restriction of using a bind variable to specify a schema object in dynamic SQL applies to ALL SQL, not just DDL or DML. IOW, irrelevant here.  

> >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?

It does NOT state that DDL doesn't have a bind stage. (Don't understand the "in general"? Either it does or it doesn't, no?)

What it says is that DDL has parsing-to-execution executed in one step:
<QUOTE>
The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.
</QUOTE>

No, I'm sorry: this whole thing is just improperly documented and not even remotely obvious.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Wed Feb 25 2004 - 19:16:56 CST