?
Solved

Passing values from SqlPlus to ksh

Posted on 2003-02-20
9
Medium Priority
?
1,972 Views
Last Modified: 2013-12-26
I would like to be able to return sqlplus results back to my Korn Shell script. The return values would not be huge, a single value or perhaps 20 - 30 values that I could load into an array.
I then want to run these values through some conditionals for further processing.

The pseudocode would basically look like this:
************************************************
run sqlplus script...
 (select fieldA from mytable where fieldB = 'Hello World')
MyLocalVar=fieldA...

If [[MyLocalVar = "A"]] then
    do some work.....
fi
*************************************************
The database is Oracle.
If anyone has any sample code on how to do this I would be grateful...Thanks.
0
Comment
Question by:GeekMan
[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
  • 6
  • 3
9 Comments
 
LVL 3

Accepted Solution

by:
yokel earned 400 total points
ID: 7987266
This works in ksh..

set -A yokel `sqlplus -s helpdesk/st0rm << EOF
select fieldA from mytable where fieldB = "Hello world";
EOF`

This puts the results into an array called yokel. First element is accessed with ${yokel[0]} ie.

echo "First Element = ${yokel[0]}"

To not get the column headings the output create a file called login.sql in the working directory and put the line "heading off"

You could do this in your script by having the line..

echo "heading off" > login.sql

before the "set -A.." line above and afterwards removing login.sql.

Hope this helps.
0
 

Author Comment

by:GeekMan
ID: 7987506
Here's where I am so far:
*****************************************
set -A yokel 'sqlplus -s <<EOF
$RDBMS_USER/$RDBMS_PASSWD@$RDBMS_HOST
set pause off
select toggle from CIN_CUST_LOAD where TOGGLE = 'A'
/
exit
EOF'

echo "First Element = ${yokel[0]}"
*****************************************
What I get back is the ksh code...no query results. If I increment the array index to [1] I get nothing.
I'm pretty sure the sqlplus is executing...As a test I removed the "set -A yokel" and got a return of A..., it just wasn't in a variable.
0
 
LVL 3

Expert Comment

by:yokel
ID: 7987595
You need the semi-colan after the select statement.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 3

Expert Comment

by:yokel
ID: 7987601
semi-colon even!
Just as if you were in sqlplus
0
 
LVL 3

Expert Comment

by:yokel
ID: 7987612
Sorry your way works as well! ie put the "/" on the next line.
H'mm I will have to look..
0
 
LVL 3

Expert Comment

by:yokel
ID: 7987670
Well I think I've taken the code you provided ubove changed the query to something on my database and it works fine.
I'm using the Korn Shell on Solaris 2.6
0
 
LVL 3

Expert Comment

by:yokel
ID: 7987681
If you are on returning one value, then you can change the code to
$yokel=`sqlpluse -s <<EOF
...
EOF`

Even if gettting back mutiple entries you can always use sed/awk/cut to parse the output.
0
 

Author Comment

by:GeekMan
ID: 7988455
I believe it's:
HP-UX V.5.1 true64
0
 

Author Comment

by:GeekMan
ID: 7993551
You were correct with your solution. Our environment here is 95% windows so I was editing my files with WSFtp and WordPad. I was using single quotes (being primarily a windows/VB developer) and didn't realize your example contained "back tics".
When I switched to the back tics it all worked. I had to add 2 other parameters in my SQL (heading off, pagesize 0), the final code looked like this:
************************************
RETVAL=`sqlplus -s <<EOF
$RDBMS_USER/$RDBMS_PASSWD@$RDBMS_HOST
set pause off heading off pagesize 0
select toggle from CIN_CUST_LOAD where TOGGLE = 'A'
/
exit
EOF`

echo "First Element = $RETVAL"
************************************

As you mentioned, since I was returning a single value, I skipped loading the value into an array.

Thanks..
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

771 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