Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL Method 4 : Avoid Re-parsing?

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

From: nsmith22 <nsmith22_at_gmail.com>
Date: 15 Oct 2006 19:21:38 -0700
Message-ID: <1160965298.470165.66960@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
Received on Sun Oct 15 2006 - 21:21:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US