Skip to Main Content
  • Questions
  • Getting Value to a Unix Variable From Oracle Using Korn Shell Script

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vinod.

Asked: June 16, 2000 - 2:23 am UTC

Last updated: August 25, 2010 - 1:12 pm UTC

Version: Ora 7.3.4

Viewed 10K+ times! This question is

You Asked

Hi Tom, I am using Korn shell. My question is that how can i retrieve a value from a Oracle table and assign that value to a shell variable.

(eg)

#!/bin/ksh
temp_batch()
{
ABC=ANIL;
echo $ABC
sqlplus dw_dev/rainbow <<+
DECLARE
A VARCHAR2(10);
BEGIN
SELECT 'VINOD' INTO A FROM DUAL ;
'ABC':=A ; -- Here i want to assign the value of the pl/sql variable A to the Shell variable ABC
END;
/
+
}

Awaiting for ur reply.

Regds
Vinod

and Tom said...

Try:

#!/bin/ksh

ABC=ANIL;
echo $ABC

ABC=`sqlplus scott/tiger <<+ | grep '^-' | sed 's/-//'
set serveroutput on
DECLARE
A VARCHAR2(10);
BEGIN
SELECT 'VINOD' INTO A FROM DUAL ;
dbms_output.put_line( chr(10) || '-' || A );
END;
/
exit
+`
echo $ABC
===========================================================


(those are backticks around `sqlplus ..... and +`)





Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Getting Value to a Unix Variable From Oracle Using Korn Shell Script

Jim Brooks, May 14, 2003 - 2:56 pm UTC

thanks !!! I was using this archaic spool to file and grep method until I found this on your website ...



How to assign two values from SQL Plus to Unix Shell Variables

Ramesh, May 17, 2006 - 2:23 am UTC

hi tom,
How to assign two values from SQL Plus to Unix Shell Variables.

ex:
#!/bin/ksh
filename='';
record_cound='';
##########################################
####Log into SQLPLUS and execute the procedures
##########################################
sqlplus -s $login1<<EOF1
set serveroutput on size 1000000;
declare
record_count_sql number;
file_name_sql varchar2(50);
begin
test.get_value(:record_count_sql,:file_name_sql);
dbms_output.put_line('hi');
end;

how i can get the oracle record_count and file_name and assigned to shell variable.

regards
ramesh


Tom Kyte
May 17, 2006 - 6:56 am UTC

simple approach, a variation on a theme.

o put it into one as shown above (eg: dbms_output.put_line( chr(10) || '-' || A || '-' || B )

o then use sed twice to extract the bits of interest.


[tkyte@dellpe install]$ cat test.sh
#!/bin/ksh

ABC=`sqlplus scott/tiger <<+ | grep '^-' | sed 's/-//'
set serveroutput on
DECLARE
a number := 42;
b varchar2(20) := 'x.txt';
BEGIN
dbms_output.put_line( chr(10) || '-' || A || '-' || B );
END;
/
exit
+`
echo $ABC
N=`echo $ABC|sed s/-.*//`
M=`echo $ABC|sed s/.*-//`

echo $N
echo $M
[tkyte@dellpe install]$ ./test.sh
42-x.txt
42
x.txt


May be another way

Parag Jayant Patankar, May 17, 2006 - 9:36 am UTC

Hi Tom,

May be another way to get oracle values into unix variables

/usr/site/jcl>cat t.sql
sqlplus -s / <<!
set serveroutput on
DECLARE
a number := 42;
b varchar2(20) := 'x.txt';
BEGIN
dbms_output.put_line( chr(10) || '-' || A || '-' || B );
dbms_output.put_line( 'HELLO' );
END;
/
!
/usr/site/jcl> export t=`t.sql`

/usr/site/jcl>echo $t
-42-x.txt HELLO PL/SQL procedure successfully completed.

/usr/site/jcl>echo $t | awk -F ' ' '{print $1}'
-42-x.txt

/usr/site/jcl>echo $t | awk -F ' ' '{print $2}'
HELLO

Kindly comment.

thanks & regards
pjp

Tom Kyte
May 17, 2006 - 9:39 pm UTC

there are an INFINITE number of ways I am sure.

PERL?

Russ, May 17, 2006 - 4:43 pm UTC

Unless this is a one-off thing, a shell script is an ugly way to do this. With PERL, you can use the DBI and DBD::Oracle drivers to get to the database.

This is very clean, less error prone, easier to read. The result set can be an array. No need for sed or awk.

The advantage of using sqlplus this way is that the listener (or even the database) does not need to be up or open. If you know the database will be up, PERL will be easier. (It is installed by default on almost every Unix system these days)

Use of pies to load unix variable with sqlplus value

Michael Fairclough, August 04, 2006 - 9:48 am UTC

sqlplus -s $DBUSER/$DBPASS |&
print -p -- 'set feed off'
print -p -- 'set pages 0 head off verify off line 500'
print -p -- 'set term off time off'
print -p -- 'set trimspool on'
print -p -- "set sqlprompt ''"
print -p -- "select s.sid from v\$session s, v\$process p where s.paddr = p.addr and p.spid = $pid;"
read -p MY_SID
print -p -- "exit"
print -p -- "\n"
sleep 4

A simpler way

James Miller, November 10, 2006 - 12:09 pm UTC

Hello Tom,

I think DBMS_OUTPUT and PL/SQL is sometimes overused when a simple Sqlplus script will do :

x=`sqlplus -s user/password <<SQL_END
set head off
SELECT 'Bob','Dylan'
FROM DUAL
/
SQL_END`
echo $x | read a b
echo $a
echo $b

Gives :
Bob
Dylan

What do you think?

James

Tom Kyte
November 10, 2006 - 2:52 pm UTC

I presumed their real world example would be more complex (eg: there would be more in the plsql code)

also - by using dbms_output, it makes is easy to pick off the lines of data of interest (I tagged them with a leading -)

Output is not coming

Sudip Bhowmik, December 11, 2006 - 5:56 pm UTC

Hi Tom,
I've tried the way you do...but it will return nothing..Could you pl. tell me where is the problem:

<<code snippet>>
file_name=TEST
echo $file_name
file_name=`$ORACLE_HOME/bin/sqlplus -s $username/$password@$servicename >>output.log <<ENDOFSQL1
set pagesize 0 feedback off;
SELECT 'SAI-INT70-MERGED-' || TO_CHAR(cal.period_end_date,'mm-dd-yy') || '.dat'
FROM annuities_la.EN_DEFAULT_WORKING_PERIODS wor, annuities_la.EN_CALENDAR_PERIODS cal
WHERE wor.calendar_period_id = cal.calendar_period_id;
/
ENDOFSQL1`

echo $file_name

<<echo output>>
TEST
{blank} ##Nothing is coming after sql assignment

<<output.log >>
Connected.
SAI-INT70-MERGED-12-08-06.dat

Hope I'll get a reply soon......


Tom Kyte
December 11, 2006 - 7:24 pm UTC

maybe you try a full example we can run

and you tell us what you WERE EXPECTING to see.

and make it so we can run it too. You redirected everything to the output.log, not sure what you were expecting to see and where and why.

Sudip Bhowmik, December 11, 2006 - 8:37 pm UTC

yes...you are right...I'm changing this line
file_name=`$ORACLE_HOME/bin/sqlplus -s $username/$password@$servicename 2>>output.log <<ENDOFSQL1
with the following line
file_name=`$ORACLE_HOME/bin/sqlplus -s $username/$password@$servicename 2>>output.log <<ENDOFSQL1
whenever sqlerror exit $batch_fatal;
and now it is working fine.....
now only problem exists is that:
while excuting the following line
total="`awk '{print $0 }' $DIRECTORY_PATH/${file_name} | wc -l`"
I got the one error in screen. But it won't hamper anything and got the correct count.
The error I'm getting is as follows:
awk: can't open SAI-INT70-MERGED-12-08-06.dat
record number 4922

Could you pl. tell me what is the problem here.




Tom Kyte
December 12, 2006 - 6:43 am UTC

it cannot open that file???? seems pretty clear.




Sudip Bhowmik, December 13, 2006 - 8:15 pm UTC

No, it is not pretty much clear...It is working perfectly well means it getting the value through awk command and assigning it in the variable. Only problem it leaves the message in screen. Will it be possible if it unable to open the file but able to do the correct count???

Tom Kyte
December 15, 2006 - 8:04 am UTC

umm, read the message on your screen, it is not perfectly well - not at all.


this is a shell programming question for you to solve.

calling awk/sed in SQLPLUS

Ananth, August 18, 2010 - 9:30 am UTC

Hi Tom,

i wrote the ksh script like this

export db_login=<username>/<password>@<db>
export dFile="test.dat"
export TEMP="temp.dat"
typeset recCount=$(wc -l $dFile)

sqlplus -s $db_login <<!
set serveroutput on;
declare
recCount number := $(recCount);
entirerec varchar2(200);
begin
for i in 1..recCount
loop
entirerec:= '$(sed q $dFile; sed '1d' $dFile > $TEMP; mv $TEMP $dFile)';
dbms_output.put_line('Row '||i||': '||entirerec);
..
--some processin on entirerec..
..
end loop;
end;
/
!


But the problem am facing here is
entirerec in the for loop always has the first line of the file.
my intention was to read teh first line, delete the first line, but this is not happening.

can you suggest me any other alternative.

Note: the record count can be 10000 atmax.
-----

ThnX in advance

~ Ananth
Tom Kyte
August 19, 2010 - 1:51 am UTC

you cannot call sed or awk from plsql - you are not trying to call sed or awk from sqlplus (that would be done via ! or the host command).

You are running plsql, you do not call sed or awk from plsql.


why don't you tell us what you want your script to do, not how you want to do it.

Unix File Processing | Design Patterns

Ananth, August 20, 2010 - 1:13 am UTC

Hi Tom,

we have a file which has records (around 100000 records) in it. for every record we need to query the database and do some operations on it.

can you suggest any design pattern for the above said requirement.

Note: we call sqlplus from unix environment, DB is hosted on another unix box.

~Ananth
Tom Kyte
August 25, 2010 - 12:15 pm UTC

sure, the design pattern would NOT BE to "for every record, we need to query the database"

the design pattern would either be:

a) map the file as an external table, so you can "select * from the_file" in sqlplus or any tool. and then

b) JUST JOIN to the needed data - "select * from the_file, the_other_table(s) where...."

(that would be appropriate if you do this ONCE IN A WHILE - to process a new file - and you only read the file once and then are done with it - not over and over and over again)


or it would be:

a) map the file as an external table, so you can "insert into a real_table select * from the_file"

b) JUST JOIN the newly loaded data to the existing data in other tables in bulk



do not, repeat DO NOT, run 100,000 tiny little queries - run one big monster query that does everything you need.

Unix File Processing | Design Patterns

MF, August 20, 2010 - 3:34 pm UTC

Sounds like you should look up external tables.

SQLPlus and UNIX

Ananth, August 22, 2010 - 2:40 pm UTC

External Tables..? can you just tel me hw do i go ahead with it..?

~Ananth
Tom Kyte
August 25, 2010 - 1:12 pm UTC

it is called "reading"

see below, I see someone already followed up with that.


You can also search on this site (or many thousands of other sites :) )

external table

Michel Cadot, August 25, 2010 - 12:19 pm UTC

Dumitru, November 30, 2010 - 8:58 am UTC

here's another way of doing it

a=`echo "select field_name from TABLE where table_name='TABLE_NAME';" | sqlplus -s $ORACLE_CON_STRING
echo $a
set -A a_arr $a

export CONF_FILE_PATH=${a_arr[2]}
export CONF_FILE_NAME=${a_arr[3]}

Dumitru, November 30, 2010 - 9:01 am UTC

last one was missing a backtick

a=`echo "select field_name from TABLE where table_name='NAME';" | sqlplus -s $ORACLE_CONN_STRING`
echo $a
set -A a_arr $a

export CONF_FILE_PATH=${a_arr[2]}
export CONF_FILE_NAME=${a_arr[3]}

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here