Re: Dynamic SQL Method 4 : Avoid Re-parsing?

"nsmith22" <nsmith22_at_gmail.com> wrote in message news:1160965298.470165.66960_at_e3g2000cwe.googlegroups.com...
> The need involves the ability for developers to writer server-side
> scripts in a proprietary language that allows the fetching of data from
> arbitrary tables and with arbitrary conditions. The script does not
> contain the SQL itself, but the language interpreter builds the SQL
> based on script tags. This means that there is not a finite or known
> set of possibilities, so it is required to be able to execute
> completely dynamic SQL.
>
> The potential for SQL injection is being avoided by using bind
> variables in Oracle.
>
> The only problem we are having is the ability to cache commonly used
> statements. Using Pro*C, statement identifiers must be known at
> compile time, so we cannot manage them appropriately.
>
> Nick
>
> Jim Kennedy wrote:
> > "nsmith22" <nsmith22_at_gmail.com> wrote in message
> > news:1160785569.715764.238750_at_m73g2000cwd.googlegroups.com...
> > > My overall goal is to get results from arbitrary SQL queries (only
> > > queries) using bind variables with a single function that communicates
> > > with Oracle. I have been able to accomplish this with ANSI Dynamic
SQL
> > > Method 4 (using Pro*C), but it forces me to do a prepare, declare
> > > cursor, open cursor, fetch, and close cursor each and every time. The
> > > huge cost is in the prepare, which forces some kind of parsing (I
think
> > > hard parse) every time.
> > >
> > > Is there a way to achieve the same thing in Pro*C, stored procedures,
> > > OCI, or anything else that will improve performance?
> > >
> > > If you would like a code sample I can provide that.
> > >
> > > Thanks,
> > > Nicholas Smith
> > >
> > What is the business need? Your description sounds very open to sql
> > injection. Why do you need total dynamic sql?
> > Jim
>

You have a poor design. It is highly unlikely that people issue random sql. In reality they usually have a broad set of possibilities, but a realatively small number of statements that actually account for 80%+ of the statements. (Generally, people look for the same stuff all the time in the same manner.) If you are using bind variables than repeated statements will be found in the cache. You could have a cursor manager that managed N number or cursors and thus not reparse etc. a statement that has already been issued. (except for the bind values) Then you could keep the cursor open and just re execute, fetch.
Jim Received on Sun Oct 15 2006 - 22:05:17 CDT