Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell script to catch PL/SQL return values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script to catch PL/SQL return values
# 8  
Old 03-23-2010
Quote:
Originally Posted by monie2717
it does not work .. can this be achieved without a pl sql block
and using just a select query and a linux variable .
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 -

Code:
#!/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 -

Code:
$
$ # 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
# 9  
Old 03-23-2010
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
# 10  
Old 03-23-2010
Quote:
Originally Posted by monie2717
... but both of them throws the following error on my system

Date is Fridayyyyy:
Count = ERROR:
ORA-01741: illegal zero-length identifier
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 ').

Code:
...
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 -

Code:
$ 
$ 
$ 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 -

Code:
$ 
$ 
$ 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.

Code:
$ 
$ 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
# 11  
Old 03-23-2010
Thanku so so much
thanks so much .. ! ur code worked like magic Smilie
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"
# 12  
Old 03-23-2010
Quote:
Originally Posted by monie2717
...
I have another problem now ..
It throws an error when i modify the sql statement.
...
What does the "ORA-" error look like ?

tyler_durden
# 13  
Old 03-23-2010
it says invalid character. probably due to the quotes in the where clause of select query.
# 14  
Old 03-23-2010
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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to catch sql error in script?

Hi Gurus, I have a script which send sql query to oracle db and return value to my script. dummy code like below: sqlplus -s user/${PASSWD}@${ORACLE_SID} @${DIR}/query.sql > outputfile using above code, when query has error, it send error to same out put file and exit code is 0, is... (6 Replies)
Discussion started by: ken6503
6 Replies

2. Shell Programming and Scripting

Return value to shell script, depending on status of pl/sql udpate

Hi All, I need to return value to the main shell script, depending on whether the UPDATE command in the embedded pl/sql is successfu or not. #!bin/ksh updateStatus=`sqlplus --conn details-- << EOF DECLARE var_rows NUMBER; BEGIN update table_name set column_name =... (7 Replies)
Discussion started by: rituparna_gupta
7 Replies

3. Shell Programming and Scripting

Query the table and return values to shell script and search result values from another files.

Hi, I need a shell script, which would search the result values from another files. 1)execute " select column1 from table_name" query on the table. 2)Based on the result, need to be grep from .wft files. could please explain about this.Below is the way i am using. #!/bin/sh... (4 Replies)
Discussion started by: Rami Reddy
4 Replies

4. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

5. Shell Programming and Scripting

Assigning return value of an embedded SQL in a shell script variable

I've a script of the following form calling a simple sql that counts the no of rows as based on some conditions. I want the count returned by the sql to get assigned to the variable sql_ret_val1. However I'm finding that this var is always getting assigned a value of 0. I have verified by executing... (1 Reply)
Discussion started by: MxC
1 Replies

6. Shell Programming and Scripting

calling pl/sql procedure from shell and return values

How could I call an Oracle PL/SQL procedure from any shell (bash) and catch returning value from that procedure (out param) or get a returning value if it's a function. also, I got into trouble when I tried to send a number as a param #!/bin/bash -e username=$1 pwd=$2 baza=$3... (0 Replies)
Discussion started by: bongo
0 Replies

7. Shell Programming and Scripting

how to store the return values of stored procedure in unix shell script.

hi i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh). the called stored procedure returns some values through OUT variables i want to assign the return values of stored procedure in to unix shell script variable. can you provide me the code. ... (1 Reply)
Discussion started by: barani75
1 Replies

8. Shell Programming and Scripting

Need to return fail or pass from shell script on the basis of pl/sql code execution

Hi guys, I am quite new in shell scripting. I am tring to promote some oracle jobs into control-M. In control-M, I am calling a script which establishes a connection with database and execute some procedures. Now I want if that PL/sql Block got failed script should return failure to... (2 Replies)
Discussion started by: alok1301
2 Replies

9. Shell Programming and Scripting

Shell script to catch PL/SQL return values

Hello, I need some help from the experts on PL/SQL and Shell scripting. I need a shell script that runs a PL/SQL procedure and gets the values returned from the PL/SQL procedure into the shell variables. The PL/SQL procedure returns multiple values. I was able to assign a single return value... (1 Reply)
Discussion started by: Veera_Raghav
1 Replies

10. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies
Login or Register to Ask a Question