How to store results of multiple sql queries in shell variables in ksh?

Shell Programming and Scripting
1

Hi,

I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else.

Thanks in advance..
Cheers

2

Hi,

have you tried to store the queries' results in files and then going to parse those files in order to get the values for the intended variables?

see ya
fra

3

hi.
I have bulk updates running like

Update table1 set value1.....

!echo update completed at `date`

Update table 2 set value2..
!echo update completed at `date`

But both times date value is the same. So i thought using a query select sysdate from dual

How to do that? Any help would b appreciated.

Thanks in advance
Cheers

4

Hi,

if the database is Oracle, you may use:
Update...
...
SET SERVEROUTPUT ON
DEFINE MYDATE = _DATE
exec dbms_output.put_line('Update completed at ' || TO_CHAR(MYDATE) );
SET SERVEROUTPUT OFF
...

References:
DEFINE

see ya
fra

5

Hi,
I did this

#!/usr/bin/ksh



DB=abcd

USER=ghij
PASS=jjkil

DATA_PATH=./DATA
LOG_PATH=./LOG

SYear=`date +%Y`
SMonth=`date +%m`
SDay=`date +%d`
LOG_FILE=test.log
RPT_FILE=test.txt

sqlplus -s $USER/$PASS@$DB<<EOF1 > $LOG_PATH/$LOG_FILE 2>&1

SET SERVEROUTPUT ON

SET HEADING OFF
SET FEEDBACK OFF
SET WRAP OFF
SET PAGES 0
SET LINESIZE 32700
DEFINE MYDATE = _DATE

!echo " "`date` > home/LOG/test.log



exec dbms_output.put_line('Update completed at ' || TO_CHAR(MYDATE) );
SET SERVEROUTPUT OFF




QUIT
EOF1

When I run it I get this error

ERROR at line 1:
ORA-06550: line 1, column 62:
PLS-00201: identifier 'MYDATE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Please help. I dont want to use pl/sql . Is there any other way or I can modify this one.

Thanks in advance
Cheers

6

You can spool the output:

sqlplus -s ${user}@${pass}/@{inst} << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000
spool table_update.log
update table1 set value1...
select 'Update completed at ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
update table2 set value2...
select 'Update completed at ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
spool off;
exit;
EOF

You can later read this spool file and fetch the values into a variable if you want. I hope this helps.

7

Hi,
thanks for your reply.
I am trying to output the results in a log file for which i am doing
sqlplus uname/pwd@dbname << EOF > logfile

update 1. --This result should go in log file
!echo "completed successfully at" sydate -- the result to be stored in a separate file.

So i wanted something of this sort:-

variable1=`select to_char(sydate,ddmmyyyyhhmiss) from dual;
followed by variable1 >> another_file_which_is_not_logfile

Thanks in advance
Cheers