I'm running Oracle 18.c on a Windows 10 platform. I have a DOS script which calls SQL Plus to run the first SQL script, passing a parameter to it. The parameter is successfully passed to the first SQL script. In that SQL script I'm trying to append some text to the passed parameter so that it can call a second sql script using the "@" feature.
DOS Script test1.bat
@echo off
SET value1=2020_01_19_17_00_01
sqlplus my_username/my_password@my_TNS as sysdba @C:\Backups\test1.sql %value1%
And here's the sql file that's called:
Test1.sql
SET SERVEROUTPUT ON
variable param1 varchar2(512);
variable full_file_name varchar2(512);
BEGIN
:param1 := '&&1';
dbms_output.put_line('The value of the passed parameter is: ' || :param1);
:full_file_name := :param1 || '_f104.sql';
dbms_output.put_line('The new filename would be: ' || :full_file_name);
@:full_file_name;
END;
/
I'm having problems getting the value in :full_file_name to execute from the test1.sql script. If a variable were not involved I would simply use the line @2020_01_19_17_00_01_f104.sql. How do I go about executing the script file whose name is stored in :full_file_name?