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.
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>
select &n + &n from dual; vs select &&n + &&n from dual;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
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