0

Wanted to know if it is possible to "pass a variable value as an input to an oracle sql file that is being called from a shell script"

I am trying like this, but its not working:

SQL file:

set head on
set feed on
set termout off
set trimspool on

spool ts_verify.log
select tablespace_name from dba_tablespaces where tablespace_name='$ts_name';
spool off

set termout on
set trimspool off
exit

Shell script:

ts_name=''
echo "Please enter the Tablespace Name for which you want to add datafile"
read ts_name

sqlplus -s "/ as sysdba" << EOF

@ts.sql

EXIT
EOF

Regards, Aditya

0

Turn your SQL script into a template file with placeholders that are really easy to match. Then create the real SQL script from the template when you know the values of the variables:

template.sql:

set head on
set feed on
set termout off
set trimspool on

spool ts_verify.log
select tablespace_name from dba_tablespaces where tablespace_name='@@TABLESPACENAME@@';
spool off

set termout on
set trimspool off
exit

Your script:

#!/bin/sh

unset ts_name
echo "Please enter the Tablespace Name for which you want to add datafile"
read -r ts_name

# create real SQL script and pass it to `sqlplus`:
sed "s/@@TABLESPACENAME@@/$ts_name/g" template.sql |
sqlplus -s "/ as sysdba"
  • @ Mr. Kusalananda. It worked perfectly. Thank you for the help. – user206245 Apr 27 '18 at 9:47

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.