Need to run Oracle stored procedure from UNIX env | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Need to run Oracle stored procedure from UNIX env

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 01-04-2013
prasson_ibm prasson_ibm is offline
Registered User
 
Join Date: Jul 2009
Last Activity: 15 August 2014, 5:46 AM EDT
Location: bangalore
Posts: 30
Thanks: 1
Thanked 0 Times in 0 Posts
Need to run Oracle stored procedure from UNIX env

Hi Everyone,
I want to create a script where i need to run the oracle stored procedure from unix script and get the output(sequence number ) into a variable which i will pass in my datastage job.

Below is my stored procedure:-


Code:
DECLARE 
  P_TRANSTYPE VARCHAR2(20);
  P_LOCATIONCODE VARCHAR2(10);
  P_SEQNO NUMBER;
  P_ERRORMSG VARCHAR2(32767);
 
BEGIN 
  P_TRANSTYPE := 'RBADDR';
  P_LOCATIONCODE := 'ODWH';
  P_SEQNO := NULL;
  P_ERRORMSG := NULL;
 
  AITBROKER.USP_GET_NEXT_SEQNO ( P_TRANSTYPE, P_LOCATIONCODE, P_SEQNO, P_ERRORMSG ); 
  DBMS_OUTPUT.PUT_LINE('SeqNo: ' || to_char(P_SEQNO));
END;

Can anyone help me to write a shell script for that, I have no exprience in calling stored procedure from unix.


Thanks
Prasoon

Last edited by Scrutinizer; 01-04-2013 at 03:27 AM.. Reason: code tags
Sponsored Links
    #2  
Old 01-04-2013
itkamaraj's Avatar
itkamaraj itkamaraj is offline Forum Advisor  
^Kamaraj^
 
Join Date: Apr 2010
Last Activity: 8 August 2014, 4:56 AM EDT
Posts: 3,057
Thanks: 33
Thanked 658 Times in 637 Posts
http://www.unix.com/shell-programmin...ll-script.html
Sponsored Links
    #3  
Old 01-04-2013
prasson_ibm prasson_ibm is offline
Registered User
 
Join Date: Jul 2009
Last Activity: 15 August 2014, 5:46 AM EDT
Location: bangalore
Posts: 30
Thanks: 1
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by itkamaraj View Post
Hi,

Based on example given on this trade i am able to write below script:-

Code:
#!/bin/sh
sqlplus -s aitbroker/a1t!bRoker879@aitdev<<END
var P_SEQNO NUMBER;
var P_ERRORMSG VARCHAR2(2000);
EXEC AITBROKER.USP_GET_NEXT_SEQNO('RBADDR','ODWH',:P_SEQNO,:P_ERRORMSG);
print P_SEQNO;
print P_ERRORMSG;
commit;
exit;
END

But here i want to store P_SEQNO & P_ERRORMSG into variable and then export it,can anyone help me to modify the script.

Thanks
Prasoon

Last edited by radoulov; 01-04-2013 at 06:22 AM.. Reason: Code tags.
    #4  
Old 01-04-2013
posix's Avatar
posix posix is offline
Registered User
 
Join Date: Feb 2010
Last Activity: 8 September 2014, 8:25 AM EDT
Location: grep "Bhubaneswar" "india"
Posts: 191
Thanks: 7
Thanked 13 Times in 13 Posts
Create a function for the sql activity say sql_function.
I hope using the array you can capture the output.

Code:
 
set -A var_array $(sql_function)

Traverse the array you will get the output.
Sponsored Links
    #5  
Old 01-04-2013
prasson_ibm prasson_ibm is offline
Registered User
 
Join Date: Jul 2009
Last Activity: 15 August 2014, 5:46 AM EDT
Location: bangalore
Posts: 30
Thanks: 1
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by posix View Post
Create a function for the sql activity say sql_function.
I hope using the array you can capture the output.

Code:
 
set -A var_array $(sql_function)

Traverse the array you will get the output.
Hi Posix,

Thanks for your prompt reply!

Can you please modify my script and add this code you have given because i am new in calling stored procedure from unix.

Thanks
Prasoon
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Call and redirect output of Oracle stored procedure from unix script jakSun8 Shell Programming and Scripting 0 03-24-2010 05:21 PM
how to call oracle stored procedure from unix shell barani75 Shell Programming and Scripting 6 02-26-2010 02:51 PM
how to pass the values to unix shell from the oracle stored procedure. barani75 Shell Programming and Scripting 6 02-24-2010 10:38 AM
Invoking Oracle stored procedure in unix shell script hidnana Shell Programming and Scripting 1 10-15-2008 07:37 AM
Calling an Oracle Stored Procedure from Unix shell script Leojhose Shell Programming and Scripting 2 08-06-2007 06:00 AM



All times are GMT -4. The time now is 09:36 PM.