Ryan, Bind variables are always host variables ? two different terms for the same thing. I believe that the term "host variables" comes from PRO*Precompilers in particular... Mladen, SQL*Plus does have true ³bind variables²; the substitution variables (usually preceded by ³&²) are another matter altogether. SQL*Plus allows you to declare, initialize, and use bind variables as follows: SQL> variable b1 number SQL> exec :b1 := 100; PL/SQL procedure successfully completed. SQL> select count(*) from dba_objects where object_id = :b1; COUNT(*) ---------- 1 SQL> select count(*) from dba_objects where object_id = 100; COUNT(*) ---------- 1 As opposed to substitution variables, which you described: SQL> define V_OID = 100 SQL> select count(*) from dba_objects where object_id = &&V_OID; old 1: select count(*) from dba_objects where object_id = &&V_OID new 1: select count(*) from dba_objects where object_id = 100 COUNT(*) ---------- 1 Hope this helps.... -Tim on 3/7/04 12:48 PM, Mladen Gogala at mgogala@xxxxxxxxxxxx wrote: > Sqplus doesn't have "bind variables". Sqlplus acts like pre-processor > (say cpp) and replaces &var with the the provided substitute. > > > On 03/07/2004 02:39:37 PM, Ryan wrote: >> So sqlplus bind variables are really just host variables? >> ----- Original Message ----- >> From: "Tim Gorman" <tim@xxxxxxxxxxxxx> >> To: <oracle-l@xxxxxxxxxxxxx> >> Sent: Sunday, March 07, 2004 2:25 PM >> Subject: Re: concerning hard parses >> >> >>> I haven't seen your script (might have gotten stripped out by the list >>> handler?), but the problem is likely the test bed, SQL*Plus. >>> >>> SQL*Plus is (apparently) coded to conserve cursors. SQL tracing a session >>> in SQL*Plus shows that, no matter how often you execute the same query, it >>> is parsed each time, an indirect indication that the previously-used >> cursor >>> was closed (note: "close cursor" operations do not show up in SQL >> tracing, >>> so I am only inferring the closure). Note that even when you execute a >>> different SQL statement, the same cursor number is reused. That is, each >>> "dep=0" (i.e. recursive depth zero) cursor always tends to show up with >> the >>> same cursor number in SQL*Plus, a further indication of cursor re-use and >>> conservation. SQL*Plus only seems to allocate a new cursor number for >> each >>> new recursive depth (i.e. "dep" > 0) >>> >>> In other words, it's just the way SQL*Plus is coded, nothing more >>> significant. SQL*Plus is not designed to reduce parsing, but it is >> probably >>> designed to minimize cursor memory resources. >>> >>> In contrast, most forms and reports tools and batch program APIs tend to >>> encourage the use of the HOLD_CURSOR=TRUE RELEASE_CURSOR=FALSE philosophy, >>> where a new cursor is opened for each SQL statement (unless explicitly >>> closed), but by no means is it a requirement... >>> >>> Hope this helps... >>> >>> >>> >>> on 3/5/04 2:26 PM, ryan.gaffuri@xxxxxxx at ryan.gaffuri@xxxxxxx wrote: >>> >>>> I ran a test with bind variables from sqlplus. I am not sure why Im >> getting a >>>> hard parse. >>>> >>>> 1. create table myTable as select * from dba_objects; >>>> 2. I then ran a script to tell me how many parses my current session >> has. >>>> 3. I then initialized a bind variable with 'test_bind.sql' >>>> and ran the following query from test_select.sql >>>> >>>> select object_name >>>> from mytable >>>> where object_name like :object_name >>>> and rownum < 2 >>>> >>>> 4. I then re-initialized the bind variable to a different value and ran >> it >>>> again. I got a hard parse. Shouldn't I get a soft parse since I'm using >> bind >>>> variables? >>>> >>>> so that its readable. I attached the results and I attached the 3 >> scripts I >>>> used. I hope this is ok... >>>> >>>> >>>> ---------------------------------------------------------------- >>>> Please see the official ORACLE-L FAQ: http://www.orafaq.com >>>> ---------------------------------------------------------------- >>>> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx >>>> put 'unsubscribe' in the subject line. >>>> -- >>>> Archives are at //www.freelists.org/archives/oracle-l/ >>>> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >>>> ----------------------------------------------------------------- >>> >>> ---------------------------------------------------------------- >>> Please see the official ORACLE-L FAQ: http://www.orafaq.com >>> ---------------------------------------------------------------- >>> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx >>> put 'unsubscribe' in the subject line. >>> -- >>> Archives are at //www.freelists.org/archives/oracle-l/ >>> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >>> ----------------------------------------------------------------- >> >> ---------------------------------------------------------------- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> ---------------------------------------------------------------- >> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx >> put 'unsubscribe' in the subject line. >> -- >> Archives are at //www.freelists.org/archives/oracle-l/ >> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html >> ----------------------------------------------------------------- >> ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------