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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to store results of multiple sql queries in shell variables in ksh?
# 1  
Old 11-24-2012
How to store results of multiple sql queries in shell variables in ksh?

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  
Old 11-24-2012
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  
Old 11-24-2012
not able to get it

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  
Old 11-24-2012
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  
Old 11-25-2012
Hi,
I did this
Code:
#!/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  
Old 11-25-2012
Suggestion

You can spool the output:
Code:
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  
Old 11-26-2012
Quote:
Originally Posted by bipinajith
You can spool the output:
Code:
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.


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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

How to run multiple Queries in a ksh Script?

How to run multiple Queries in a ksh Script I have a KSH script that has one SQL Query and generates and emails output of the query in HTML format. I want to change the script so that it has three SQL queries and the last query generates and emails the HTML output page of just that query. So far... (5 Replies)
Discussion started by: JolietJake
5 Replies

3. Shell Programming and Scripting

Help with storing the output of multiple sql queries to a file

Hi All, I have a file queries.txt as follows : SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; SELECT... (2 Replies)
Discussion started by: SriRamKrish
2 Replies

4. Linux

How to store count of multiple queries in variables in a shell script?

how to store the count of queries in variables inside a filein shell script my output : filename ------- variable1=result from 1st query variable2=result from 2nd query . . . . (3 Replies)
Discussion started by: sanvel
3 Replies

5. Shell Programming and Scripting

Storing the SQL results in array variables

Requirement 1) I need to execute 15 SQL queries in oracle through linux script. All these query results needs to be stored in array variables. Requirement 2) And these 15 queries needs to be executed in parallel. Requirement 3) Once all the queries executed then the shell script should... (3 Replies)
Discussion started by: Niranjancse
3 Replies

6. Shell Programming and Scripting

How to Assign SQL Query Results to Variables in Linux?

Hi, I am new to linux... How to Assign SQL Query Results to Variables in Linux,i want ti generate it in param files, Can anyone please explain me. Ex: SQL> Select * from EMP; O/P: Emp_No Emp_Name 1 AAA 2 BBB 3 CCC and I want expected... (5 Replies)
Discussion started by: Sravana Kumar
5 Replies

7. Shell Programming and Scripting

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at... (2 Replies)
Discussion started by: gary_w
2 Replies

8. Shell Programming and Scripting

Writing sql results to file using ksh -nevermind

I'm having problems with writing my sql results to a file: sqlplus -S username/password@DB <<!! set echo off set verify off set showmode off set feedback off set timing off set linesize 250 set wrap off set pagesize 0 set newpage none set tab off set trimspool on set colsep... (1 Reply)
Discussion started by: avillanueva
1 Replies

9. UNIX for Dummies Questions & Answers

store SQL statements and results in a file

Hello Guys... I want a small help from you guys. Actually in Oracle, we are having a utlity called spool through which can store whatever SQL statements executed and other queries and the output of those queries in a file So, similarly in Unix, if I start a session executing a number of Unix... (2 Replies)
Discussion started by: mraghunandanan
2 Replies

10. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you (1 Reply)
Discussion started by: user71408
1 Replies
Login or Register to Ask a Question