| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL
Thomas J. Kyte wrote:
>
> In article <8c04ak$pio_at_portal.gmu.edu>,
> Sundeep Kumargoswami <skumargo_at_osf1.gmu.edu> wrote:
> > Hi,
> > I am trying to execute a SQL statement against an oracle database
> > the field (customer_name) I am querying on contains records with
> > embedded single quotes (') for example, Craig's data exchange. When
> > the query hits one of these records it fails with improperly quoted
> > string error. The front-end is VB and the connection is made using
> ODBC
> >
> > Do While Not .EOF
> >
> > mySql = "SELECT * FROM all_customer_acct WHERE " & _
> > "customer_name = '" & UCase(!customer_nm) & "'"
> >
> > frmValidator.Text1.Text = UCase(!customer_nm)
> > frmValidator.Refresh
> >
> > outString = UCase(!customer_nm) & "|"
> >
> > How can I change the Sql Statment so it will not fail in the above
> > situation.
> >
> > Thanks,
> > Sundeep
> >
>
> Please -- if you want your app to scale above a handful of queries --
> use BIND VARIABLES. Do not concatenate in string constants, it'll
> defeat the whole purpose of the shared pool -- AND it'll overcome your
> quote problem.
>
> If you must do the character string constant (you'll hate yourself
> later for doing that), you need to double up the quotes:
>
> select * from T where c = 'Craig''s Place'
>
> > --
> > -----------------------------------
> > Sundeep Goswami
> > skumargo_at_gmu.edu
> > goswamis_at_hotmail.com
> >
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
To reinforce Thomas's point, try the following test yourself and you'll see why he stresses it so often.
Test 1: create a PL/SQL proc (or anything similar) which does a few thousand primary key reads where the column value is literal (ie not using bind variables)
Test 2: The same test with the same literal values but each SQL being processed via bind variables.
You can try this in either of two ways - bounce your database, reboot your machine etc so that nothing is preloaded in memory, OR cache the table and its index, and load up your buffer cache.
Run your tests and you'll be amazed at the difference - most of the v$sesstat figures will very similar except of course for parse counts. So its hard to spot that anything is wrong. The real difference will be in your CPU consumed and the response time.
I've tried this simple test several times on several different platforms for different numbers/types of SQL - and every time - bind variables come out better by 60-200% on CPU used and response time. (The example I've just run on my laptop - 1000 SQL's on clean memory was 0.7secs versus 3.2secs without bind vars)
If you're building a data warehouse and you have very particular data requirements, then this is about the only time I've ever seen where bind variables can be a hinderance.
HTH
--
We are born naked, wet and hungry...then things get worse Received on Fri Mar 31 2000 - 05:43:34 CST
 |
 |