Oracle SQLPLUS - how to concatenate variable in PROMPT COMMAND?-CodePudding

For example, in the beggining of script I get the input from user:

Prompt "Type the path where the script will be saved: "
spool '&&PATH\tmp_script.sql'

ok, at the end I want to leave some message like it:

Prompt
prompt End of commands.
prompt
prompt Execute: @&PATHtmp_script.sql to execute the generated script.
prompt

But the sqlplus don't undestand I am trying to concatenate &PATH with tmp_script.sql. I've tried the scape \, but don't work on prompt command.

CodePudding user response：

Isn't that two ampersands?

SQL> prompt "Type path"
"Type path"
SQL> spool '&&PATH\test.sql'
Enter value for path: c:\temp
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> prompt End of commands
End of commands
SQL> prompt Execute :: &&PATH\test.sql to execute ...
Execute :: c:\temp\test.sql to execute ...                        --> here's te result
SQL>

CodePudding user response：

Substitution variables can be terminated with a dot.

SQL> define PATH = u:\whatever
SQL> prompt File is saved to &PATH.\somefile.log

gives

File is saved to u:\whatever\somefile.log

CodePudding user response：

I've found the solution. Actually, I was almost there, need to use \, but before we need to use set escape on command to make \ as escape, You can check here

SET ESCAPE on

Prompt "Type the path where the script will be saved: "
spool '&&PATH\tmp_script.sql'

Prompt
prompt End of commands.
prompt
prompt Execute: @&PATH\tmp_script.sql to execute the generated script.
prompt