| Returning an Array from a Function [message #137162] |
Tue, 13 September 2005 08:53  |
sebastianR
Messages: 33
Registered: August 2005
|
Member |
|
|
Hi,
i've searched quite a bit, but did not find an easy explaination how to return an array from a function?
I've defined my Function in a package like this:
...
TYPE ERROR_ARR IS VARRAY(66) OF PLS_INTEGER;
FUNCTION checkRules(a IN t_a) RETURN ERROR_ARR;
The Function Body looks like this:
FUNCTION checkRules(a IN t_a)
RETURN ERROR_ARR
IS
l_ERROR_NUM ERROR_NUM := 0;
l_ERROR_ARR ERROR_ARR := ERROR_ARR();
BEGIN
...
for i in 1..66 loop
l_ERROR_ARR(i) := 0;
end loop;
RETURN l_ERROR_ARR;
END checkRules;
Okay, that compiles without errors or warnings, but I have troubles executing this function from outside.
I'm trying like this:
DECLARE
TYPE ERROR_ARR IS VARRAY(66) OF PLS_INTEGER;
l_ERROR_ARR ERROR_ARR;
BEGIN
l_ERROR_ARR := <PACKAGE_NAME>#.checkRules(170377);
END;
Which gives me the following error:
ORA-06550: Zeile 7, Spalte 18:
PLS-00382: Dieser Ausdruck hat den falschen Typ
ORA-06550: Zeile 7, Spalte 3:
PL/SQL: Statement ignored
ORA-06550: Row 7, Column 18:
PLS-00382: This expression has an invalid type
ORA-06550: Row 7, Column 3:
PL/SQL: Statement ignored
I think the mistake is at the declaration outside before calling the function.
The returned value does not seem to fit the declared type outside. What am I doing wrong?
Thank you for your help,
SebastianR
|
|
|
|
| Re: Returning an Array from a Function [message #137179 is a reply to message #137162] |
Tue, 13 September 2005 10:54  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
I think you need to make your type a true SQL type, instead of only having it visible inside PL/SQL (i.e., your package).
Given that decision, I had to make your type a VARRAY of INTEGERs instead of PLS_INTEGERs, as PLS_INTEGER is only recognized by PL/SQL, and not by pure SQL.
I also removed the TYPE declarations from both the package specification and the anonymous block.
Finally, I had to add an EXTEND collection method to the function to prevent ORA-06533: Subscript beyond count errors.SQL> CREATE OR REPLACE TYPE error_arr AS VARRAY(66) OF INTEGER
2 /
Type created.
SQL> CREATE OR REPLACE PACKAGE pkg
2 AS
3 FUNCTION checkrules(a IN NUMBER)
4 RETURN error_arr;
5 END pkg;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 AS
3 FUNCTION checkrules(a IN NUMBER)
4 RETURN error_arr
5 IS
6 l_error_arr error_arr := error_arr();
7 BEGIN
8 FOR i IN 1..66
9 LOOP
10 l_error_arr.EXTEND;
11 l_error_arr(i) := 0;
12 END LOOP;
13 RETURN (l_error_arr);
14 END checkrules;
15 END pkg;
16 /
Package body created.
SQL> DECLARE
2 l_error_arr error_arr;
3 BEGIN
4 l_error_arr := pkg.checkrules(170377);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> |
|
|
|
| Re: Returning an Array from a Function [message #137193 is a reply to message #137162] |
Tue, 13 September 2005 13:10 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The variable in your anonymous block needs to be based on the actual type declaration in the package. Even though the type in your anonymous block is the same as in the package, the variable needs to be based on the type in the package:
sql>create or replace package test
2 is
3
4 TYPE ERROR_ARR IS VARRAY(66) OF PLS_INTEGER;
5
6 FUNCTION checkRules RETURN ERROR_ARR;
7
8 end;
9 /
Package created.
sql>create or replace package body test
2 is
3
4 FUNCTION checkRules
5 RETURN ERROR_ARR
6 IS
7 l_ERROR_ARR ERROR_ARR := ERROR_ARR();
8 BEGIN
9 for i in 1..66 loop
10 l_error_arr.extend;
11 l_ERROR_ARR(i) := 0;
12 end loop;
13 RETURN l_ERROR_ARR;
14 END checkRules;
15
16 end;
17 /
Package body created.
sql>DECLARE
2 TYPE ERROR_ARR IS VARRAY(66) OF PLS_INTEGER;
3 l_ERROR_ARR ERROR_ARR;
4 BEGIN
5 l_ERROR_ARR := test.checkRules;
6 END;
7 /
l_ERROR_ARR := test.checkRules;
*
ERROR at line 5:
ORA-06550: line 5, column 25:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored
sql>DECLARE
2 l_ERROR_ARR test.ERROR_ARR;
3 BEGIN
4 l_ERROR_ARR := test.checkRules;
5 END;
6 /
PL/SQL procedure successfully completed. |
|
|
|
| Re: Returning an Array from a Procedure [message #394210 is a reply to message #137193] |
Thu, 26 March 2009 02:43 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I have to return an array from a procedure. I took help of the above code but I am getting error.
Please see the part of code below :-
CREATE OR REPLACE PACKAGE pks_ams_upload AS
TYPE ERROR_ARR IS VARRAY(66) OF VARCHAR2(100);
TYPE c1 IS REF CURSOR;
PROCEDURE sp_check_P1 (
p_file_id IN VARCHAR2,
p_flag IN VARCHAR2,
p_session_id IN VARCHAR2,
ERROR_ARR OUT VARCHAR2);
end pks_ams_upload;
/
CREATE OR REPLACE PACKAGE BODY AMSUAT.pks_ams_upload
IS
/*Procedure sp_check_P1 to validate the first screen */
PROCEDURE sp_check_p1 (
p_file_id IN VARCHAR2,
p_flag IN VARCHAR2,
p_session_id IN VARCHAR2,
ERROR_ARR OUT VARCHAR2
)
IS
l_ERROR_ARR ERROR_ARR := ERROR_ARR();
c3 c1;
c2 tmp_ams_ia_profil_parking%ROWTYPE;
v_query VARCHAR2 (500);
v_error_index NUMBER := 0;
app_null EXCEPTION; --user defined exception
BEGIN
v_query :=
'SELECT * FROM TMP_AMS_IA_PROFIL_PARKING WHERE FILE_ID = '
|| p_file_id
|| ' AND session_id ='
|| p_session_id;
OPEN c3 FOR v_query;
LOOP
BEGIN
FETCH c3
INTO c2;
EXIT WHEN c3%NOTFOUND;
IF TRIM(c2.appl_no) IS NULL THEN
RAISE app_null;
END IF;
EXCEPTION
WHEN app_null THEN
l_error_arr.extend;
l_ERROR_ARR(v_error_index) := 'Application No cannot be NULL';
v_error_index := v_error_index + 1;
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
raise_application_error (-20001,
'An error was encountered - '
|| SQLCODE
|| ' -ERROR-'
|| SQLERRM
);
END;
END LOOP;
END sp_check_p1;
END pks_ams_upload;
The above code is only part of a package to give an understanding of what I am trying to do for returning the array ERROR_ARR which consist of error messages.
The error I am getting is on line 12 where the statement is l_ERROR_ARR ERROR_ARR := ERROR_ARR();
12/19 PLS-00488: invalid variable declaration: object 'ERROR_ARR' must be a type or subtype
12/19 PL/SQL: Item ignored
208/13 PLS-00320: the declaration of the type of this expression is incomplete or malformed
I am new to returning array from a procedure. The type is defined in the package.
Please help me this as where I am going wrong.
Regards,
Mahi
[Updated on: Thu, 26 March 2009 02:45] Report message to a moderator |
|
|
|
|
|
| Re: Returning an Array from a Procedure [message #394215 is a reply to message #394213] |
Thu, 26 March 2009 03:30 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi Bonker,
Thanks for your help. It compiled.
Now I am trying to test the procedure to return the array but again I am stuck...
As per the declaration of the Procedure, I need to send the OUT parameter as well while calling sp_check_P1 but I am not sure how to do this.
PROCEDURE sp_check_P1 (
p_file_id IN VARCHAR2,
p_flag IN VARCHAR2,
p_session_id IN VARCHAR2,
ERROR_ARR OUT VARCHAR2);
I am trying to call the procedure sending only 3 arguements...not sure what to do for fourth one since an OUT parameter.
DECLARE
TYPE ERROR_ARR IS VARRAY(66) OF VARCHAR2(100);
l_ERROR_ARR ERROR_ARR := ERROR_ARR();
BEGIN
pks_ams_upload.sp_check_p1(1,'P1','12',l_ERROR_ARR);
END;
/
ORA-06550: line 6, column 10:
PLS-00306: wrong number or types of arguments in call to 'SP_CHECK_P1'
ORA-06550: line 6, column 10:
PL/SQL: Statement ignored
[/code]
Please help me on this....
Regards,
Mahi
[Updated on: Thu, 26 March 2009 03:43] Report message to a moderator |
|
|
|
| Re: Returning an Array from a Procedure [message #394217 is a reply to message #394215] |
Thu, 26 March 2009 03:44 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
When you call the procedure, you need to pass in a variable declared as the same type as the one in the package.
Just because both of them are VARRAY(66) of Pls_integer does not make them the same type - they actually need to both reference a type rom the same place..
Try changing your calling code to:DECLARE
l_error_arr pkg_ams_upload.error_arr;
BEGIN
pks_ams_upload.sp_check_p1(1,'P1','12',l_error_arr);
END;
/ |
|
|
|
| Re: Returning an Array from a Procedure [message #394222 is a reply to message #394217] |
Thu, 26 March 2009 03:52 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi JRowbottom,
I tried running the changed code :-
DECLARE
l_error_arr pks_ams_upload.error_arr;
BEGIN
pks_ams_upload.sp_check_p1(1,'P1','12',l_error_arr);
END;
But again got the error :-
ORA-06550: line 4, column 10:
PLS-00306: wrong number or types of arguments in call to 'SP_CHECK_P1'
ORA-06550: line 4, column 10:
PL/SQL: Statement ignored
Is it because I have given ERROR_ARR OUT VARCHAR2 in the procedure. But it has compiled correctly.
PROCEDURE sp_check_P1 (
p_file_id IN VARCHAR2,
p_flag IN VARCHAR2,
p_session_id IN VARCHAR2,
ERROR_ARR OUT VARCHAR2);
|
|
|
|
|
|
| Re: Returning an Array from a Procedure [message #394225 is a reply to message #394222] |
Thu, 26 March 2009 04:00 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That is the cause.
If you look at your procedure code, I don't think that you actually set the ERROR_ARR output variable anywhere.
If I were you, I'd rename the ERROR_ARR output variable to P_ERROR_ARR to distinguish it from any local variables, change the type to Error_Arr and make sure that you set p_error_arr to l_error_arr at the end of the code in the procedure.
|
|
|
|
| Re: Returning an Array from a Procedure [message #394226 is a reply to message #394225] |
Thu, 26 March 2009 04:05 |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Thanks to both of you to make me understand and work on OUT parameter as ARRAY.
I was able to do it.
I changed the varchar2 to array type.
PROCEDURE sp_check_p1 (
p_file_id IN VARCHAR2,
p_flag IN VARCHAR2,
p_session_id IN VARCHAR2,
p_error_arr OUT pks_ams_upload.error_arr
)
IS
Thanks a lot for your help.
Mahi
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
