How do I execute a SQL script using a bind variable

Question

I'm running Oracle 18.c on a Windows 10 platform. I have a large 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 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%

SQL Script 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 getting the script file whose name is stored in :full_file_name to execute?

Answer 1

I found a way to do this. My sql script now looks like:

Define ffn104 = '&&1._f104.sql'
@&ffn104
exit

Note that in the Define statement I had to put a period after the passed variable &&1 to end its definition. Then I appended _f104.sql. That solved the problem.