Re: sql/plsql query question

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1068184296.931042_at_yasure>...
> 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.

I like DSQL but I understand the trepidation when approaching it.

We use a tool from Quest, "SQL Impact" which becomes less useful when dynamic SQL is used, mostly because of whatever is dynamic in the SQL, for instance we often make the table a variable and we'll never see it's use properly in Impact. It's more a limitation of Impact (and perhaps other 3rd party tools) than a reason to not use DSQL, but it is a consideration for many.

Greg Received on Fri Nov 07 2003 - 07:46:04 CST