Re: Dynamice SQL in Oracle

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 14 May 2005 08:27:47 -0700
Message-ID: <1116084219.248820@yasure>

Mark C. Stock wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1116047996.888419_at_yasure...
>

>>Maxim Demenko wrote:
>>
>>
>>>DA Morgan schrieb:
>>>
>>>
>>>>Maxim Demenko wrote:
>>>>
>>>>
>>>>
>>>>>As the table t was created with execute immediate, he didn't have
>>>>>another choice ;-)
>>>>>
>>>>>Best regards
>>>>>
>>>>>Maxim
>>>>
>>>>
>>>>Actually he did. Anyone creating a table using EXECUTE IMMEDIATE has
>>>>a choice.
>>>>
>>>>The choice to not try to kludge SQL Server habits into Oracle and
>>>>to do their DDL according to "best practices" using global
>>>>temporary tables.
>>>>
>>>>What is, in effect, being said here is: "If I do something stupid
>>>>it doesn't work." And on that I will agree.
>>>
>>>
>>>What you wrote is just context substitution of my posting. It is
>>>obvious, that there are "always" a choise between static sql and dynamic
>>>sql. It is obvious that static sql is mostly to prefer ( at least on
>>>Oracle databases). The meaning of my remark was: If you create a table
>>>within an plsql block  by dynamic sql and you *have* to do an insert in
>>>*this* table in the *same* block - you have no another choice  as to do
>>>it by dynamic sql too - as contradiction to previous posting. If my
>>>message was unambiguously, i'm sorry, i'll try to improve my english.
>>>
>>>Best regards
>>>
>>>Maxim
>>
>>True but you are ignoring the point. There is NEVER a reason do create
>>a table within a PL/SQL block. EVER!
>>
>>A sense of "needing to do so" is, by definition, a desire to do harm.
>>-- 
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)

>
>
> OP was probably looking for CREATE SCHEMA or just needed more familiarity
> with SQL*Plus scripting.
>
> However, here's two good reasons for creating a table within PL/SQL:
> -- To enforce standards and ease development I've got a PL/SQL package that,
> among other things, has procedures to creates standardized code tables and
> associative tables
> -- When mod_plsql is used for a developer interface, DDL will be issued from
> within PL/SQL
>
> I'm suring there are many other DBA/developer/maintenance reasons for issue
> DDL from within PL/SQL, so it's important to know how it works and what its
> constraints are
>
> That being said, coding table creation into a production application
> (whether the DDL is issues within PL/SQL or not) is very often a design flaw
> or unawareness of GLOBAL TEMPORARY tables -- although it still may exist if
> the application is designed to be user-extensible in a way that cannot be
> accomplished with overly generic data structures. If such a feature is
> included, it needs to be well designed and not based on assumptions or prior
> experience and habits.
>
> Perhaps to rephrase another maxim "Never say never, rarely say rarely,
> usually say not usually...'
>
> ++ mcs

As I said above:

If you are running a script then run a script. NDS is not required.
If you can not connect directly to the server then possibly you shouldn't be doing what you are doing. -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)

Received on Sat May 14 2005 - 10:27:47 CDT