calling a stored procedure from a unix script

[seemagoyal]

Hi,

I don't have any experiance of UNIX and oracle.
but i have got a short assignment for writing a unix script that will call a stored procedure. this script has to run this procedure after every two mintes.

I have a few questions before actually start writing the script:

1. Do i have to have unix and oracle on same server.
2. what libraries i need to in unix, if any

I tried sqlplus user/password@databa sename which i got in this forum only but it's saying ksh: sqlplus: not found

please help me out. Thanks in advance.

[debasisdas]

Please find the details here.

[amitpatel66]

Hi,

I don't have any experiance of UNIX and oracle.
but i have got a short assignment for writing a unix script that will call a stored procedure. this script has to run this procedure after every two mintes.

I have a few questions before actually start writing the script:

1. Do i have to have unix and oracle on same server.
2. what libraries i need to in unix, if any

I tried sqlplus user/password@databa sename which i got in this forum only but it's saying ksh: sqlplus: not found

please help me out. Thanks in advance.

Set the environment before calling sqlplus command.
Environment can be set using ". newfin" command.

Eg:

$ . newfin <instance name>
$ sqlplus user/pwd@dbname

[seemagoyal]

Hi Amit,

Thanks for your response.

Can you please answer the two doubts which i have asked?

[amitpatel66]

Hi Amit,

Thanks for your response.

Can you please answer the two doubts which i have asked?

Seema,

Well I dont understand your question clearly?
What you mean by having UNIX and oracle in same server?

Basically its a Unix Box (unix OS) in which Oracle will be installed.

[seemagoyal]

Seema,

Well I dont understand your question clearly?
What you mean by having UNIX and oracle in same server?

Basically its a Unix Box (unix OS) in which Oracle will be installed.

well - In my case, I have a unix box and then the database is lying on some other database server (different phisical machine)
so can't we connect to that database from this box like we connect to any remote database through java like using jdbc driver

[amitpatel66]

well - In my case, I have a unix box and then the database is lying on some other database server (different phisical machine)
so can't we connect to that database from this box like we connect to any remote database through java like using jdbc driver

Yes you can connect to the database located in another machine.
Get the server name,port and other connection related details.
Specify the TNS ENTRY and you can connect to that database by using tool like PUTTY or any other.

[seemagoyal]

Yes you can connect to the database located in another machine.
Get the server name,port and other connection related details.
Specify the TNS ENTRY and you can connect to that database by using tool like PUTTY or any other.

Ok, yeah i got a Unix server now where oracle is also installed.
Now i searched for some sample script on net and got the one below for me:

#!/bin/sh
sqlplus UID/PWD@Hostname
exec my_proc
whenever SQLError exit faliure

but it doesn't work. do you see any issues with this.

[amitpatel66]

Ok, yeah i got a Unix server now where oracle is also installed.
Now i searched for some sample script on net and got the one below for me:

#!/bin/sh
sqlplus UID/PWD@Hostname
exec my_proc
whenever SQLError exit faliure

but it doesn't work. do you see any issues with this.

Do you have the HOSTNAME specified in your TNS Entry file?
Do you have the Proper UID and PWD?

Can you POST the exact error message that you are facing?

[seemagoyal]

Do you have the HOSTNAME specified in your TNS Entry file?
Do you have the Proper UID and PWD?

Can you POST the exact error message that you are facing?

hi,
Thanks for all your responses.

Ok - it's working now - the user id - i was given by the DBA was not having excecute rights for the stored procedures.

need your help on - how do i know - that my stored procedure has run successfully or it has been failed.
there is some oncept of return codes - right? please help me on that.

[amitpatel66]

hi,
Thanks for all your responses.

Ok - it's working now - the user id - i was given by the DBA was not having excecute rights for the stored procedures.

need your help on - how do i know - that my stored procedure has run successfully or it has been failed.
there is some oncept of return codes - right? please help me on that.

Yes, you can either have a status flag as OUT parameter and return 0 or 1 based on execution of your procedure.

If u r performing any DML operations, then you can COMMIT within a procedure and do a manual check if the DML operation was carried out successfully on the tables!!

[seemagoyal]

Yes, you can either have a status flag as OUT parameter and return 0 or 1 based on execution of your procedure.

If u r performing any DML operations, then you can COMMIT within a procedure and do a manual check if the DML operation was carried out successfully on the tables!!

Thanks Amit
for such a quick response.

the thing is that i am very new to Unix - this is my first assignment.
I was searching for a sample script on net - to capture return code and also if i can get the exact return code so that i can log that in a log file with the error message and error code.

like this time i am getting the following error:

DBD::Oracle::st execute failed: ORA-01403: no data found

also could you please tell what is the code for successfull execution.

[amitpatel66]

Thanks Amit
for such a quick response.

the thing is that i am very new to Unix - this is my first assignment.
I was searching for a sample script on net - to capture return code and also if i can get the exact return code so that i can log that in a log file with the error message and error code.

like this time i am getting the following error:

DBD::Oracle::st execute failed: ORA-01403: no data found

also could you please tell what is the code for successfull execution.

In case if you want tot capture the Error message and Error code, you can make use of SQLCODE and SQLERRM keywords.

SQLCODE - gives you error code
SQLERRM - gives you error message.

Include the EXCEPTION block in your procedure, and check for any exception, if any exception occurs, insert the data in toe err_log table.

Eg:

[code=oracle]
CREATE OR REPLACE PROCEDURE xyz IS
BEGIN
<your code goes here>;
EXCEPTION
WHEN NO DATA FOUND THEN
INSERT INTO err_log VALUES(SQLCODE, SQLERRM);
END;
[/code]

In case of successful execution of procedure, SQL CODE will be 0
and SQLERRM will be "ORA-0000: normal, successful completion"

[seemagoyal]

In case if you want tot capture the Error message and Error code, you can make use of SQLCODE and SQLERRM keywords.

SQLCODE - gives you error code
SQLERRM - gives you error message.

Include the EXCEPTION block in your procedure, and check for any exception, if any exception occurs, insert the data in toe err_log table.

Eg:

[code=oracle]
CREATE OR REPLACE PROCEDURE xyz IS
BEGIN
<your code goes here>;
EXCEPTION
WHEN NO DATA FOUND THEN
INSERT INTO err_log VALUES(SQLCODE, SQLERRM);
END;
[/code]

In case of successful execution of procedure, SQL CODE will be 0
and SQLERRM will be "ORA-0000: normal, successful completion"

I cann't touch the stored procedure. I was looking for some solution in unix itself.

If i can get the error code and message in shell script. Like now when i run my shell script - at unix prompt i am getting that error message. If i can capture that message in script itself and then i can write that error message into a log file after attaching some more information to it.

I hope you are getting me.

[amitpatel66]

I cann't touch the stored procedure. I was looking for some solution in unix itself.

If i can get the error code and message in shell script. Like now when i run my shell script - at unix prompt i am getting that error message. If i can capture that message in script itself and then i can write that error message into a log file after attaching some more information to it.

I hope you are getting me.

Check out the below code would help:

[code=unix]
sqlplus -s << END_OF_SQL
UID/pwd
VAR v_out_err_msg NUMBER
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SERVEROUTPUT ON SIZE 100000
EXECUTE <your procedure name>(:v_out_er r_msg);
EXIT :v_out_err_msg;
END_OF_SQL

STATUS=$?

if [ $STATUS -ne 0 ]
then
echo "Extract encountered errors during the process" >>$LOG_FILE
exit $FAILURE
else
echo "The extract file has been generated."
echo "\nExtract completed successfully.\n "
fi
[/code]


You need to add one parameter atleast to check the execution status of your procedure as I have in the above code (ie v_out_err_msg)