SQLplus script from file with bash substitutions

Question

I am running a bash script that includes a here document, which calls SQLPLUS. This includes a .sql script to perform several grants. It works correctly without the substitution.

I want to be able to substitute in a bash variable into the grant statements. Is that possible?

This is a snippet from the bash script

CREDLINE=ownusr/ownpass
GRANT2DO=foo.sql
PASSPROC=bar    <=== this is what I want to pass
sqlplus << EOQ11
$CREDLINE@chaos01
@$GRANT2DO
quit
EOQ11

This is a snippet from the foo.sql

grant execute on $PASSPROC to user86;
grant execute on $PASSPROC to user99;

I have tried several variations on $VAR and &1, but none has worked so far.

oratom · Accepted Answer · 2014-06-26 20:18:53Z

1

You have to call the sql-script with the value of what you want to pass

from sqlplus execute the script:

@foo.sql bar

in foo.sql use:

grant execute on &1 to usr

answered Jun 26, 2014 at 20:18

oratom

1213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rr- · Accepted Answer · 2014-06-26 20:14:02Z

0

You can make your script create .sql file instead of having it replace existing one. Then pass it to sqlplus like you already do.

To make the script readable, you can use so-called "here documents" syntax, which is described here and which you are seemingly already familiar with.

If you insist on doing actual substitution, you could copy the template .sql to some temporary file and run sed -i on it with proper arguments to replace the variables. That, however, is much more complicated than the approach above.

answered Jun 26, 2014 at 20:14

rr-

14.9k6 gold badges49 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQLplus script from file with bash substitutions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related