Error while running Procedure in shell script

Shell Programming and Scripting
1

Hi All,

I am running the below proc in unix by connecting through sqlplus and the procedure is completing successfully. But when i am trying to run through shell scripting by using function. I am getting the error as follows. Please guide me where I am going wrong.

#!/bin/sh
opera ()
{
DECLARE
param NVARCHAR2 (20) := 'NULL';
BEGIN
opat.Opat_Data_Archive_Pkg.archive_dataSet_versions ('g692245', param);
END;
/
}
sqlplus OPERA_DATA/tanstaaflUAT115@TNYOPA57 opera

output:

./coast.sh: line 5: syntax error near unexpected token `('
./coast.sh: line 5: `param NVARCHAR2 (20) := 'NULL';'
2

Just guessing: opera is an SQL function, not a shell function. You need to declare it within sqlplus...

3

still no luck.

4

Post your modified code, and how you call your script. And, of course, what error messages you get in your modified code.

5

Would you be better with a here document?

A very quick hack at this would be:-

#!/bin/sh

sqlplus OPERA_DATA/tanstaaflUAT115@TNYOPA57 <<EOSQL
DECLARE
param NVARCHAR2 (20) := 'NULL';
BEGIN
opat.Opat_Data_Archive_Pkg.archive_dataSet_versions ('g692245', param);
END;
/
EOSQL

Is that useful?
Robin

6

Hi rbatte1,

when run the script now I am not seeing any errors. But it's stopping after the below. Can you please help me here.

./vijay.sh
 SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 13 08:36:38 2016
 Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
 SQL>   2    3    4    5    6
7

See post#4.