Re: Validating dynamic SQL

Jeremy wrote:
>
> In article <poxGa.980899$Zo.222761_at_sccrnsc03>, kennedy-
> down_with_spammers_at_attbi.com says...
> > I'm guessing that one of the business requirements is that the application
> > doesn't need to scale. The famous "We will clear up the performance (or
> > scalability) issues once we have the functionality." As one developer I
> > worked with said "Performance and scalability are part of the day to day job
> > of a developer; not something to be bolted on later." My experience has
> > been likewise. Everytime it was something tackled last it ended up costing
> > a fortune to fix. Massive design changes and rearchitecture of the system.
> > It is unfortunate that I have yet to see a Computer Sciences graduate that
> > has taken courses in performance and scalability and how it has to be
> > implemented from the start.
> >
> > Sybrand is correct. Unless it is rare that these things run, scalability is
> > going to bite the big one.
> > Jim
> >
> >
>
> Au contraire, the application *does* need to scale but you have to get
> this into perspective. As I said in my original response to Sybrand, you
> don't know the app. A little more detail. The place in the app where
> this feature would be used is a function that would be executed perhaps
> once per week. The data volumes are relatively low. The SQL will be
> written by someone who understands the database structure and will be
> tested in e.g. sqlplus. Even if the 'dynamic' sql part took 5 seconds to
> execute, it would be no issue.
>
> thanks for your thoughts
>
> cheers
>
> --
>
> jeremy
> ['75 RD250A] | ['02 Fazer 600 in blue]

Its possible but you'll need to have some good developers to achieve it. Try the following demo's on your system - the syntax might need a little tweak to make it run - I'm just typing this direct

set timing on
alter system flush shared_pool;
declare
  x number;
begin
 for i in 1 .. 100000 loop
   execute immediate 'select '||i||' from dual' into x;  end loop;
end;
/

declare
  x number;
begin
 for i in 1 .. 100000 loop
   execute immediate 'select :b1 from dual' into x using i;  end loop;
end;
/

declare
  x number;
begin
 for i in 1 .. 100000 loop
   select i into x from dual;
 end loop;
end;
/

The first one is hard parsing, the second one soft parsing, the third one no parsing.

If you developers work all the tricks to make the stored SQL scalable, then you might go somewhere. If not, you might end up with a hard parsing mess.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"