Re: Dynamice SQL in Oracle

"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 Received on Sat May 14 2005 - 06:09:58 CDT