CODEWITHSUNDEEP
oracle-databasesqlplus
HAL 9000
HAL 9000

Reputation: 3985

How to use bind variable value as prompt in SQL*Plus ACCEPT command

I'd like to use a bind variable value as input prompt in a SQL*Plus script. Here's what I've tried:

1) I defined the bind variable as follows

SQL>var prompt varchar2(100)

2) and assigned a value using PL/SQL

SQL>exec select 'your name' into :prompt from dual

3) now I can PRINT or select the bind variable value as follows:

SQL>select :prompt from dual;

:PROMPT
------------------------------------
your name


SQL>print prompt

PROMPT
------------------------------------
your name

4) now I'd like to have "your name" being shown as ACCEPT prompt but I don't know how that can be achieved:

SQL>accept input prompt 'prompt'
prompt

SQL>accept input prompt ':prompt'
:prompt

5) would I be able to assign the bind value back to a substitution variable, it could be done like that:

SQL>define prompt = 'your name subst'

SQL>accept input prompt '&prompt.>'
your name subst>Bob

SQL>def input
DEFINE INPUT           = "Bob" (CHAR)

The only way I see this could be done is SPOOLing into a temp.sql file and running this using @temp.sql but this seems like a terrible hack

Upvotes: 0

Views: 5010

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10721

Use COL .. NEW_VALUE as mentioned.

From my comprehensive discussion of SQL*Plus variables https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#3_2 :

SQL> variable mybv varchar2(14)
SQL> begin
  2    /* ... */
  3    :mybv := 'report.log';
  4  end;
  5  /

Pass the bind variable's value to a new substitution variable "nv" by using a query:

SQL> column mybvcol new_value nv noprint
SQL> select :mybv mybvcol from dual;

Now you can use the substitution variable in a SPOOL command:

SQL> spool &nv

The SPOOL command executes as if you had typed

SQL> spool report.log

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191570

You could use the column ... new_value syntax with a query, but unless you're running from a script (so set termout off takes effect) you'll get extra blank lines:

SQL> var prompt varchar2(100)
SQL> exec select 'your name' into :prompt from dual

PL/SQL procedure successfully completed.

SQL> set feedback off
SQL> column o_prompt new_value n_prompt noprint
SQL> select :prompt as o_prompt from dual;



SQL> accept input prompt '&n_prompt.>'
your name>Bob
SQL> def input
DEFINE INPUT           = "Bob" (CHAR)

I don't think there's any way to define a substitution variable directly from a bind variable, or use a bind variable in a purely client statement like define, accept, etc. (Other than print, which is a client command but exists for that purpose).

Of course, if you're doing that then you don't really need the bind variable, you can do it more directly with just:

SQL> set feedback off
SQL> column o_prompt new_value n_prompt noprint
SQL> select 'your name' as o_prompt from dual;



SQL> accept input prompt '&n_prompt.>'
your name>Bob
SQL> def input
DEFINE INPUT           = "Bob" (CHAR)

Upvotes: 1

Related Questions