I have sql file with multiple unix variable to spool the log into specific location and need to replace the unix variable with actual path before running sql file

`cat a.sql
  accept v_answer prompt "Please Enter Name:"
  spool $SQL_SPOOL_LOG_DIR/db_lists.log
  select name from dbs;
  spool off
  spool $SQL_SPOOL_LOG_DIR/db_test.log
    v_sql varchar2(250);
     v_sql := 'alter system set container=db; ';
     dbms_output.put_line ( v_sql);
     v_sql := 'create table test (eno number);';
     dbms_output.put_line ( v_sql);
    v_sql := 'insert into test values (23423);';
     dbms_output.put_line ( v_sql); 
  spool off
  ! grep -i 'alter\|create\|insert' spool $SQL_SPOOL_LOG_DIR/db_test.log > spool $SQL_CUSTOM_SQL_DIR/table_create.sql
  spool $SQL_SPOOL_LOG_DIR/table_create.log
  spool off

I want to replace the following variable with actual path before running the SQL file

  • 1
    I would use envsubst or M4 – Neil McGuigan Apr 11 '17 at 23:01

Write a little bash wrapper around this file and use sed to replace the variables with the real values before running the sql file.

Example with hard coded values:


sed -i 's|$SQL_SPOOL_LOG_DIR|'"$SQL_SPOOL_LOG_DIR"'|g' a.sql
sed -i 's|$SQL_CUSTOM_SQL_DIR|'"$SQL_CUSTOM_SQL_DIR"'|g' a.sql

mysql < a.sql #example
  • I tried the following, but it is simply replacing $SQL_SPOOL_LOG_DIR instead of actual path echo $SQL_SPOOL_LOG_DIR /home/oracle/te_setup_test/log/sql/ echo $CUSTOM_ORACLE_SQL_DIR /home/oracle/te_setup_test/sql/ echo $CUSTOM_ORACLE_OUT_DIR /home/oracle/te_setup_test/out/ sed -i 's/SQL_SPOOL_LOG_DIR/$SQL_SPOOL_LOG_DIR/g' a.sql cat a.sql spool $$SQL_SPOOL_LOG_DIR/tde_script.log – CoolChap007 Apr 11 '17 at 20:44
  • sed lines needed better handling of the quotes. i've fixed the example, this should work. – user373333 Apr 11 '17 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.