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

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Thu, 26 Feb 2004 21:36:03 +0100
Message-ID: <c1ll8n$qb5$1@news4.tilbu1.nb.home.nl>

Noons wrote:

> 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

asktom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:12010934572944453880::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:455220177497,   (may wrap):
<quote>
Followup:
it would be

   execute immediate 'create table ' || t1 || '(eno number)';

you cannot BIND DDL ever -- DDL does not do bind variables. </quote>

-- 

Regards,
Frank van Bortel

Received on Thu Feb 26 2004 - 14:36:03 CST