Well im sorry, but you are wrong. Besides that, I posted here to get
an answer to a question, not a criticism of a design that you know
nothing about. There is a reason for dynamic SQL method 4, and its for
applications that cannot know a finite set of SQL at compile-time; go
read about it.
Jim Kennedy wrote:
> "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 Mon Oct 16 2006 - 10:29:03 CDT
