Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Execute multiple SQL scripts from single SQL Plus connection
Top Forums Shell Programming and Scripting Execute multiple SQL scripts from single SQL Plus connection Post 302466084 by gary_w on Monday 25th of October 2010 11:27:45 AM
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:
ataufique
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT CENTOS

setup-nsssysinit

SETUP-NSSSYSINIT(1)					     Network Security Services					       SETUP-NSSSYSINIT(1)

NAME

       setup-nsssysinit - Query or enable the nss-sysinit module

SYNOPSIS

       setup-nsssysinit [on] [off] [status]

DESCRIPTION

       setup-nsssysinit is a shell script to query the status of the nss-sysinit module and when run with root priviledge it can enable or disable
       it.

       Turns on or off the nss-sysinit module db by editing the global PKCS #11 configuration file. Displays the status. This script can be
       invoked by the user as super user. It is invoked at nss-sysinit post install time with argument on.

OPTIONS

       on
	   Turn on nss-sysinit.

       off
	   Turn on nss-sysinit.

       status
	   returns whether nss-syinit is enabled or not.

EXAMPLES

       The following example will query for the status of nss-sysinit:

		   /usr/bin/setup-nsssysinit status

       The following example, when run as superuser, will turn on nss-sysinit:

		   /usr/bin/setup-nsssysinit on

FILES

       /usr/bin/setup-nsssysinit

SEE ALSO

       pkg-config(1)

AUTHORS

       The nss libraries were written and maintained by developers with Netscape, Red Hat, Sun, Oracle, Mozilla, and Google.

       Authors: Elio Maldonado <emaldona@redhat.com>.

LICENSE

       Licensed under the Mozilla Public License, v. 2.0. If a copy of the MPL was not distributed with this file, You can obtain one at
       http://mozilla.org/MPL/2.0/.

nss 3.15.4							   17 June 2014 					       SETUP-NSSSYSINIT(1)
All times are GMT -4. The time now is 06:34 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy