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
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
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
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......
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.
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???
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
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
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
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]}