• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1987
  • Last Modified:

Passing values from SqlPlus to ksh

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
GeekMan
Asked:
GeekMan
  • 6
  • 3
1 Solution
 
yokelCommented:
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
 
GeekManAuthor Commented:
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
 
yokelCommented:
You need the semi-colan after the select statement.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
yokelCommented:
semi-colon even!
Just as if you were in sqlplus
0
 
yokelCommented:
Sorry your way works as well! ie put the "/" on the next line.
H'mm I will have to look..
0
 
yokelCommented:
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
 
yokelCommented:
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
 
GeekManAuthor Commented:
I believe it's:
HP-UX V.5.1 true64
0
 
GeekManAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now