Solved

How to capture the return value of the oracle function in shell script?

Posted on 2003-12-03
8
1,748 Views
Last Modified: 2013-12-26
I have a shell script like this:

sqlplus -s $USER/$PASS << ENDSQL #1> /dev/null 2>&1
set define off
set head off
whenever sqlerror exit sql.sqlcode
select fn_get_current_date_for_region('USA') from dual;
 ENDSQL
   
I want to capture the return value of the oracle function fn_get_current_date_for_region('USA') in the shell script. How can i do this?
Thanks...
0
Comment
Question by:rama_krishna
8 Comments
 
LVL 7

Expert Comment

by:glassd
ID: 9872736
One way to return a value is to echo it. Anything sent to STDOUT can be caught. Something like this:

#!/bin/ksh
GetHostName()
{
   typeset Host
   Host=$(uname -n)
   echo $Host
}

MyHost=$(GetHostName)

You can capture multiple values using this technique:

#!/bin/ksh
GetHostName()
{
   typeset Host OS
   Host=$(uname -n)
   OS=$(uname -s)
   echo $Host $OS
}

GetHostName | read MyHost MyOS

If you need to prompt for input inside the script, use this format:

read Var1?"Enter variable 1: "

This does not sent the prompt to STDOUT.

If you REALLY need to put data to the screen inside the function, try this:

#!/bin/ksh
GetHostName()
{
   typeset Host Screen
   Screen=$1
   echo "Gathering host info" > $Screen
   Host=$(uname -n)
   echo $Host
}

Term=$(tty)
MyHost=$(GetHostName $Term)
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 9874129

sqlplus -s $USER/$PASS << ENDSQL   >mylog.txt   2> /dev/null
set define off
set head off
whenever sqlerror exit sql.sqlcode
select fn_get_current_date_for_region('USA') from dual;
............add oracle statement to print "RETURN_VALUE IS :BLABLA"
 ENDSQL

At your ksh script

ORA_RET=`grep RETURN_VALUE mylog.txt  | cut -f2 -d":" `
echo $ORA_RET

0
 
LVL 7

Expert Comment

by:glassd
ID: 9874293
Oops, I completely misread the question. What an idiot!!!
0
 
LVL 14

Expert Comment

by:chris_calabrese
ID: 9875163
Probably better off doing this from Perl using the DBI module for Oracle (see www.cpam.org).

You'll have much better ability to get return values, error codes, row counts, etc., etc.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:HamdyHassan
ID: 9875298
chris_calabrese:
Could you provide an exmaple for that? I want to learn that also
0
 
LVL 1

Expert Comment

by:benpung
ID: 9956277
maybe i'm off base here, but would something like this work for your original question?

B_YOUR_VARIABLE=""
B_YOUR_VARIABLE=`sqlplus -s $LOGON_NAME/$LOGON_PASSWD @$HOME/YOUR_DIRECTORY/YOUR_SCRIPT.sql`

i use this to get the value returned by sql scripts into UNIX variables. just a thought....
0
 
LVL 18

Expert Comment

by:liddler
ID: 10241374
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ  No refund

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

liddler
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10300463
PAQed - no points refunded (of 30)

Computer101
E-E Admin
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
Introduction: The undo support, implementing a stack. Continuing from the eigth article about sudoku.   We need a mechanism to keep track of the digits entered so as to implement an undo mechanism.  This should be a ‘Last In First Out’ collec…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now