Execute multiple SQL scripts from single SQL Plus connection


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Execute multiple SQL scripts from single SQL Plus connection
# 1  
Old 10-19-2010
Execute multiple SQL scripts from single SQL Plus connection

Hi!
I would like to do a single connection to sqlplus and execute some querys.
Actually I do for every query one connection to database

i.e

Code:
echo 'select STATUS from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

echo 'select VERSION from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

echo 'select VERSION from v$instance;
exit' > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT


is it posible?
thanks
# 2  
Old 10-19-2010
Why don't you do this?
Code:
echo "select STATUS from v$instance;
select VERSION from v$instance;
select VERSION from v$instance;
" > $SQL_FILE

sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT

# 3  
Old 10-19-2010
I need to save the result in variables or files with the same name ($SELECT_RESULT)
# 4  
Old 10-25-2010
anybody know?
# 5  
Old 10-25-2010
Quote:
I need to save the result in variables or files with the same name ($SELECT_RESULT)
This sentence does not make any sense. Also your second and third queries are the same.

Please give a good clear example making it clear what is a file and what is a variable. Please make is clear whether any or all of them are different for each query.


You could combine the queries into one query:
Code:
select version,status from v$instance;

Also personally I would use an Oracle "spool" command to write the results to a file (i.e. not a Shell redirect).

Last edited by methyl; 10-25-2010 at 10:29 AM..
# 6  
Old 10-25-2010
Here's something that may help

This has been tested on Solaris using dtksh and Oracle 10g.

Construct the query so it returns rows as name=value, so they can be used in the shell script.
Read the query output a line at a time.
for each row returned, if it is not null, use eval to make it a shell variable.

Code:
#!/usr/dt/bin/dtksh
# Oracle environment is assumed to be set already.

unset version  # Remove variable names from the environment if they exist.
unset status

( sqlplus -s / <<EOF
  set heading off;
  select 'version='||'1.2' from dual;
  select 'status='||'UP' from dual;
  exit;
EOF
)|
while read line
do
 if [[ -n $line ]]
   then eval $line
 fi
done

print "Version: $version"
print "Status: $status"

output:
Code:
$ sptest
Version: 1.2
Status: UP
$

Hope this helps.
Gary
This User Gave Thanks to gary_w For This Post:
# 7  
Old 10-25-2010
I think it looks good. I'm going to try this.
thanks!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to use for loop to execute multiple .sql files while using SQLPLUS for db connection.?

Hello , Im calling every single file inside my script like 1.sql,2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting errorUnexpected EOF] , can anyone please help me out in this.. How i can use for loop to invoke my... (6 Replies)
Discussion started by: preethi87
6 Replies

2. UNIX and Linux Applications

how to execute multiple .sql scripts from within a shell script using sqlplus

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks any help would be appreciated :wall: (1 Reply)
Discussion started by: TRS80
1 Replies

3. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

4. Shell Programming and Scripting

SQL query in a loop with single sqlplus connection

Hi, I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data. Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Discussion started by: lsantacana
4 Replies

5. Shell Programming and Scripting

Not able to execute many SQL scripts within a shell script

I am using HP-UX: I have written a ksh script where I need to connect to sqlplus and execute few sql scripts. Part of this code is - sqlplus user/temp1234 <<! set serverout on set feedback off set pages 1000 set colsep , set echo off spool /home/supp1/pks/output.csv... (8 Replies)
Discussion started by: Sriranga
8 Replies

6. Shell Programming and Scripting

How to execute the multiple line sql using shell script

Hi All, Please help me to write a shell script to execute the below sql query. select c.account_no,b.bill_no,a.pay_type,(b.total_due + b.recvd + b.adjusted + b.disputed + b.transferred) as amt_not_billed,d.cash_on_delivery, (select j.bill_no from billinfo_T y, bill_t j where... (1 Reply)
Discussion started by: girish.raos
1 Replies

7. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

8. Programming

Single sql query to spool to multiple files

Is there anyway to spool my select statement into spool files of max 10000 records each? eg I have a select statement that will return 45000 records. A normal spool command will output the 45000 into just one spool file. How can I make sqlplus do this? 00001 - 10000 records --- spool... (3 Replies)
Discussion started by: Leion
3 Replies

9. UNIX for Dummies Questions & Answers

split a single sql file into multiple files

Hi,I have a single sql file containing many create table ddl's.Example: CREATE TABLE sec_afs ( rpt_per_typ_c char(1) NOT NULL, rpt_per_typ_t varchar(20) NULL, LOCK ALLPAGES go EXEC sp_primarykey 'sec_afs', rpt_per_typ_c go GRANT SELECT ON sec_afs TO developer_read_only... (5 Replies)
Discussion started by: smarter_aries
5 Replies

10. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies
Login or Register to Ask a Question