Solved

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

Posted on 2003-12-03
8
1,768 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
no14 challenge 14 66
tripleUp challenge 7 78
MaxSpan challenge 9 94
Detect file exist or not 3 153
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

815 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

9 Experts available now in Live!

Get 1:1 Help Now