replace unix variable with value into sql file

Question

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`

I would use envsubst or M4

Neil McGuigan
– Neil McGuigan

2017-04-11 23:01:59 +00:00
Commented Apr 11, 2017 at 23:01 — Neil McGuigan
– Neil McGuigan, Commented Apr 11, 2017 at 23:01

user373333 · Accepted Answer · 2017-04-11 21:04:31Z

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

edited Apr 11, 2017 at 21:04

answered Apr 11, 2017 at 20:33

user373333

2512 silver badges4 bronze badges

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
– CoolChap007

2017-04-11 20:44:22 +00:00
Commented Apr 11, 2017 at 20:44
sed lines needed better handling of the quotes. i've fixed the example, this should work.

user373333
– user373333

2017-04-11 21:05:54 +00:00
Commented Apr 11, 2017 at 21:05

Add a comment |

Stack Exchange Network

replace unix variable with value into sql file

1 Answer 1

You must log in to answer this question.

Hot Network Questions

replace unix variable with value into sql file

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions