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.

Alex Poole · Accepted Answer · 2014-05-21 06:47:41Z

2

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`

answered May 21, 2014 at 6:47

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

Collectives™ on Stack Overflow

Calling sql file from shell script with parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related