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` 

Answer 1

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