This discussion is archived
2 Replies Latest reply: Jul 20, 2011 9:53 PM by Frank Kulash RSS

substitution run time variable format

739856 Newbie
Currently Being Moderated
Dear All:

Here is a simple sqlplus command line with & for substitution variable:

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;
Enter value for sal:


Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:

set verify off;
SQL> SELECT * FROM emp where sal >&SAL;


Enter value for sal:


Any help, advise, suggestion will be highly appreciated.

Thanks
  • 1. Re: substitution run time variable format
    sb92075 Guru
    Currently Being Moderated
    user12018084 wrote:
    Dear All:

    Here is a simple sqlplus command line with & for substitution variable:

    set verify off;
    SQL> SELECT * FROM emp where sal >&SAL;
    Enter value for sal:


    Now as you see, oracle asks to Enter Value for substitution variable in line just below the SQL>. I wonder if there is one or more line could be left there so to improve the readability. So it should look like:

    set verify off;
    SQL> SELECT * FROM emp where sal >&SAL;


    Enter value for sal:


    Any help, advise, suggestion will be highly appreciated.

    Thanks
    SQL*Plus was never intended to be a User Interface utility.
    Accept reality & concentrate on more important issues than blank lines on terminal.
  • 2. Re: substitution run time variable format
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    It's true that SQL*Plus is a light-weight front end, and you should find something better for production user interface.
    It's not true that SQL*Plus has to be all that ugly. The SQL*Plus ACCEPT command is a more genteel way to assign values to substitution variables. You can use the SQL*Plus PROMPT command to print blank lines. Write your script like this:
    PROMPT
    PROMPT
    ACCEPT sal     PROMPT "Please enter a value for sal (e.g. 3000): "
    PROMPT
    PROMPT
    
    set verify off;
    
    SELECT  * 
    FROM    emp 
    WHERE   sal     > &SAL;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points