SQL*Plus substitution variables

[joness59]

Is there a way to have PL/SQL Dev handle substitution variables the same way as in SQL*Plus? I have a script the sets a value for a variable in a select statement and then that variable is displayed in a second query. In PL/SQL Dev, I get a window prompting for a value for the variable, even though it has already been set. Here is the script:

col dbname format a8

set termout off
set verify off
select case value
when 'PSHRPRD9' then 'PROD'
when 'PSHRDEV' then 'DEV'
when 'PSHRTRN1' then 'TRN1'
when 'PSHRTRN2' then 'TRN2'
when 'PSHRGLD9' then 'GOLD'
else '???' end "dbname"
from v$parameter
where name = 'db_name';
set termout on

select '&dbname' "DATABASE"
, sid
, serial#
, username
, osuser
, terminal
, client_info "CLIENT"
, sql_hash_value
, status
, program
, to_char(logon_time,'MM/DD/YYYY HH:MI:SS') "LOGON_TIME"
from v$session@pshrtrn2
where username is not null
and status != 'INACTIVE'
order by database, client, osuser, status;

set verify on

 

[Bo Pedersen]

SQL*Plus will also prompt for the value when using &dbname. In order to not be prompted for a new value, use &&dbname.

As an alternative, the current script could be written as one select.

Bo Pedersen

 

[joness59]

I guess I wasn't clear in my description. The problem is not in SQL*Plus, but PL/SQL Dev is always prompting for the value even though it has already been set in the first select statement.

TIA,

Stephen Jones

 

[CTzen]

Stephen,

I run your example in SQL*Plus and it asked me for a value. The reason is, I believe, you've missed statement similar to

col dbname new_value dbname

That way, your first select will set value for 'dbname' variable. Also naming variable same way as table column makes it all a bit confusing Now, code below is working in PL/SQL Developer 7 Beta 1:

cl scr
set echo off
col dbname format a8
undefine dbname

select case value
when 'PSHRPRD9' then 'PROD'
when 'PSHRDEV' then 'DEV'
when 'PSHRTRN1' then 'TRN1'
when 'PSHRTRN2' then 'TRN2'
when 'PSHRGLD9' then 'GOLD'
else '???' end "dbname"
from v$parameter
where name = 'db_name';

prompt "--- Without new_value ---"
define dbname

col dbname new_value dbname

select case value
when 'PSHRPRD9' then 'PROD'
when 'PSHRDEV' then 'DEV'
when 'PSHRTRN1' then 'TRN1'
when 'PSHRTRN2' then 'TRN2'
when 'PSHRGLD9' then 'GOLD'
else '???' end "dbname"
from v$parameter
where name = 'db_name';

prompt "--- With new_value ---"
define dbname

select '&dbname' "DATABASE"
, sid
, serial#
, username
, osuser
, terminal
, client_info "CLIENT"
, sql_hash_value
, status
, program
, to_char(logon_time,'MM/DD/YYYY HH:MI:SS') "LOGON_TIME"
from v$session@pshrtrn2
where username is not null
and status != 'INACTIVE'
order by database, client, osuser, status;