access the variable value in .sql file — oracle-tech

    Forum Stats

  • 3,714,814 Users
  • 2,242,631 Discussions
  • 7,845,075 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

access the variable value in .sql file

curious_mind
curious_mind Member Posts: 249 Bronze Badge

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.

Tagged:

Best Answer

Answers

  • Hemendra.Singh
    Hemendra.Singh Member Posts: 26 Red Ribbon

    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
    curious_mind Member Posts: 249 Bronze Badge

    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
    Hemendra.Singh Member Posts: 26 Red Ribbon

    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
    curious_mind Member Posts: 249 Bronze Badge

    it is printing this:

    @$EODSSQLFILE/FCP_ADHOC.sql;

  • Hemendra.Singh
    Hemendra.Singh Member Posts: 26 Red Ribbon

    Thanks for update,

    could you please clear your requirement.

    Thanks,

    Hemendra Singh

  • curious_mind
    curious_mind Member Posts: 249 Bronze Badge

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

    @$EODSSQLFILE/FCP_ADHOC.sql;

  • Hemendra.Singh
    Hemendra.Singh Member Posts: 26 Red Ribbon

    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
    EdStevens-OC Member Posts: 52 Silver Trophy
    @$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
    curious_mind Member Posts: 249 Bronze Badge

    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
    EdStevens-OC Member Posts: 52 Silver Trophy


    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
    EdStevens-OC Member Posts: 52 Silver Trophy

    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
  • curious_mind
    curious_mind Member Posts: 249 Bronze Badge

    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.

Sign In or Register to comment.