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@databasename 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@databasename 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:

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;
 

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:

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;
 

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:

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

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)

[seemagoyal]

ok,

because of client recomendation i have to use perl now.

in perl i am getting a return value of 1 in rc:
i guess 1 is for faliure but it's not giving any error also - any thoughts?

eval { $sql_cmd = "BEGIN\n$stored_proc_mauisecond( p_num_rec => :p_num_rec, p_upper_bound => :p_upper_bound)\;END\;";
$sth = $dbh->prepare( $sql_cmd );
$sth->bind_param(":p_num_rec",50);
$sth->bind_param(":p_upper_bound","10-10-2007");
my $rc=$sth->execute(); print LOGF scalar localtime(), ": Execution of stored procedure with return code: $rc\n";

};
if( $@ ) {
print LOGF scalar localtime(), ": Execution of stored procedure failed: $DBI::errstr\n";
$dbh->rollback;
# exit 0;
}

[amitpatel66]

ok,

because of client recomendation i have to use perl now.

in perl i am getting a return value of 1 in rc:
i guess 1 is for faliure but it's not giving any error also - any thoughts?

eval { $sql_cmd = "BEGIN\n$stored_proc_mauisecond( p_num_rec => :p_num_rec, p_upper_bound => :p_upper_bound)\;END\;";
$sth = $dbh->prepare( $sql_cmd );
$sth->bind_param(":p_num_rec",50);
$sth->bind_param(":p_upper_bound","10-10-2007");
my $rc=$sth->execute(); print LOGF scalar localtime(), ": Execution of stored procedure with return code: $rc\n";

};
if( $@ ) {
print LOGF scalar localtime(), ": Execution of stored procedure failed: $DBI::errstr\n";
$dbh->rollback;
# exit 0;
}

You guess is wrong.
1 is for success and 0 is for failure in perl!!
Your code is working fine and it returns perfect status ie 1 which means success

[seemagoyal]

Oh - is it?

Thanku so much Amit.
You have been a great help to me :-)

[amitpatel66]

Oh - is it?

Thanku so much Amit.
You have been a great help to me :-)

You are Welcome :)

Amit

[seemagoyal]

You are Welcome :)

Amit

Hi Amit,

I have all the database details(user name and password also) in the perl script - hard coded.

what should be the best way to hide this. I guess i should have that in a seperate file and use it in main perl script.

Can you please tell me what is the way in perl to do this

[amitpatel66]

Hi Amit,

I have all the database details(user name and password also) in the perl script - hard coded.

what should be the best way to hide this. I guess i should have that in a seperate file and use it in main perl script.

Can you please tell me what is the way in perl to do this

Yes, In case ig you are going to use the same database login UID,connection string, then you ca nretrive those by storing them in the file.

Is your application going to accept User/Password/Connection string from the users as an input values?

[seemagoyal]

Yes, In case ig you are going to use the same database login UID,connection string, then you ca nretrive those by storing them in the file.

Is your application going to accept User/Password/Connection string from the users as an input values?

yes i was hoping to have the details in a configuration kind of file - like we have properties file in java.
so that when my script goes in production env - i just need to edit user name, pwd and db details in just this config file.

i searched on net - guess there rea a no of ways like 'require' , use .....

but i couldn't write the working code.

can you please gimme an example - what format should be the config file and then how can i extract values based on key

[amitpatel66]

yes i was hoping to have the details in a configuration kind of file - like we have properties file in java.
so that when my script goes in production env - i just need to edit user name, pwd and db details in just this config file.

i searched on net - guess there rea a no of ways like 'require' , use .....

but i couldn't write the working code.

can you please gimme an example - what format should be the config file and then how can i extract values based on key

The parameters file can be of any format might not be a problem. Let it be a txt file with User,password,connection string terminated with ":" (Colon)

Now from perl, open this file in read mode,extract data before every colon (taking a substr in general). Place this configuration parameter file in the same path/directory where your perl file resides.

I hope this helps!!

[seemagoyal]

The parameters file can be of any format might not be a problem. Let it be a txt file with User,password,connection string terminated with ":" (Colon)

Now from perl, open this file in read mode,extract data before every colon (taking a substr in general). Place this configuration parameter file in the same path/directory where your perl file resides.

I hope this helps!!

Hi Amit,

In perl, I am using DBI to call stored procedure. May you tell me a way so that i can write dbms_output.put_line statement to a log file through perl or to the terminal.

like in Unix we can say set serveroutput on, what we can do in perl for that

[amitpatel66]

Hi Amit,

In perl, I am using DBI to call stored procedure. May you tell me a way so that i can write dbms_output.put_line statement to a log file through perl or to the terminal.

like in Unix we can say set serveroutput on, what we can do in perl for that

You can try below code in order to get the functionality of SERVEROUTPUT in perl:

 
#enable output
  $dbh->func( 1000000, 'dbms_output_enable' );
#execute proc $sp
  my $sth=$dbh->prepare($sp);
  $sth->execute();
#retrieve output
  my @text = $dbh->func( 'dbms_output_get' );
 
 

and @text contains what would have been printed in your SQLPLUS session with "SET SERVEROUTPUT ON"

[seemagoyal]

You can try below code in order to get the functionality of SERVEROUTPUT in perl:

 
#enable output
  $dbh->func( 1000000, 'dbms_output_enable' );
#execute proc $sp
  my $sth=$dbh->prepare($sp);
  $sth->execute();
#retrieve output
  my @text = $dbh->func( 'dbms_output_get' );
 
 

and @text contains what would have been printed in your SQLPLUS session with "SET SERVEROUTPUT ON"

oh great
Thanks Amit

I shall let u know - how does that work for me :-)

[amitpatel66]

oh great
Thanks Amit

I shall let u know - how does that work for me :-)

You are welcome :)

We are glad that the solution helped you!!

Regards,
Amit