This was a clear and concise explanation of the difference between declaring and using a bind variable in SQL*Plus and the declaring and using a variable via the DEFINE statement.

How do you prompt for a bind variable in a SQL*Plus script with just a SQL query?

Is the following method correct?

ACCEPT x PROMPT 'Enter value for X: '

variable x number

exec :x := &x

select :x from dual;

Hi Tom,

Hope you had a great vacation away from all this!

How can I assign the result of a function to a define variable?
Eg: Define dt=to_char(sysdate). I want the result of the function to be stored in the define variable. Thanks.

ops$tkyte@ORA817DEV.US.ORACLE.COM> <b>column dt new_val dt</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char(sysdate) dt from dual;

DT
---------
05-AUG-02

ops$tkyte@ORA817DEV.US.ORACLE.COM> select '&dt' from dual;
old   1: select '&dt' from dual
new   1: select '05-AUG-02' from dual

'05-AUG-0
---------
05-AUG-02

ops$tkyte@ORA817DEV.US.ORACLE.COM>  

Please ignore the above posting, I found the answer from your archives as below:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:203612348073,%7Bdefine%7D <code>

Thanks.

Is it possible in SQL-Plus whether a variable exists?
In my parent-script I define a variable using accept.
In my child-script I want to use this variable if defined . If not defined (i.e. when running this script without the parent), I want to use a default-value.
When I use &<var-name> in my child-script I get prompted for <var_name>

You would use DEFAULT on the accept to make the variable have a value in the event the user gives you none.


ops$tkyte@ORA920> accept a default hello
afda
ops$tkyte@ORA920> select '&a' from dual;
old   1: select '&a' from dual
new   1: select 'afda' from dual

'AFD
----
afda

ops$tkyte@ORA920> accept a default hello

ops$tkyte@ORA920> select '&a' from dual;
old   1: select '&a' from dual
new   1: select 'hello' from dual

'HELL
-----
hello

ops$tkyte@ORA920> 

Hi Tom
I have a case where using a variable gives me 
unexpected results.

---
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               CHAR(2000)
 DATA                                               CHAR(100)

SQL> select count(*) from t1 where name like 'name%';

  COUNT(*)
----------
      9050

SQL> @sel3
SQL> select count(*)
  2  from t1 no_binding
  3  where name like 'name%';

  COUNT(*)
----------
      9050

SQL>
SQL>
SQL> @sel4
SQL> variable x char(2000);
SQL> exec :x := 'name';

PL/SQL procedure successfully completed.

SQL> select count(*)
  2  from t1 binding
  3  where name like :x || '%';

  COUNT(*)
----------
         0

SQL>
SQL>
-----
My question is why in the second case the result gives
me 0 rows. Strange thing is if I do a bind with the
value of name3 in the second case,it gives me the
correct result as follows:

SQL> set echo on
SQL> @sel4
SQL> variable x char(2000);
SQL> exec :x := 'name3';

PL/SQL procedure successfully completed.

SQL> select count(*)
  2  from t1 binding
  3  where name like :x || '%';

      9000

SQL>


Can you kindly explain what is going on here? 

"that was

where name like 'name             <almost 2000 spaces>      ' || '%'
"
I knew it was something to do with char...

But then how did it work with the second case
...
"SQL> set echo on
SQL> @sel4
SQL> variable x char(2000);
SQL> exec :x := 'name3';

PL/SQL procedure successfully completed.

SQL> select count(*)
  2  from t1 binding
  3  where name like :x || '%';

      9000
"

Should n't it have been 
'name3  aalmost 2000 spaces %' in which case it should
have failed?

Thanx!
 

Thanx for pointing it out!! That clarifies the confusion.
I dont use char in my production - I was using it to
"fill" up the table because that is what a test case
required..I would use rpad and varchar2..

Thanx again!

Menon:)

justin@DEV> select * from v$version;

BANNER
===========================================================
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production


1)
justin@DEV> var x number
justin@DEV> exec :x := 4;

PL/SQL procedure successfully completed.

justin@DEV> print x

X
=================
4

2)
justin@DEV> var x refcursor
justin@DEV> begin
2 open :x for select 1111,2222 from dual;
3 end;
4 /

PL/SQL procedure successfully completed.


justin@DEV> print x

1111 2222
================= =================
1111 2222


justin@DEV> select :x from dual;
select :x from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Any ideas why the command barfs me out?
TAR?

I needed a refresher of these basic concepts (bind variables) and have been searching for twenty minutes looking for the answer. I finally came across this article and found my answer. This was clear, concise, and provided sufficient example to get the point across.

Cheers,
JJ

Tom,
Is it possible to do something like shell does in sql*plus with define variables?

define prifix=mytable_
select count(*) from user_tables
where table_name like &prifixTableName;

and then sqlplus substitutes it with mytable_TableName instead of being asked for values of prifixTableName as a whole???

similar thing in shell script would be ${prifix}TableName, do we have {} or similar magic characters to let sqlplus know the boundary of the variable name?

yes, that works -- you are missing quotes and a terminator:

where table_name like '&prifix.TableName';

that will:

ops$tkyte@ORA9IR2> define prifix=mytable_
ops$tkyte@ORA9IR2> select '&prifix.TableName' from dual;
old   1: select '&prifix.TableName' from dual
new   1: select 'mytable_TableName' from dual
 
'MYTABLE_TABLENAM
-----------------
mytable_TableName


 

pls ignore my question, just &prifix.tablename will work. thanks anyway, but if there is something different, could you share with us?

Tom,

In your response to SR from USA above you state:-

<quote>
cursor sharing will let sqlplus use bind variables efficiently. One of the real
world good uses of cursor sharing.
</quote>

I'm having some difficulty with this. Note the following simple shell script - I'm trying to avoid parsing a procedure call.

****************************************************
#!/bin/ksh

function f
{
sqlplus -s <<EOF
scott/tiger@ab
exec pkg.prc( '$1', '$2' );
EOF
}

f A B
f B C
f C D
f E F
****************************************************

The PKG.PRC just accepts the parameters and does nothing else.

So, unsurprisingly, after I execute this script, I see the following:-

SCOTT@AB> select sql_text from v$sql where lower(sql_text) like '%pkg.prc%';

SQL_TEXT
---------------------------------------------------------------------------
BEGIN pkg.prc( 'C', 'D' ); END;
BEGIN pkg.prc( 'E', 'F' ); END;
BEGIN pkg.prc( 'A', 'B' ); END;
select sql_text from v$sql where lower(sql_text) like '%pkg.prc%'
BEGIN pkg.prc( 'B', 'C' ); END;

Unfortunately, in sqlplus, we can't assign bind variables without issuing some SQL or PL/SQL !

So here's an attempted workaround using cursor sharing without affecting the main package:-

****************************************************
#!/bin/ksh

function f
{
sqlplus -s <<EOF
scott/tiger@ab
var v1 varchar2(10);
var v2 varchar2(10);
alter session set cursor_sharing=force;
exec :v1 := '$1'; :v2 := '$2';
alter session set cursor_sharing=exact;
exec pkg.prc( :v1, :v2 );
EOF
}

f A B
f B C
f C D
f E F
****************************************************

But here's what happens:-

SCOTT@AB> alter system flush shared_pool;

System altered.

SCOTT@AB> !a2 <-- The shell script...

Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.

SCOTT@AB> col sql_text format a40
SCOTT@AB> select sql_text, executions from v$sql where lower(sql_text) like '%pkg.prc%';

SQL_TEXT EXECUTIONS
---------------------------------------- ----------
BEGIN pkg.prc( :v1, :v2 ); END; 4
<snipped above query from this output>

That's better. But what has happened to the BV assignment ?

SCOTT@AB> select sql_text, executions from v$sql where lower(sql_text) like '%:v1 :=%';

SQL_TEXT EXECUTIONS
---------------------------------------- ----------
BEGIN :v1 := 'A'; :v2 := 'B'; END; 1
BEGIN :v1 := 'E'; :v2 := 'F'; END; 1
BEGIN :v1 := 'C'; :v2 := 'D'; END; 1
BEGIN :v1 := 'B'; :v2 := 'C'; END; 1
<snipped above query from this output>

Any suggestions ?

Regards
Adrian

My question is related to original question. I want to use bind variables but it does not work . I am building a string sepearted by comma and I want to use that string in my select . Here is what I am doing:

SQL> variable t varchar2(200);
SQL> exec :t := '(275,287)';  <- this is my string 

PL/SQL procedure successfully completed.

SQL>  begin
  2   for x in (select fname,id_num from custs where cust_id in :t)
  3   loop
  4       dbms_output.put_line(x.fname||' & '||x.id_num);
  5   end loop;
  6   end;
  7  /
 begin
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 2

In table, cust_id is a number data type and thats why I am getting this error , how would I be able to use bind variables in my for loop?

Thanks for your answer.

Syed

 

I used following link to solve my problem, never mind:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:210612357425 <code>

Hi Tom
This is a smll matter, but if I prompt for something like a tablespace name, I need it in upper case. Is there anyway to convert it to upper case before I use it?

CL SCR
PROMPT About to attempt creation of WTIX02 Account. This requries the Temporary and
PROMPT data tablesace names (Usually TEMP and USERS)
PROMPT Please enter these values in UPPERCASE
PROMPT
ACCEPT temporary_tablespace prompt "Temporary Tablespace Name :"
ACCEPT tablespace PROMPT "Data tablespace name :"

CREATE USER WTIX02 PROFILE "DEFAULT"
IDENTIFIED BY WTI_02 DEFAULT TABLESPACE "&&tablespace"
TEMPORARY TABLESPACE "&&temporary_tablespace"
QUOTA UNLIMITED
ON "&&temporary_tablespace"
QUOTA UNLIMITED
ON "&&temporary_tablespace"
ACCOUNT UNLOCK

i want to write a query somthin like
select '&columns_name' from '&table_name';

is it posible?
and how i can do this.

If the input is a=2
B=3
C=4
And output 'll be like
a=2
a=2
B=3
B=3
B=3
C=4
C=4
C=4
C=4 how it come using plsql and sql

Not far off from sitting on your keyboard and submitting that as a question.

Thanks for your answerds, but I had a recurring.
I use the new_value syntax with noprint.
When I assign a variable with select, that's fine, but I have a line break in the spool.
Even if you have turned Termout off, we delete it on the screen, but not in the spool.
do you have a trick to solve this problem?


Thanks for your answerds, but I had a recurring.
I use the new_value syntax with noprint.
When I assign a variable with select, that's fine, but I have a line break in the spool.
Even if you have turned Termout off, we delete it on the screen, but not in the spool.
do you have a trick to solve this problem?
Best regards

</>
$ more req_spool.sql
SET HEAD OFF
SET FEED OFF
SET PAGESIZE 0
SET LINESIZE 80
SET ECHO OFF
SET FEEDBACK OFF
set termout on
UNDEFINE P_Name
DEFINE P_Name = '&&1'
select '&P_Name' from dual;
select 'first line' from dual;
set termout off
COLUMN C_V_subst NEW_VALUE V_subst noprint
select decode ( '&P_Name','1','one','2','two','many') C_V_subst from dual;
--set termout on
select '&V_subst lines' from dual;

SQL> spool test_spool.log
SQL> @req_spool.sql 2
2
first line
SQL> spool off

SQL> !more test_spool.log
SQL> @req_spool.sql 2
2
first line

two lines
SQL> spool off
</>

set termout off
set verify off
SET HEAD OFF
SET FEED OFF
SET PAGESIZE 0
SET LINESIZE 80
SET ECHO OFF
SET FEEDBACK OFF
Set Newpage none
UNDEFINE P_Name
DEFINE P_Name = '&&1'
set echo off
COLUMN C_V_subst NEW_VALUE V_subst noprint
select decode ( '&P_Name','1','one','2','two','many') C_V_subst , 0 from dual;
spool &&2
select '&P_Name' from dual;
select 'first line' from dual;
select '&V_subst lines' from dual;
spool off
set termout on

thank you for your answer,
I'm sorry I did not explain the case well. but unfortunately in my case I am not the spool master, it's positioned/created before my sql script,
I tested with a bind variable, but to transfer the value of the bind variable to the define (substitution) variable, it is also necessary to go through a select ...

Hi,

If I understand Phils comment about spool master correctly it means there is a little framework in place that does the spooling and file handling and the users can only supply the inner sql so there might be no post processing possible either. Also maybe there are some empty lines that need to be kept.
Would be nice of there where a spoolout off (or termout off applied to spool as well) of if spool off/on remembered the spool file instead of resetting it to the default.
Although this will not help in this case I found something neat when experimenting with
https://en.wikipedia.org/wiki/Line_starve
https://en.wikipedia.org/wiki/C0_and_C1_control_codes#C1
{code}
select UNISTR('\0055') x from dual
union all
select Unistr('\001B') || Unistr('\0022') || Unistr('\0043') || Unistr('\008D') from dual
union all
select UNISTR('\0056') x from dual

X
------
U
V

3 rows selected.

select UNISTR('\0055') x from dual
union all
select UNISTR('\0020') from dual
union all
select UNISTR('\0056') x from dual

X
-
U

V

3 rows selected.
{code}

works only in my TOAD-sheet, not in my sql-plus though.

regards,

SQL> spool out.log
SQL> select 'show this' from dual;

'SHOWTHIS
---------
show this

SQL> spool off
SQL> select 'but not this' from dual;

'BUTNOTTHIS'
------------
but not this

SQL> spool out.log app
SQL> select 'and show this' from dual;

'ANDSHOWTHIS'
-------------
and show this

SQL> spool off
SQL>
SQL>
SQL> ho type out.log
SQL> select 'show this' from dual;

'SHOWTHIS
---------
show this

SQL> spool off
SQL> select 'and show this' from dual;

'ANDSHOWTHIS'
-------------
and show this

SQL> spool off

SQL>

Hi,

sorry for the wrong tags 

in the previous entry,

regards,

Hi,

Spool off/on (even with append) wouldn't work I think, because you have to specify the name of the spool file, which the inner script doesn't know and (I ihink) can't get at.

regards,