Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2003-12-03
8
1,777 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Perl Awk Need Help 3 128
notReplace  challenge 53 141
fix34  challenge 9 140
I could not set window to top 4 20
In this article, I'll describe -- and show pictures of -- some of the significant additions that have been made available to programmers in the MFC Feature Pack for Visual C++ 2008.  These same feature are in the MFC libraries that come with Visual …
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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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