| passing table type to pl/sql procedure [message #46369] |
Thu, 03 June 2004 04:19  |
kunal
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
Requirement: We need to pass a String array from java to pl/sql, do some processing there and get the new array back to java.
We are using the following code:
-- create a table type
CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (30);
-- package specification
CREATE OR REPLACE PACKAGE TEST_PKG AS
FUNCTION ARRAYIMPL(P_IN IN STRARRAY)
RETURN STRARRAY PIPELINED;
FUNCTION SINGLEIMPL(P_IN IN VARCHAR2)
RETURN VARCHAR2;
END TEST_PKG; -- package spec
-- package body
CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
FUNCTION SINGLEIMPL(P_IN IN VARCHAR2)
RETURN VARCHAR2
IS
P_OUT VARCHAR2(30);
BEGIN
P_OUT := P_IN;
RETURN P_OUT;
END SINGLEIMPL;
FUNCTION ARRAYIMPL(P_IN IN STRARRAY)
RETURN STRARRAY PIPELINED
IS
P_OUT STRARRAY := STRARRAY();
BEGIN
FOR I IN 1 .. P_IN.COUNT
LOOP
P_OUT.EXTEND;
P_OUT (I) := SINGLEIMPL(P_IN(I));
END LOOP;
RETURN;
END ARRAYIMPL;
END TEST_PKG;
-- Test code:
DECLARE
L_IN STRARRAY := STRARRAY();
L_OUT STRARRAY := STRARRAY();
BEGIN
L_IN.EXTEND(2);
L_IN (1) := 'hello';
L_IN (2) := 'world!';
L_OUT := TEST_PKG.ARRAYIMPL (L_IN);
for I in 1 .. L_OUT.count
loop
DBMS_OUTPUT.PUT_LINE
('Value in row ' || TO_CHAR (i) || ': ' || L_OUT (i));
end loop;
END;
-- Exception:
L_IN STRARRAY := STRARRAY();
*
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
Please let us know what is the problem here? Also If there is any other way of solving this problem.
The other thing we tried was to use a procedure inplace of function:
-- Specification:
PROCEDURE ARRAYIMPL(P_IN IN STRARRAY, P_OUT OUT STRARRAY);
-- Body:
PROCEDURE ARRAYIMPL(P_IN IN STRARRAY, P_out out STRARRAY)
IS
BEGIN
FOR I IN 1 .. P_IN.COUNT
LOOP
P_OUT.EXTEND;
P_OUT (I) := SINGLEIMPL(P_IN(I));
END LOOP;
END ARRAYIMPL;
-- Test Code:
DECLARE
L_IN STRARRAY := STRARRAY();
L_OUT STRARRAY := STRARRAY();
BEGIN
L_IN.EXTEND(2);
L_IN (1) := 'hello';
L_IN (2) := 'world!';
TEST_PKG.ARRAYIMPL (L_IN, L_OUT);
for I in 1 .. L_OUT.count
loop
DBMS_OUTPUT.PUT_LINE
('Value in row ' || TO_CHAR (i) || ': ' || L_OUT (i));
end loop;
END;
-- Exception:
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "APPS.TEST_PKG", line 15
ORA-06512: at line 8
Thanks In Advance
|
|
|
|
| Re: passing table type to pl/sql procedure [message #46379 is a reply to message #46369] |
Thu, 03 June 2004 09:25  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You won't be able to pipeline the function here:
FUNCTION ARRAYIMPL(P_IN IN STRARRAY)
RETURN STRARRAY -- no pipeline
IS
P_OUT STRARRAY := STRARRAY();
BEGIN
FOR I IN 1 .. P_IN.COUNT
LOOP
P_OUT.EXTEND;
P_OUT (I) := SINGLEIMPL(P_IN(I));
END LOOP;
RETURN p_out; -- return a value
END ARRAYIMPL;
The p_out parameter needs to be initialized within the procedure instead of in the anonymous block:
PROCEDURE ARRAYIMPL(P_IN IN STRARRAY, P_out out STRARRAY)
IS
BEGIN
p_out := strarray(); -- init here
FOR I IN 1 .. P_IN.COUNT
LOOP
P_OUT.EXTEND;
P_OUT (I) := SINGLEIMPL(P_IN(I));
END LOOP;
END ARRAYIMPL;
And to run it:
sql>declare
2 l_in strarray := strarray();
3 l_out strarray;
4 begin
5 l_in.extend(2);
6 l_in (1) := 'hello';
7 l_in (2) := 'world!';
8 -- func
9 l_out := test_pkg.arrayimpl (l_in);
10 for i in 1 .. l_out.count loop
11 dbms_output.put_line('Value in row ' || to_char (i) || ': ' || l_out (i));
12 end loop;
13 -- proc
14 test_pkg.arrayimpl (l_in, l_out);
15 for i in 1 .. l_out.count loop
16 dbms_output.put_line('Value in row ' || to_char (i) || ': ' || l_out (i));
17 end loop;
18 end;
19 /
Value in row 1: hello
Value in row 2: world!
Value in row 1: hello
Value in row 2: world!
PL/SQL procedure successfully completed. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
