Need to run Oracle stored procedure from UNIX env

Shell Programming and Scripting
1

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

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

2
3

Hi,

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

#!/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

4

Create a function for the sql activity say sql_function.
I hope using the array you can capture the output.

 
set -A var_array $(sql_function)

Traverse the array you will get the output.

5

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