| 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: SQL-Plus &variables in HINT
A copy of this was sent to Christian Bantzer <bantzerc_at_orca.akctr.noaa.gov> (if that email address didn't require changing) On Tue, 31 Mar 1998 16:21:46 -0800, you wrote:
I tried both of your examples and in (7.3 anyway) they worked just fine. I spooled my session and got the following results:
SQL> select 'partone&&variable.parttwo',
2 'blahone&variable.blahtwo'
3 from dual
4 /
Enter value for variable: ABC
old 1: select 'partone&&variable.parttwo', new 1: select 'partoneABCparttwo', old 2: 'blahone&variable.blahtwo' new 2: 'blahoneABCblahtwo'
'PARTONEABCPARTTW 'BLAHONEABCBLAHTW
----------------- -----------------
So that shows that (1) below does work, plus did replace the . before blahtwo.
then:
SQL> set autotrace on
SQL> select * from emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- --------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
..... [snip]
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=560) 1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=560)
SQL> select /*+ INDEX( emp SYS_&var.750 ) */ * from emp 2 /
Enter value for var: C00
old 1: select /*+ INDEX( emp SYS_&var.750 ) */ * from emp
new 1: select /*+ INDEX( emp SYS_C00750 ) */ * from emp
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- --------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
..... [snip]
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=14 Bytes=560) 1 0 TABLE ACCESS (BY ROWID) OF 'EMP' (Cost=2 Card=14 Bytes=560) 2 1 INDEX (FULL SCAN) OF 'SYS_C00750' (UNIQUE) So, I did a select * to show that it would full scan on a select *. Then I did a hint with a define variable in it (my primary key index on empno was named SYS_C000750). The explain plan the second time around shows that it used my hint to access the table....
can you show us an example that doesn't work? We can probably fix it.
>Hi,
>
>I have too peeves with the SQL-Plus handling of &variables, any help with
>which I would surely appreciate.
>
>1) if you want to substitute the value of an &variable in the middle of a
>string you need to somehow tell sql-Plus where the variable name ends and
>the string continues. By default a . will do that. like in
>
>partone&variable.parttwo
>
>that's all fine and good if you only need to do that once in your script.
>if you try something like
>
>partone&&variable.parttwo
>blahone&variable.blahtwo
>
>SQL-plus will not replace the . before blahtwo and your script won't work.
>If you leave the . away your script won't work either because it will ask
>for a value for variableblahtwo.
>
>OK, that is just an annoyance, since you always can repeatedly enter the
>value for &variable (i.e. not use the &&) here's the one I really need
>help with.
>
>2) If you want to use a &variable within a hint it doesn't work at all.
>like in
>
>select --+ INDEX(tablename IDX_TAB_&yy._PK)
>
>
>Any help surely appreciated
>
> Thanks
> Christian
>==========================================================================
>Christian Bantzer PSMFC-PacFIN F/AKC
>christian_bantzer_at_psmfc.org Bldg. 4 Rm. 2066
>phone (206) 526 4057 7600 SandPoint Wy. NE
>fax (206) 526 4074 Seattle, WA 98115-0070
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Apr 01 1998 - 00:00:00 CST
 |
 |