CODEWITHSUNDEEP
oracle-databaseshellplsql
tester
tester

Reputation: 223

passing shell variable to plsql procedure

I am trying to run my procedure from shell script. The issue is that parameters are not read by the procedure

#! /bin/bash

circle="GENX"
Date="2015/09/29"
version="V2"

/usr/ORACLE/u01/app/product/11.2.0/client_1/bin/sqlplus admin/admin@TESTDB <<"EOF"
begin
GEN_AUTOMATION.Delete_Invalid_Data('$circle','$Date','$version');
commit;
end;
/
"EOF"

Upvotes: 2

Views: 3926

Answers (2)

Stanislav
Stanislav

Reputation: 28106

You need to use unquoted EOF to have a parameter expansion. Due to bash reference for redirections:

This type of redirection instructs the shell to read input from the current source until a line containing only word (with no trailing blanks) is seen. All of the lines read up to that point are then used as the standard input for a command.

The format of here-documents is:

<<[-]word
    here-document
  delimiter

No parameter and variable expansion, command substitution, arithmetic expansion, or filename expansion is performed on word. If any characters in word are quoted, the delimiter is the result of quote removal on word, and the lines in the here-document are not expanded. If word is unquoted, all lines of the here-document are subjected to parameter expansion, command substitution, and arithmetic expansion, the character sequence \newline is ignored, and ‘\’ must be used to quote the characters ‘\’, ‘$’, and ‘`’.

So, you have to make the word (EOF in your case) unquoted in the bash script, to make it work:

#! /bin/bash

circle="GENX"
Date="2015/09/29"
version="V2"

/usr/ORACLE/u01/app/product/11.2.0/client_1/bin/sqlplus admin/admin@TESTDB <<EOF
begin
GEN_AUTOMATION.Delete_Invalid_Data('$circle','$Date','$version');
commit;
end;
/
EOF

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

It doesn't like the heredoc limit string being quoted; I can reproduce and removing the quotes fixes it:

/usr/ORACLE/u01/app/product/11.2.0/client_1/bin/sqlplus admin/admin@TESTDB <<EOF
begin
GEN_AUTOMATION.Delete_Invalid_Data('$circle','$Date','$version');
commit;
end;
/
EOF

This is nothing to do with Oracle, it's how the shell and heredoc are working; this:

cat <<"EOF"
$circle
"EOF"

... prints $circle instead of GENX too.

This is known behaviour:

Quoting or escaping the "limit string" at the head of a here document disables parameter substitution within its body. The reason for this is that quoting/escaping the limit string effectively escapes the $, `, and \ special characters, and causes them to be interpreted literally.

Upvotes: 1

Related Questions