CODEWITHSUNDEEP
oracle-databasestored-proceduressqlplus
Raffaele
Raffaele

Reputation: 23

Call stored procedure with Shell Script

Can you help me? How to connect to database Oracle from shell script using sqlplus? Can you do an example of shell script that call a stored procedure??

Thank you.

Upvotes: 1

Views: 42424

Answers (2)

Harshil
Harshil

Reputation: 1300

Here's an example:

create an sh file "callProcedureFromBash.sh"

#!/bin/bash      this is a comment

#if you can access sqlplus already, you don't need following 2 lines
export ORACLE_HOME = /efs/dist/oracledb/client/10G/exec
export PATH=$PATH:$ORACLE_HOME/bin

sqlplus "userid/password@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = WWW.YOURHOST.COM) (PORT = 12345) (CONNECT_DATA = (SID = HXZ524))))" <<END

DECLARE
    a int;
BEGIN
    packagename.Procedurename(a);
END;
/
commit;

Upvotes: 2

ProfessionalAmateur
ProfessionalAmateur

Reputation: 4563

Create a xxxx.sql file and execute it like this:

set serveroutput on;
execute STORED_PROCEDURE;
set serveroutput off;
exit

You will need a way to execute your script, I use CRON on *nix based systems. My script looks something like this 

#!/bin/sh
# This short shell script calls the XXXX Stored Procedure
# drive.
PATH=$PATH:/opt/oracle/local/bin:/usr/local/bin
export ORACLE_SID=XXXX;   
export ORAENV_ASK=NO;      
export TWO_TASK=XXXXXXXXX;    
. /usr/local/bin/oraenv
sqlplus USERID/PASSWPRD @SQL_Script_to_call_stored_Proceudre.sql
exit

Upvotes: 3

Related Questions