Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: how to execute multiple .sql scripts from within a shell script using sqlplus
Special Forums UNIX and Linux Applications how to execute multiple .sql scripts from within a shell script using sqlplus Post 302522279 by jsmithstl on Saturday 14th of May 2011 05:50:36 AM
Old 05-14-2011
This will work for you.

shell script:
Code:
$cat dbms_output.ksh
#!/bin/ksh

ORA_DB=mydb
ORA_USER=myuser
ORA_PWD=$(cat ~/.pwd/.${ORA_USER}_${ORA_DB})

SQLFILE=~/scripts/sql/dbms_output.sql
LOGFILE=~/scripts/logs/dbms_output.log

sqlplus -s /nolog <<-EOF >> ${LOGFILE}
   connect $ORA_USER/$ORA_PWD@$ORA_DB

   set serveroutput on format wrapped
   set pages 0 lines 300 trimout on trimspool on
   set echo off feedback off sqlprompt ''

   @$SQLFILE

   exit

EOF

exit

sql script:
Code:
$ cat ../sql/dbms_output.sql 
begin

  dbms_output.put_line(to_char(sysdate, 'yyyymmdd hh24:mi')||'|'||'Testing dbms_output to a logfile');

end;
/

results:
Code:
$ ./dbms_output.ksh 
$ cat ../logs/dbms_output.log
20110514 03:46|Testing dbms_output to a logfile

Last edited by jsmithstl; 05-14-2011 at 06:51 AM.. Reason: typo
 

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

how can i install sqlplus and execute some sql commands

hi everybody, i am Talip, a begginner at unix based systems and i have a problem (actually, we may think myself as the problem, in this situation). i am not sure if this is the correct platform for my questions. if it is not please forgive me about this inappropriate mail. what i have: *... (2 Replies)
Discussion started by: talipk
2 Replies

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

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

5. Shell Programming and Scripting

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 echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

6. Solaris

cannot execute sqlplus in shell script under cron

Hi, I have a bourne shell script that needs to login into sqlplus and execute a stored procedure. I need this shell script to be run under a user crontab. It works fine if run under normal shell prompt. But it gave error message in my log file when run under crontab: SQL*Plus: Release... (4 Replies)
Discussion started by: joe_x
4 Replies

7. Shell Programming and Scripting

Find and execute shell scripts in multiple sub directories in parallel

I have one parent directory and within that parent directory there are several other sub-directories and within those sub-directories there are several other "large number" of sub-directories. All the sub directories have a shell script in them with a common file name execute_command.sh I want... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

8. Shell Programming and Scripting

Script to execute multiple scripts

Hi All, I have 4 scripts to execute. Each one take anywhere from 3 -9 hours to run depending on what it's doing. I'm running one at a time then check back periodically to see if it's still going and if not, run the other. How can I put these scripts into another script so that they are... (5 Replies)
Discussion started by: bbbngowc
5 Replies

9. Shell Programming and Scripting

How to execute 10 sql files from a folder through sqlplus in shell script?

I am new to shell scripting and i want to know how to execute the *.sql files from a folder through sqlplus in shell script and files should be execute in sequentially one by one while execution if any ORA error it has to exit from sqlplus session 1) scripts from external folder 2) logs has... (1 Reply)
Discussion started by: sreekanth Reddy
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 DEBIAN

cvm-mysql

cvm-mysql(8)						      System Manager's Manual						      cvm-mysql(8)

NAME

       cvm-mysql - MySQL module

SYNOPSIS

       cvm-mysql

CREDENTIALS

       Pass phrase

DESCRIPTION

       This module queries a MySQL database for the account name, compares the stored pass phrase with the given one using crypt(3).

CONFIGURATION VARIABLES

       CVM_MYSQL_DEFAULT_FILE
	      The  full  path of the defaults file to read if the following variable is set.  If not set, the file $HOME/.my.cnf will be read (the
	      MySQL default).

       CVM_MYSQL_DEFAULT_GROUP
	      If set, the module will read connection default options from the named group in the defaults file as above

       CVM_MYSQL_HOST
	      The hostname or IP of the MySQL server.  If not set, a connection to the local host is assumed.

       CVM_MYSQL_USER
	      The MySQL login ID to connect as.  If not set, the invoking user is assumed.

       CVM_MYSQL_PASS
	      The password for the above user.

       CVM_MYSQL_DB
	      The database name, must be set.

       CVM_MYSQL_PORT
	      The port number for the TCP/IP connection (only used if the server is not local).

       CVM_MYSQL_POSTQ (optional)
	      The SQL query to execute after the credentials have been validated, see cvm-sql(7).

       CVM_MYSQL_PWCMP (optional)
	      The password comparison module to use.

       CVM_MYSQL_SOCKET
	      The path to the socket that should be used for connections to a local server.

       CVM_MYSQL_QUERY (optional)
	      The SQL query to issue to retrieve the row containing the account information from the database, see cvm-sql(7).

SEE ALSO

       cvm-sql(7), cvm-pgsql(8), cvm-pwfile(8), cvm-qmail(8), cvm-unix(8), cvm-vmailmgr(8),  cvm-benchclient(8),  cvm-checkpassword(8),  cvm-test-
       client(8)

	http://untroubled.org/pwcmp/pwcmp.html
	http://untroubled.org/cvm/cvm.html

																      cvm-mysql(8)
All times are GMT -4. The time now is 05:23 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy