Executing Oracle Stored Procs from Korn Shell


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Executing Oracle Stored Procs from Korn Shell
# 1  
Old 08-05-2003
Network Executing Oracle Stored Procs from Korn Shell

Can someone tell me how to execute an Oracle Stored Procedure from a Korn Shell Script.

Previously, I'm able to execute standard sql using the following:-

The_Output=`sqlplus................. << EOF
select count(*) from abc
/
EOF`

Can I use sqlplus for the Stored Proc ?

Do I need to EXEC the Stored Proc ?

Any help much appreciated with this one !

V.
# 2  
Old 08-05-2003
You dont need the back quotes and Yes you can use sqlplus to execute the stored procedure. Use a "here" document to execute your statement. You can embed both SQL and PL/SQL in the block. Here is a <<Link>> to a thread that may be of some benefit to you.

the '-s' option puts sqlplus in silent mode

sqlplus -s $USER_ID/$USER_PWD << SQLSTRING

#call package here
#or
#write your SQL and place the block
#inside here. Anything you put in here must
#be valid SQL syntax. Also, for your benefit,
#if you put PL/SQL in here, make sure that
#your DECLARE and END; are not indented
#or spaced in any way. They must be on
#the left margin.


SQLSTRING

Last edited by google; 08-06-2003 at 06:46 AM..
# 3  
Old 08-05-2003
you could EXEC the Stored Proc like this
sqlplus user/userpwd < /yourhomedirectory/your.sql
# 4  
Old 08-06-2003
Further, you also do the following to execute your standard SQL statement.

sqlplus user/userpwd @/yourhomedirectory/your.sql
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

KSH to execute stored procs/ctrl files

I am needing to call sqlplus and sqlldr in the same korn shell script. Below are the steps that I need to accomplish. I am new to Oracle shell scripting so an example would be great. CBOB_LOAD.KSH - script name Calls Oracle stored procedure sp_CBOB_CLEAR_LN_STAGING (SERVER: TESTSVR100) Calls... (0 Replies)
Discussion started by: acewilli
0 Replies

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

3. Shell Programming and Scripting

how to pass the values to unix shell from the oracle stored procedure.

Hi i am calling a stored procedure from unix shell like this call test_proc('0002','20100218'); the stored procedure was giving output like this dbms_output.put_line(' processed earlier'); i want to see the output in the unix shell where i called. Thanks barani (6 Replies)
Discussion started by: barani75
6 Replies

4. Shell Programming and Scripting

Executing Shell Script from Within a Sybase Stored Proc

Greetings, I need to make an open server call to a shell script from inside a Sybase Stored procedure. Coul any one please provide a sample code? TIA (0 Replies)
Discussion started by: rajpreetsidhu
0 Replies

5. Shell Programming and Scripting

Korn Shell and Oracle

Hi Guys, I haven't worked on oracle much but I have a situation where I have to do bdf in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell. SERVER_ID ... (9 Replies)
Discussion started by: pareshan
9 Replies

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

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

8. Shell Programming and Scripting

Shell arrays in oracle stored procedure

Is it possible to pass unix shell arrays in Oracle stored procedure? Is yes, how? Thanks (6 Replies)
Discussion started by: superprogrammer
6 Replies

9. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies

10. UNIX for Advanced & Expert Users

Oracle To Korn Shell Array

I'm attempting to populate an array in ksh using the following command: set -A $(SELECT_INVOICE | sed '/^$/d') SELECT_INVOICE is a function that executes the SQL query. Problem: Some of the invoice numbers have alpha characters with spaces(example: OVEN MICRO). The Korn shell is treating... (1 Reply)
Discussion started by: kdst
1 Replies
Login or Register to Ask a Question

Featured Tech Videos