Calling sql file from shell script with parameters

Question

I have developed following code to call a sql file from shell script testshell.sh

#!/usr/bin/env ksh
feed=`sqlplus -s uname/pwd <<-EOF
@test.sql 'Test_VAl'
/
exit;
EOF`
echo $feed;

My sql file is test.sql which contains following:

   Declare

 attributeName varchar2(255):=&1;
BEGIN
     DBMS_OUTPUT.put_line (attributeName);

END;

I am getting following error while execution.

old 3: attributeName varchar2(255):=&1;
new 3: attributeName varchar2(255):=Test_VAl;
attributeName varchar2(255):=Test_VAl; test.sql testshell.sh
ERROR at line 3:
ORA-06550: line 3, column 31: PLS-00201: identifier 'TEST_VAL' must be declared
ORA-06550: line 3, column 16: PL/SQL: Item ignored
ORA-06550: line 5, column 25: PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3: PL/SQL: Statement ignored

Please tell me how to fix this issue.

Answer 1

If your substitute variable is a string then you need to quote it when it's used, not when it's passed in. At the moment it doesn't have quotes so it's treated as an object identifier, and there is no matching object or variable, hence the error.

So your SQL script would be:

set verify off
DECLARE
  attributeName varchar2(255):='&1';
BEGIN
  DBMS_OUTPUT.put_line (attributeName);
END;
/

Of course you don't need to define a local variable but I assume you're experimenting with simple cases for now.

The set verify off stops the old and new messages being displayed. Thos are useful for debugging but otherwise are usually just noise.

Then you can call it with:

feed=`sqlplus -s uname/pwd <<-EOF
@test.sql Test_VAl
exit;
EOF`

Or if you include the exit in the script you can do:

feed=`sqlplus -s uname/pwd @test.sql Test_VAl`