Shell Script for call a procedure in Oracle DB

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell Script for call a procedure in Oracle DB
# 1  
Old 07-23-2009
Shell Script for call a procedure in Oracle DB

Hi everyone!

I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB).
I have to create a shell script to execute that procedure for each txt file in that directory. Anyone have some tips for me to do that???


# 2  
Old 07-24-2009
IF you mean call a stored procedure search the forums for 'oracle shell'. We get this type of question dozens of times a year.
# 3  
Old 07-24-2009
Hi Renato,

In your shell script add below code

sqlplus -s <<EOFSQL
select * from emp where ename=$1 and eno=$2; 

please replace select query with your proceure execution sysntax


Last edited by DukeNuke2; 07-24-2009 at 05:47 AM..
# 4  
Old 07-24-2009
I know how to do for a single .txt file, the fact is, I have to do something like a FOR command for all txt files in that directory, but can I do that???

for a single file I create a procedure.sql with the lines:

exec prc_cargatxt('TESTE_SUPERVISAO','PRECOLETA.1502_17.06.2009_16h19m13s.txt');

and in the batch file I put:

sqlplus username/password@dbname @C:\procedure.sql

its working correct, but how to do for all files???

# 5  
Old 07-24-2009
Hello renatoal,

for filename in `ls`
sqlplus username/password@dbname @${filename}


Last edited by Franklin52; 07-24-2009 at 05:31 PM.. Reason: urls removed
# 6  
Old 07-26-2009
Originally Posted by
Hello renatoal,

for filename in `ls`
sqlplus username/password@dbname @${filename}

That's not a very efficient way of doing things.
First of all, you'll need an "exit" in there so as to exit from sqlplus and get ready for the next iteration with a new filename. (Otherwise, the script opens up sqlplus, runs the script and just waits for you to supply the next input to sqlplus.)

So the code would be something like this:

$ cat
for i in data*.txt ; do
sqlplus test/test @procedure.sql $i <<EOF 1>/dev/null 2>&1


Here, "procedure.sql" is a script similar to the one posted by the OP.

$ cat procedure.sql
exec prc_read_data('&1')

It calls the stored procedure "prc_read_data" which takes in a file name as input. (Furthermore, the procedure opens the file,reads the data and loads into a table.)

And there are 100 such files that match the pattern "data*.txt":

$ ls -1 data*.txt | wc -l

The problem with your approach is that it executes the "sqlplus" binary 100 times, one per iteration. So there are 100 connections, 100 executions, and 100 disconnections.

What you'd want to do is - 1 connection to Oracle, 100 executions, 1 disconnection. And for that you'd need something more powerful - Perl, for instance:

$ cat
#!/usr/bin/perl -w
use DBI;
# get the database handle
$dbh = DBI->connect("dbi:Oracle:xe","test","test");             # connect once
# now loop through the files that match the glob pattern
while (defined($fname=glob($dir))) {                            # start looping through files
  $sth = $dbh->prepare("begin prc_read_data('$fname'); end;");  # prepare statement within loop
  $sth->execute();                                              # execute statement within loop
}                                                               # end loop
$dbh->disconnect();                                             # and disconnect once


This script uses the Perl DBI module, and is pretty self-explanatory.

For the 100 file testcase, the execution times are shown below:

$ time .

real    0m16.963s
user    0m4.540s
sys    0m2.304s
$ time perl

real    0m0.828s
user    0m0.196s
sys    0m0.080s

Of course, if it is a one-time job, then the shell script approach may work fine for the task at hand. But if it is a scheduled, regular job, with the number of files potentially increasing down the line, then the OP may want to consider ways to minimize the overall execution time.

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell script to execute Oracle procedure and trigerring email on success and failure

Here is the shell script which need to trigger a stored procedure and when the record count is 0 then it should capture in the log that 0 duplicate records are found and if it's more than 0 then log with no of records. Also if there is any sqlerror then it should write the same in the log file and... (17 Replies)
Discussion started by: senmng
17 Replies

2. Shell Programming and Scripting

To call Oracle procedure by reading parameter file in UNIX

hi Guys, is there a way to pass parameter into oracle store procedure by reading date range in file and increment accordingly. Something like this file.txt 01-JAN-2015 03-JAN-2015 sqlplus -s user/pwd@DB execute TEST( to_date( '01-JAN-2015, '' ), to_date( '03-JAN-2015', ... (1 Reply)
Discussion started by: rohit_shinez
1 Replies

3. Shell Programming and Scripting

How to call stored procedure with CLOB out parameter from shell script?

I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Discussion started by: vel4ever
0 Replies

4. Shell Programming and Scripting

Execute a shell script from Oracle procedure

Hi Gurus, Want to execute a shell script from a oracle procedure and get the status of the same, any assistance in this regard will be appreciated. is script name which I want to execute from oracle procedure It should work something like below procedure test begin... (1 Reply)
Discussion started by: palanisvr
1 Replies

5. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak (0 Replies)
Discussion started by: jakSun8
0 Replies

6. Shell Programming and Scripting

how to call oracle stored procedure from unix shell

Hi i want to call a oracle stored procedure from unix (using bash shell). consider this is my oracle stored procedure with parameter create procedure testproc(name IN varchar, age IN Number, id OUT Number ) AS begin id=1; dbms_output.put.line('successfull validation') end;... (6 Replies)
Discussion started by: barani75
6 Replies

7. Shell Programming and Scripting

Invoking Oracle stored procedure in unix shell script

Here's a shell script snippet..... cd $ORACLE_HOME/bin Retval=`sqlplus -s <<eof $TPDB_USER/april@$TPD_DBCONN whenever SQLERROR exit 2 rollback whenever OSERROR exit 3 rollback set serveroutput on set pages 999 var status_desc char(200) var status_code... (1 Reply)
Discussion started by: hidnana
1 Replies

8. Shell Programming and Scripting

i want to call a oracle function in my shell script

i want to call a oracle function in my shell script (4 Replies)
Discussion started by: dineshr85
4 Replies

9. Shell Programming and Scripting

Calling an Oracle Stored Procedure from Unix shell script

hai, can anybody say how to call or to execute an oracle stored procedure in oracle from unix... thanks in advance.... for ur reply.... by, leo (2 Replies)
Discussion started by: Leojhose
2 Replies

10. Shell Programming and Scripting

run shell script from oracle store procedure

hi, this is urgent..can i run a shell script from store procedure without using java. (8 Replies)
Discussion started by: arnabb4u
8 Replies
Login or Register to Ask a Question