By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,088 Members | 1,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,088 IT Pros & Developers. It's quick & easy.

calling a stored procedure from a unix script

P: 13
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.
Oct 12 '07 #1
Share this Question
Share on Google+
26 Replies


debasisdas
Expert 5K+
P: 8,127
Please find the details here.
Oct 12 '07 #2

amitpatel66
Expert 100+
P: 2,367
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
Oct 12 '07 #3

P: 13
Hi Amit,

Thanks for your response.

Can you please answer the two doubts which i have asked?
Oct 12 '07 #4

amitpatel66
Expert 100+
P: 2,367
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.
Oct 12 '07 #5

P: 13
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
Oct 12 '07 #6

amitpatel66
Expert 100+
P: 2,367
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.
Oct 12 '07 #7

P: 13
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.
Oct 24 '07 #8

amitpatel66
Expert 100+
P: 2,367
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?
Oct 25 '07 #9

P: 13
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.
Oct 29 '07 #10

amitpatel66
Expert 100+
P: 2,367
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!!
Oct 29 '07 #11

P: 13
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.
Oct 29 '07 #12

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE xyz IS
  2. BEGIN
  3. <your code goes here>;
  4. EXCEPTION
  5. WHEN NO DATA FOUND THEN
  6. INSERT INTO err_log VALUES(SQLCODE,SQLERRM);
  7. END;
  8.  
In case of successful execution of procedure, SQL CODE will be 0
and SQLERRM will be "ORA-0000: normal, successful completion"
Oct 29 '07 #13

P: 13
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE xyz IS
  2. BEGIN
  3. <your code goes here>;
  4. EXCEPTION
  5. WHEN NO DATA FOUND THEN
  6. INSERT INTO err_log VALUES(SQLCODE,SQLERRM);
  7. END;
  8.  
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.
Oct 30 '07 #14

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1. sqlplus -s << END_OF_SQL
  2. UID/pwd
  3. VAR v_out_err_msg   NUMBER
  4. WHENEVER SQLERROR EXIT SQL.SQLCODE
  5. WHENEVER OSERROR  EXIT FAILURE
  6. SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SERVEROUTPUT ON SIZE 100000
  7.    EXECUTE <your procedure name>(:v_out_err_msg);
  8. EXIT :v_out_err_msg;
  9. END_OF_SQL
  10.  
  11. STATUS=$?
  12.  
  13. if [ $STATUS -ne 0 ]
  14. then
  15.    echo "Extract encountered errors during the process"   >>$LOG_FILE
  16.    exit $FAILURE
  17. else
  18.    echo "The extract file has been generated." 
  19.    echo "\nExtract completed successfully.\n" 
  20. fi
  21.  

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)
Oct 30 '07 #15

P: 13
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;
}
Oct 31 '07 #16

amitpatel66
Expert 100+
P: 2,367
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
Nov 1 '07 #17

P: 13
Oh - is it?

Thanku so much Amit.
You have been a great help to me :-)
Nov 1 '07 #18

amitpatel66
Expert 100+
P: 2,367
Oh - is it?

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

Amit
Nov 1 '07 #19

P: 13
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
Nov 6 '07 #20

amitpatel66
Expert 100+
P: 2,367
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?
Nov 6 '07 #21

P: 13
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
Nov 6 '07 #22

amitpatel66
Expert 100+
P: 2,367
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!!
Nov 6 '07 #23

P: 13
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
Nov 15 '07 #24

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. #enable output
  3.   $dbh->func( 1000000, 'dbms_output_enable' );
  4. #execute proc $sp
  5.   my $sth=$dbh->prepare($sp);
  6.   $sth->execute();
  7. #retrieve output
  8.   my @text = $dbh->func( 'dbms_output_get' );
  9.  
  10.  
and @text contains what would have been printed in your SQLPLUS session with "SET SERVEROUTPUT ON"
Nov 15 '07 #25

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. #enable output
  3.   $dbh->func( 1000000, 'dbms_output_enable' );
  4. #execute proc $sp
  5.   my $sth=$dbh->prepare($sp);
  6.   $sth->execute();
  7. #retrieve output
  8.   my @text = $dbh->func( 'dbms_output_get' );
  9.  
  10.  
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 :-)
Nov 15 '07 #26

amitpatel66
Expert 100+
P: 2,367
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
Nov 16 '07 #27

Post your reply

Sign in to post your reply or Sign up for a free account.