Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-12-03
8
Medium Priority
?
1,818 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
[X]
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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

670 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