CODEWITHSUNDEEP
bashsqlplussubstitution
JimR
JimR

Reputation: 513

SQLplus script from file with bash substitutions

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.

Upvotes: 0

Views: 299

Answers (2)

oratom
oratom

Reputation: 121

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

Upvotes: 1

rr-
rr-

Reputation: 14831

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.

Upvotes: 0

Related Questions