11-05-2007
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 statement to parse through the records in Table1 and load the necessary results into a Table. (Table2). This works fine too.
Here is how my script looks like...
# Always truncate table 1 before loading data.
print "truncate table table1;" | sqlplus -s userid/password@db>$now.log
sqlldr userid/password@db control=dbload.ctl>>$now.log
# Always truncate table 2 before loading data.
print "truncate table table2;" | sqlplus -s userid/password@db>>$now.log
# Parse through Table1 and load only necessary records to Table2.
print "insert into table2 select id, min(STARTDATE) as startdate from table1 group by id, to_char(STARTDATE,'mm/dd/yyyy');" | sqlplus -s userid/password@db>>$now.log
I call this script myscript.sh, which will be run via crontab every 15 mins.
Here are my questions:
1) Note how I establish a database connection to execute every sql I need to run? I am thinking there should be an easy way to this. I looked into the "here documents" but not sure how I can use them above. Do I really need to use the "here document" or is my script good the way as it is now?
Would it open and close the db connections for each sql correctly?
If I do use the "here document", do I need to have one for the sqlldr and the other for all the sqlplus part?
Please advise best way to go ahead.
Thanks
RG
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Oracle and Scripting gurus,
I need some help with this script...
I am trying to add the query
SELECT * FROM
ALL_SYNONYMS
WHERE SYNONYM_NAME = 'METADATA'
in the current script....
Read the result set and look for the TABLE_NAME field. If the field is pointing to one table eg.... (18 Replies)
Discussion started by: madhunk
18 Replies
2. UNIX for Advanced & Expert Users
Hi all,
In a shell script I need to pass two parameters to a pl/sql script and get the ouput of the pl/sql script and use it in shell script.
For example
Shell script : test.sh
PL/SQL script : get_id.sql parameter1 parameter2
Actually get_id.sql has a select statement something... (1 Reply)
Discussion started by: lijju.mathew
1 Replies
3. Shell Programming and Scripting
Hello everybody
I need help calling sql file from shell script.
Can anyone help me creating a small shell script which calls an sql file .
The .sql file should contain some select statements like
select emp_no from emp_table;
select emp_id from emp_table;
And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies
4. Shell Programming and Scripting
hi all,
i have a function which will take i/p as a ddl sctipt as i/p and execute it,
let
function execute_sql
{
db_var="$1"
v_cnt=`sqlplus -s XXXXX/XXXXX@aXXX << ENDSQL | sed -e "s/Connected\.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off serveroutput on size... (4 Replies)
Discussion started by: manas_ranjan
4 Replies
5. Shell Programming and Scripting
Dear All,
I want to call an sql script within a unix shell script. I want to pass a parameter into the shell script which should be used as a parameter in teh sql script.
e.g
$ ./shell1.sh 5000129
here 5000129 is a prameter
inside shell script i am calling one sql script
e.g. ... (2 Replies)
Discussion started by: Radhe
2 Replies
6. UNIX for Advanced & Expert Users
Hi
I have a shell script that call a sql file. The sql file will create a spool file.
My requirement is, when ever i get an OS error like file not found. I have to log it in a log file.
Could some who worked in a like scenario help me by giving the code sample.
Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies
7. Shell Programming and Scripting
Hi,
I want to call a sql file in my shell script. see the below code:-
if ]
then
(
isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://"
${S_PWD}
set nocount on
go
use ${S_DB}
go
// need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies
8. Shell Programming and Scripting
Hi,
I am calling a sql file script.sql from shell script and passing few parameters also as shown below:
sqlplus -S id/password @script.sql $param1 $param2
Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as... (7 Replies)
Discussion started by: anil029
7 Replies
9. UNIX for Advanced & Expert Users
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
10. Shell Programming and Scripting
Hi,
i have one simple PL/SQL Block and i have saved it as .sql file, which i am trying to call from UNIX script.
PL/SQL block structure
CONNECT DB_NAME/PWD@Database
whenever SQLERROR EXIT 1;
Declare
..Variables...
BEGIN
--Code--
exception
END;
exit;
I have save this block as... (3 Replies)
Discussion started by: abhii
3 Replies
LEARN ABOUT REDHAT
dbi::shell
DBI::Shell(3) User Contributed Perl Documentation DBI::Shell(3)
NAME
DBI::Shell - Interactive command shell for the DBI
SYNOPSIS
perl -MDBI::Shell -e shell [<DBI data source> [<user> [<password>]]]
or
dbish [<DBI data source> [<user> [<password>]]]
DESCRIPTION
The DBI::Shell module (and dbish command, if installed) provide a simple but effective command line interface for the Perl DBI module.
DBI::Shell is very new, very experimental and very subject to change. Your milage will vary. Interfaces will change with each release.
TO DO
Proper docs - but not yet, too much is changing.
"/source file" command to read command file. Allow to nest via stack of command file handles. Add command log facility to create batch
files.
Commands: load (query?) from file save (query?) to file
Use Data::ShowTable if available.
Define DBI::Shell plug-in semantics. Implement import/export as plug-in module
Clarify meaning of batch mode
Completion hooks
Set/Get DBI handle attributes
Portability
Emulate popular command shell modes (Oracle, Ingres etc)?
COMMANDS
Many commands - few documented, yet!
help
/help
chistory
/chistory (display history of all commands entered)
/chistory | YourPager (display history with paging)
clear
/clear (Clears the current command buffer)
commit
/commit (commit changes to the database)
connect
/connect (pick from available drivers and sources)
/connect dbi:Oracle (pick source from based on driver)
/connect dbi:YourDriver:YourSource i.e. dbi:Oracle:mysid
Use this option to change userid or password.
current
/current (Display current statement in the buffer)
do
/do (execute the current (non-select) statement)
dbish> create table foo ( mykey integer )
dbish> /do
dbish> truncate table OldTable /do (Oracle truncate)
drivers
/drivers (Display available DBI drivers)
edit
/edit (Edit current statement in an external editor)
Editor is defined using the enviroment variable $VISUAL or $EDITOR or default is vi. Use /option editor=new editor to change in the
current session.
To read a file from the operating system invoke the editor (/edit) and read the file into the editor buffer.
exit
/exit (Exits the shell)
get
/get (Retrieve a previous command to the current buffer)
go
/go (Execute the current statement)
Run (execute) the statement in the current buffer. This is the default action if the statement ends with /
dbish> select * from user_views/
dbish> select table_name from user_tables
dbish> where table_name like 'DSP%'
dbish> /
dbish> select table_name from all_tables/ | more
history
/history (Display combined command and result history)
/history | more
option
/option [option1[=value]] [option2 ...]
/option (Displays the current options)
/option MyOption (Displays the value, if exists, of MyOption)
/option MyOption=4 (defines and/or sets value for MyOption)
perl
/perl (Evaluate the current statement as perl code)
quit
/quit (Leaves shell. Same as exit)
redo
/redo (Re-execute the previously executed statement)
rhistory
/rhistory (Display result history)
rollback
/rollback (rollback changes to the database)
For this to be useful, turn the autocommit off. /option autocommit=0
table_info
/table_info (display all tables that exist in current database)
/table_info | more (for paging)
trace
/trace (set DBI trace level for current database)
Adjust the trace level for DBI 0 - 4. 0 off. 4 is lots of information. Useful for determining what is really happening in DBI. See
DBI.
type_info
/type_info (display data types supported by current server)
AUTHORS and ACKNOWLEDGEMENTS
The DBI::Shell has a long lineage.
It started life around 1994-1997 as the pmsql script written by Andreas Konig. Jochen Wiedmann picked it up and ran with it (adding much
along the way) as dbimon, bundled with his DBD::mSQL driver modules. In 1998, around the time I wanted to bundle a shell with the DBI, Adam
Marks was working on a dbish modeled after the Sybase sqsh utility.
Wanting to start from a cleaner slate than the feature-full but complex dbimon, I worked with Adam to create a fairly open modular and very
configurable DBI::Shell module. Along the way Tom Lowery chipped in ideas and patches. As we go further along more useful code and concepts
from Jochen's dbimon is bound to find it's way back in.
COPYRIGHT
The DBI::Shell module is Copyright (c) 1998 Tim Bunce. England. All rights reserved. Portions are Copyright by Jochen Wiedmann, Adam Marks
and Tom Lowery.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
perl v5.8.0 2002-11-29 DBI::Shell(3)