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 OSF1

sh

sh(1)							      General Commands Manual							     sh(1)

NAME

       sh - Shell, the standard command language interpreter

DESCRIPTION

       [Tru64  UNIX]  Tru64  UNIX  provides two command interpreters with the name sh.	The XCU5.0 and POSIX.2 compliant command interpreter sh is
       available in the file /usr/bin/posix/sh and is described in the sh(1p) reference page.  The Bourne shell,  historically	known  as  sh,	is
       available in the file /usr/bin/sh and is described in the sh(1b) reference page.

       [Tru64 UNIX]  Your initial, or login, shell is determined by your entry in the file /etc/passwd. This file can be changed only by your sys-
       tem administrator. You must use whatever procedures are in place at your location to have this entry changed.

       [Tru64 UNIX]  If available on your system, you may use the passwd -s or the chsh commands to change your login shell.

									  Note

       This option is not available if your site manages passwords through the Network Information Service (NIS) facility.  Check with your system
       administrator.

       [Tru64 UNIX]  Subsequent shells spawned from the initial shell depend on the value in the environment variable BIN_SH.  If this variable is
       set to xpg4, the POSIX shell is started. If this variable is set to svr4, an SVR4 compliant version of the shell is started. If this  vari-
       able  is  unset,  the  Bourne shell is started. If this variable is set to any other value, an error is reported and the results are unpre-
       dictable. See the EXAMPLES section for information on setting this variable.

NOTES

       [Tru64 UNIX]  With Tru64 UNIX Version 4.0 the Korn shell, /usr/bin/ksh is the same as the POSIX shell /usr/bin/posix/sh.

RESTRICTIONS

       [Tru64 UNIX]  The file /etc/shells must include entries for both the POSIX shell /usr/bin/posix/sh and the Bourne  shell,  /usr/bin/sh.	If
       this file is incorrect, see your system administrator.

EXAMPLES

       Using  the  Bourne,  Korn,  or  POSIX  shell, to set the variable BIN_SH to use the POSIX/ XCU5.0compliant shell, enter: BIN_SH=xpg4 export
       BIN_SH Using the Bourne, Korn, or POSIX shell, to set the variable BIN_SH to use the SVR4 compliant shell, enter: BIN_SH=svr4 export BIN_SH
       Using the Bourne, Korn, or POSIX shell, to unset the variable BIN_SH, enter: unset BIN_SH Using the C/ shell, to set the variable BIN_SH to
       use the POSIX/XCU5.0 compliant shell, enter: setenv BIN_SH xpg4 Using the C/ shell, to set the variable BIN_SH to use  the  SVR4  compliant
       shell, enter: setenv BIN_SH svr4 Using the C/ shell, to unset the variable BIN_SH, enter: unsetenv BIN_SH

FILES

       User profile.  Contains user information, including the login shell name.  Contains the names of available and permitted shells.

SEE ALSO

       Commands:  csh(1), ksh(1), Bourne shell sh(1b), POSIX shell sh(1p), passwd(1)

       Files:  passwd(4), shells(4)

       Standards:  standards(5)

																	     sh(1)
All times are GMT -4. The time now is 02:04 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy