Re: sql/plsql query question

mcstock wrote:

>daniel, be more specific
>
>since the basis for both EXECUTE IMMEDIATE and DBMS_SQL is to construct a
>SQL statement in a varchar2 variable, how is it that 'if you do it
>correctly' you don't mask the object reference in a varchar2 (thus masking
>it from USER_DEPENDENCIES)
>
>-- mcs
>
>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>news:1068163649.824295_at_yasure...
>
>
>>roger wrote:
>>
>>
>>
>>>Mostly, it is because I try to avoid dynamic SQL at all costs.
>>>
>>>My main reason being that with DSQL you end up coding
>>>schema object names (tables, columns, functions etc..)
>>>as strings, rather than as symbols that are visible and can
>>>be checked at compile time.
>>>
>>>
>>>
>>This is not true if you do it correctly.
>>
>>I'd suggest you go to http://asktom.oracle.com and look at the numerous
>>examples Tom has created.
>>Tom doesn't write junk.
>>--
>>
>>Daniel Morgan
>>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
>>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
>>damorgan_at_x.washington.edu
>>(replace 'x' with a 'u' to reply)
>>
>>
>>

If the object name is explicitly in the string ... then which search engine will find:

INSERT INTO mytable VALUES (1);

but won't find:

EXECUTE IMMEDIATE 'INSERT INTO mytable VALUES (1)'; ?

It is explicit. i thought you were referring to burying it in a variable.

This isn't a religious thing with me. I don't care how you solve the problem. But if you use
dynamic SQL you will be able to construct SQL statements that exactly do what you wish
without creating clauses that may or may not work under certain conditions based upon
data that may exist in the future but don't exist today.

I use a lot of dynamic SQL. My impression is that Tom Kyte and a lot of other people do
too. I've yet to hear of a good argument for not using it when it is an appropriate solution,
in other words EXECUTE IMMEDIATE 'COMMIT'; will work but it is inappropriate.

So have at it ... and don't use it if you wish. But I fail to see the point of your argument
as you presented it.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)