Home » SQL & PL/SQL » SQL & PL/SQL » Calling parametarized procedure in unix shell scripts
Calling parametarized procedure in unix shell scripts [message #256756] Mon, 06 August 2007 09:20 Go to next message
rajini25
Messages: 2
Registered: August 2007
Location: Bangalore
Junior Member
I have a plsql procedure with in and array of out parameter as :-

PACKAGE SET_EMP1
IS

TYPE varchar_array IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

PROCEDURE GET_FILE_NAME (
p_ss_sourec_id IN VARCHAR2,
p_file_name OUT varchar_array
);
END;

PACKAGE BODY SET_EMP1
IS
PROCEDURE GET_FILE_NAME (
p_ss_sourec_id IN VARCHAR2,
p_file_name OUT varchar_array
)
IS

BEGIN

p_file_name(1) := 'xyz';
p_file_name(2) := 'xyz1';

END GET_FILE_NAME;

END;

i am calling this package in unix script

the unix script is:
r=PB_WDM_ROLLOUT_PLAN
echo "`sqlplus -s jrajinikr/rajini25@ncadd << EOT
set serveroutput on
set pagesize 0 head off echo off feedback off linesize 100
spool temp
var b varchar2(100);
exec SET_EMP1.GET_FILE_NAME('$r',:b);
print b c;
spool off
EOT`"

i am getting some error.

now i have to capture the out parameter of the procedure in unix to a variable how to do it?
Re: Calling parametarized procedure in unix shell scripts [message #256759 is a reply to message #256756] Mon, 06 August 2007 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>i am getting some error.
Error? What error? I don't see any error.
Therefore you don't have any problem.
Re: Calling parametarized procedure in unix shell scripts [message #256822 is a reply to message #256759] Mon, 06 August 2007 15:40 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you need to print the contents of your array and print using dbms_output.put_line, then parse that output in the shell and assign to environment variables.

Personally I'd just add a dbms_output.put_line in your stored proc itself and ignore the returned array (assuming the proc isn't used elsewhere)...
Previous Topic: Insert into table
Next Topic: Outer Join is not allowed in operand OR or IN
Goto Forum:
  


Current Time: Mon Dec 05 08:45:38 CST 2016

Total time taken to generate the page: 0.08339 seconds