0

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
  declare
    v_sql varchar2(250);
   begin
     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); 
   end;
   /
  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
  @$SQL_CUSTOM_SQL_DIR/table_create.sql
  spool off
exit;`

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

`$SQL_SPOOL_LOG_DIR
 $SQL_CUSTOM_SQL_DIR`
  • 1
    I would use envsubst or M4 – Neil McGuigan Apr 11 '17 at 23:01
0

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:

#!/bin/bash
SQL_SPOOL_LOG_DIR="/path/to/log/dir"
SQL_CUSTOM_SQL_DIR="/path/to/sql/dir"

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.