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

Question

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

Christopher Jones · Accepted Answer · 2016-03-18 04:33:43Z

2

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

answered Mar 18, 2016 at 4:33

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2016-03-17 09:26:45Z

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)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related