access the variable value in .sql file

curious_mind

Hi All,

I have a requirement where i have to extract the file name which is stored in the database table which is as below:

DECLARE

V_SQL_File         VARCHAR2(40);

BEGIN

SELECT CD_VAL_VARCHAR2 INTO V_SQL_File 

FROM CD_VAL

WHERE CD_TYP   = v_proc_cd_in

AND  CD_CAT_TYP  = v_proc_step_cd_in

AND  CD_SUBCAT_TYP = 'FL_CHK';

@$EODSSQLFILE/V_SQL_File;

END;

/

in the above script V_SQL_File is not getting the value when put in like @$EODSSQLFILE/V_SQL_File

Please suggest how to use it.

Billy Verreynne

You cannot run client commands inside a server context.

A PL/SQL anonymous code block is everything from the DECLARE (or BEGIN) statement, to the END; statement. Such a text block of code is shipped to the database server. The server compiles the source code block into a cursor, and executes it.

Thus this code block CANNOT include SQL*Plus client commands - or run SQL*PLUS scripts. This code is run inside a server context where there are no means to interact with the client via client commands.

Client interaction needs to be done via bind variables. The source code block can contains bind variables, which can be used as input variables, and output variables.

So SQL*Plus can pass :FILE as a bind variable in the anonymous PL/SQL source code block. This code executes on the server, and returns the filename to use via the :FILE variable.

SQL*Plus can now create a substitution variable &FILE and place the bind variable :FILE's value into it.

And then use that to run a local script via the statement @&FILE. with whatever parameters needed.

Hemendra.Singh

Hi ,

Please try this and let me know the status.

------------------------------------------------------------------------------

set serveroutput on

DECLARE

V_SQL_File     VARCHAR2(40);

BEGIN

SELECT CD_VAL_VARCHAR2 INTO V_SQL_File 

FROM CD_VAL

WHERE CD_TYP  = v_proc_cd_in

AND CD_CAT_TYP = v_proc_step_cd_in

AND CD_SUBCAT_TYP = 'FL_CHK';

[email protected]$EODSSQLFILE/V_SQL_File;

dbms_output.put_line(V_SQL_File);

END;

/

------------------------------------------------------------------------------

Thanks,

Hemendra Singh

curious_mind

it is printing the value of the variable V_SQL_File as FCP_ADHOC.sql which is correct

but while doing this @$EODSSQLFILE/V_SQL_File the value of the variable not getting returned.

Hemendra.Singh

Thanks for update,

please try this and let me know the status.

set serveroutput on

DECLARE

V_SQL_File     VARCHAR2(40);

BEGIN

SELECT CD_VAL_VARCHAR2 INTO V_SQL_File 

FROM CD_VAL

WHERE CD_TYP  = v_proc_cd_in

AND CD_CAT_TYP = v_proc_step_cd_in

AND CD_SUBCAT_TYP = 'FL_CHK';

[email protected]$EODSSQLFILE/V_SQL_File;

dbms_output.put_line('@$EODSSQLFILE/'||V_SQL_File||';');

END;

/

Thanks,

Hemendra Singh

curious_mind

it is printing this:

@$EODSSQLFILE/FCP_ADHOC.sql;

Hemendra.Singh

Thanks for update,

could you please clear your requirement.

Thanks,

Hemendra Singh

curious_mind

requirement is now i have to execute this, whatever i have got from the DBMS statement:

@$EODSSQLFILE/FCP_ADHOC.sql;

Hemendra.Singh

Please share me the O/P of this .

spool generate_dynamic_sql.log

set serveroutput on

DECLARE

V_SQL_File     VARCHAR2(40);

BEGIN

SELECT CD_VAL_VARCHAR2 INTO V_SQL_File 

FROM CD_VAL

WHERE CD_TYP  = v_proc_cd_in

AND CD_CAT_TYP = v_proc_step_cd_in

AND CD_SUBCAT_TYP = 'FL_CHK';

[email protected]$EODSSQLFILE/V_SQL_File;

dbms_output.put_line('spool dynamic_sql.sql');

dbms_output.put_line('[email protected]$EODSSQLFILE/'||V_SQL_File||';');

dbms_output.put_line('spool off;');

dbms_output.put_line('exit;');

END;

/

spool off;

cat generate_dynamic_sql.log 

Thanks,

Hemendra Singh

EdStevens-OC

@$EODSSQLFILE/V_SQL_File;

This is a sqlplus construct to execute a sql file. It is invalid and not recognized within a PL/sQL block.

curious_mind

ok but when i am writing @$EODSSQLFILE/FCP_ADHOC.sql;

then it is executing the file FCP_ADHOC.sql as required.

but unable to execute it when i am writting @$EODSSQLFILE/V_SQL_File;

please suggest how to get this done.

EdStevens-OC

https://community.oracle.com/tech/developers/discussion/comment/16776456#Comment_16776456

As already stated, the problem arises from trying to execute it inside an anonymous PL/SQL block. I also just noticed that your syntax also includes a '$', which indicates that EODSSQLFILE is actually a shell variable. So why do you need PL/SQL to do this? What's the larger context here?

EdStevens-OC

Take a look at this demo. If that doesn't solve your problem you need to explain more.

oracle:orcl$ cat FCP_ADHOC.sql
select sysdate from dual;

oracle:orcl$ cat doit.sh
#-- set the OS env variable to indicate the dirctory where the SQL is found
EODSSQLFILE=/home/oracle
#-- invoke sqlplus using input redirection. This will allow the shell
#-- to process environment variables before passing the result to sqlplus
sqlplus scott/[email protected] <<EOF
set echo on feedback on verify on trimsp on timing on
-- create table for demo purposes
CREATE TABLE sql_files (file_id, file_name ) AS
SELECT  1, 'FCP_ADHOC.sql' FROM DUAL;
-- show what we have in the table
select * from sql_files;
-- now the guts of the demo
-- use a column def to receive the output of the query
column file_name new_value flnme
--
select file_name from sql_files;
-- show what we got
prompt Name is : &flnme
-- and execute it
@$EODSSQLFILE/&flnme
--
-- clean up the demo
drop table sql_files purge;
--
EOF

curious_mind

its not resolved yet, Anyways i am trying to explain my requirement in the broader way here:

1. i have a ksh script called update_paradigm_adhoc.ksh where the below code is written:

`sqlplus -s /nolog <<EOF > $TempLogFile

whenever sqlerror exit sql.sqlcode rollback;

whenever oserror exit failure;

connect /@$ServiceName

set pagesize 0 feedback off verify off heading off echo off serveroutput on

@$EODSSQLFILE/update_paradigm_adhoc.sql '$job_name_in' '$proc_cd_in' '$proc_step_cde_in' '$fl_nbr_in' '$cl_req_in' '$grp_req_in' '$acct_req_in' '$dist_req_in' '$dist_loc_req_in' '$verbose_log_flag_in' '$pred_check_req_in' '$error_code_1_in' '$error_code_2_in' '$Ibd_id_in' '$bch_dte_in' '$schema_name_in' '$proc_strt_reqd_in' '$frequency_in' '$data_grp_cde' '$child_purge_reqd_in' 

EOF`

and i am trying to call a sql file called update_paradigm_adhoc.sql which is working perfectly fine.

here $EODSSQLFILE refers to a location where all my .sql files are placed

now inside of the sql script update_paradigm_adhoc.sql i have to call another sql file whose name is stored in some database table and i am using below query:

SELECT CD_VAL_VARCHAR2 INTO V_SQL_File  FROM CD_VAL WHERE CD_TYP   = v_proc_cd_in

AND  CD_CAT_TYP  = v_proc_step_cd_in AND  CD_SUBCAT_TYP = 'FL_CHK';

now when i am trying to execute this sql file with the below command, its not working:

@$EODSSQLFILE/V_SQL_File;

but when i am harcoding the name of the sql file, then it is working fine. like this:

@$EODSSQLFILE/FCP_ADHOC.sql;

Please suggest how to call the sql file via using the variable because we have to make it a generic script and no hard coding should be there. hope i am able to clarify my requirement now.

Billy Verreynne

You cannot run client commands inside a server context.

A PL/SQL anonymous code block is everything from the DECLARE (or BEGIN) statement, to the END; statement. Such a text block of code is shipped to the database server. The server compiles the source code block into a cursor, and executes it.

Thus this code block CANNOT include SQL*Plus client commands - or run SQL*PLUS scripts. This code is run inside a server context where there are no means to interact with the client via client commands.

Client interaction needs to be done via bind variables. The source code block can contains bind variables, which can be used as input variables, and output variables.

So SQL*Plus can pass :FILE as a bind variable in the anonymous PL/SQL source code block. This code executes on the server, and returns the filename to use via the :FILE variable.

SQL*Plus can now create a substitution variable &FILE and place the bind variable :FILE's value into it.

And then use that to run a local script via the statement @&FILE. with whatever parameters needed.