Shell script to catch PL/SQL return values

monie2717 · March 23, 2010, 11:44am

I tried searching the forum for similar posts but its closed now.
Would appreciate any help on this.
I am trying to capture return value from a select query into a variable.
DB is Oracle
I am able to spool it to a file but I donot intend to use it.

Here is my script that does not work
I am able to connect to the db but the value is not populated into variable

 #!/bin/sh
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x = '$ORACLE_HOME/bin/sqlplus report/report@$ORACLE_SID <<EOF
declare 
var number;
select count(*) from report.CUBE_BUILD_INDICATOR;
dbms_output.put_line(var);
exit;
eof'
echo 'done 1'
echo $var
echo var
echo 'done 2'
echo x
echo $x

jim_mcnamara · March 23, 2010, 11:55am

lots problems that I can see quickly:

 #!/bin/sh
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus report/report@$ORACLE_SID <<EOF
SET SERVEROUT ON SIZE 100000
declare 
  var number:=0;
BEGIN
  dbms_output.enable(100000);
  select count(*) 
    into var 
   from report.CUBE_BUILD_INDICATOR;
  dbms_output.put_line(var);
END;
/
exit;
EOF )
echo 'done 1'
echo var
echo $var
echo 'done 2'
echo x
echo $x

you need to mind spaces and capitalization; x=$( ) runs a process as a child and assigns the printed output to x;
SET SERVEROUT allows dbms_output to write to the tty
dbms_out.enable() is also required
EOF <-> EOF must match casewise
you need "select something into var" in order for var to have a value.
You must have gotten dozens of errors - both from PL/SQL and from shell.

monie2717 · March 23, 2010, 12:00pm

Thanks for the prompt reply. Variable still displays nulls. Is thr something major i am missing.
( Am a total novice in this )

jim_mcnamara · March 23, 2010, 12:05pm

reread the post, please, copy it EXACTLY, don't add or remove spaces, or change case.
If that fails, get into sqlplus, cut and paste the PL/SQL stuff (from SET SERVEROUT ... to

END;
/

)onto the screen and run it.

---------- Post updated at 10:05 ---------- Previous update was at 10:03 ----------

I tried the pl/sql it works correctly as posted.

monie2717 · March 23, 2010, 12:10pm

Yeah the code works gud. But the value of x and var turns out null.
Any idea why.. infact var should atleast be 0 since its been initialized

durden_tyler · March 23, 2010, 12:36pm

Firstly, the variable "var" is declared in the anonymous PL/SQL block. It is not a shell variable in the caller environment. So, I'd think its scope would be local to the PL/SQL block, and you wouldn't see it in the Unix script.

Secondly, in order to set the value of "x", you may want to run sqlplus in "silent" mode. And also set a few SQL*Plus report environment parameters so as to ensure that only the value of "var" is printed. (Other stuff like the blurb, the feedback message etc. should not be printed.)

Try this -

#!/usr/bin/sh
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
SET SERVEROUT ON SIZE 100000
declare 
  var number:=0;
BEGIN
  select count(*) 
    into var 
   from report.CUBE_BUILD_INDICATOR;
  dbms_output.put_line(var);
END;
/
exit;
EOF )
echo 'done 1'
echo var
echo $var
echo 'done 2'
echo x
echo $x

HTH,
tyler_durden

monie2717 · March 23, 2010, 2:08pm

it does not work .. can this be achieved without a pl sql block
and using just a select query and a linux variable .

durden_tyler · March 23, 2010, 2:26pm

What's the outcome ? Is it printing null values for x ?

The following code uses a sqlplus bind variable and a single SELECT statement to fetch the record count -

#!/usr/bin/sh
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
var n varchar2(100)
exec select count(*)||'' into :n from report.CUBE_BUILD_INDICATOR;
print :n
exit;
EOF )
echo "Count = $x"

A variation of this script is successful on my system -

$
$ # check the contents of the script
$ cat -n getvalue_1.sh
     1  #!/usr/bin/sh
     2  x=$(sqlplus -s test/test <<EOF
     3  set heading off pages 0 feedback off
     4  var n varchar2(100)
     5  exec select count(*)||'' into :n from t;
     6  print :n
     7  exit;
     8  EOF)
     9  echo "Count = $x"
$
$ # execute the script
$ ./getvalue_1.sh
Count = 100
$
$ # confirm that 100 is indeed the record count
$ (echo "select count(*) from t;") | sqlplus -s test/test
  COUNT(*)
----------
       100
1 row selected.
$
$

ORACLE_HOME and ORACLE_SID are already set on my system, so I did not set them explicitly. "T" is just a dummy table that I created specifically for this testcase.

HTH,
tyler_durden

monie2717 · March 23, 2010, 2:42pm

thanks for the code . but both of them throws the following error on my system

Date is Fridayyyyy:
Count = ERROR:
ORA-01741: illegal zero-length identifier

durden_tyler · March 23, 2010, 6:23pm

That's because you probably typed in my code in your script, rather than doing a copy/paste.
And while typing, you entered two double quotes (" and ") next to the count(*).
They are two single quotes (' and ').

...
exec select count(*)||'' into :n from report.CUBE_BUILD_INDICATOR;
...

The portion of the script in red color is two key-presses of the single quote character.

Here's what happens when you use two double quotes -

$ 
$ 
$ cat -n getvalue_1.sh
     1  #!/bin/sh
     2  x=`sqlplus -s test/test <<EOF
     3  set heading off pages 0 feedback off
     4  var n varchar2(100)
     5  -- NOTE THE TWO DOUBLE-QUOTE CHARACTERS APPENDED TO COUNT(*) IN THE LINE BELOW...
     6  exec select count(*)||"" into :n from t;
     7  print :n
     8  exit;
     9  EOF`
    10  echo "Count = $x"
$ 
$ ./getvalue_1.sh
Count = ERROR:
ORA-01741: illegal zero-length identifier
$ 
$ 
$

And here's what happens when you use two single quotes -

$ 
$ 
$ cat -n getvalue_1.sh
     1  #!/bin/sh
     2  x=`sqlplus -s test/test <<EOF
     3  set heading off pages 0 feedback off
     4  var n varchar2(100)
     5  -- NOTE THE TWO SINGLE-QUOTE CHARACTERS APPENDED TO COUNT(*) IN THE LINE BELOW...
     6  exec select count(*)||'' into :n from t;
     7  print :n
     8  exit;
     9  EOF`
    10  echo "Count = $x"
$ 
$ ./getvalue_1.sh
Count = 100
$ 
$

I think you can remove the single-quotes as long as the bind variable "n" is declared as a VARCHAR2.

$ 
$ cat -n getvalue_1.sh
     1  #!/bin/sh
     2  x=`sqlplus -s test/test <<EOF
     3  set heading off pages 0 feedback off
     4  var n varchar2(100)
     5  exec select count(*) into :n from t;
     6  print :n
     7  exit;
     8  EOF`
     9  echo "Count = $x"
$ 
$ ./getvalue_1.sh
Count = 100
$

HTH,
tyler_durden

monie2717 · March 23, 2010, 9:25pm

thanks so much .. ! ur code worked like magic
thanks a lot.

---------- Post updated at 09:25 PM ---------- Previous update was at 08:57 PM ----------

I have another problem now ..
It throws an error when i modify the sql statement.
TODAY=$(date)

ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
var n varchar2(100)
exec select cube_cd into :n from report.CUBE where module = 'LNR_ALL';
print :n
exit;
EOF )
echo "Count = $x"

durden_tyler · March 23, 2010, 9:47pm

What does the "ORA-" error look like ?

tyler_durden

monie2717 · March 23, 2010, 9:54pm

it says invalid character. probably due to the quotes in the where clause of select query.

durden_tyler · March 23, 2010, 10:57pm

Copy and paste your session over here, so that it shows

your exact code,
the execution and
the ORA- error message returned by Oracle.

tyler_durden

monie2717 · March 24, 2010, 9:45am

code :

TODAY=$(date)
ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
var n varchar2(100)
exec select mod_cd into :n from report.cube where cube_cd = 'LNR_ALL';
print :n
exit;
EOF )
echo "Count = $x"

error :
cognos@sec-dev-sun04 : ./lr_cube_tst.sh misdev
./lr_cube_tst.sh: !/bin/sh: not found
Date is Fridayyyyy: Wed Mar 24 09:43:22 EDT 2010
Count = BEGIN select mod_cd into :n from report.cube where cube_cd = "LNR_ALL"; END;
*
ERROR at line 1:
ORA-06550: line 1, column 64:
PL/SQL: ORA-00904: "LNR_ALL": invalid identifier
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

output in db

select mod_cd from report.cube where cube_cd = 'LNR_ALL';

MOD_CD
------
LNR

durden_tyler · March 24, 2010, 9:59am

And this is what I see on my database:

test@ORA11G>
test@ORA11G> var x varchar2(30)
test@ORA11G>
test@ORA11G> select z into :x from t where y = "b";
select z into :x from t where y = "b"
                                  *
ERROR at line 1:
ORA-00904: "b": invalid identifier
 
test@ORA11G>
test@ORA11G>

We are back to square one !
You will have to start reading my posts completely and understand them thoroughly if you wish you gain something out of this.

I usually get tired saying the same thing again and again and again... ad infinitum...

tyler_durden

monie2717 · March 24, 2010, 10:09am

thanks... but i am using single quotes in my script

durden_tyler · March 24, 2010, 10:27am

That doesn't appear to be the case below -

Count = BEGIN select mod_cd into :n from report.cube where cube_cd = "LNR_ALL"; END;

tyler_durden

monie2717 · March 24, 2010, 10:32am

This is my script ..i tried in two ways as you mentioned.
Also listed is the errors i am getting
it would be really helpful if you could throw some insight here as u seem to be really good at this..

first attempt


TODAY=$(date)

ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
var n varchar2(100)
exec select mod_cd into||'' :n from report.cube where cube_cd = 'LNR_ALL';
print :n
exit;
EOF )
echo "Count = $x"

error

Date is Fridayyyyy: Wed Mar 24 10:13:39 EDT 2010
Count = ERROR:
ORA-01741: illegal zero-length identifier

Second attempt


TODAY=$(date)

ORACLE_HOME=/opt/oracle/product/9.2.0
ORACLE_SID=$1
echo "Date is Fridayyyyy: $TODAY"
x=$($ORACLE_HOME/bin/sqlplus -s report/report@$ORACLE_SID <<EOF
set heading off pages 0 feedback off
var n varchar2(100)
exec select mod_cd into :n from report.cube where cube_cd = 'LNR_ALL';
print :n
exit;
EOF )
echo "Count = $x"

error :

Date is Fridayyyyy: Wed Mar 24 10:31:34 EDT 2010
Count = BEGIN select mod_cd into :n from report.cube where cube_cd = "LNR_ALL"; END;

\*

ERROR at line 1:
ORA-06550: line 1, column 64:
PL/SQL: ORA-00904: "LNR_ALL": invalid identifier
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

durden_tyler · March 24, 2010, 11:12am

(1) Use the "code" tags to show the contents of your scripts. Use it to separate your code from the text of your post.

(2) If your script name is "lr_cube_tst.sh" and it is in the current directory, and if you pass the argument "misdev" to it, then show the output of the following 3 commands:

cat -n ./lr_cube_tst.sh
 
od -bc ./lr_cube_tst.sh
 
./lr_cube_tst.sh misdev

Of course, put the commands as well as their output within "code" tags.

Check the FAQ of this forum to figure out how to use "code" tags, if you don't know it already.

tyler_durden