Home » SQL & PL/SQL » SQL & PL/SQL » Returning an Array from a Function
Returning an Array from a Function [message #137162] Tue, 13 September 2005 08:53 Go to next message
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 Go to previous messageGo to next message
Art Metzer
Messages: 2478
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #394213 is a reply to message #394210] Thu, 26 March 2009 02:59 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Does this change work for you? Kindly note I have not tested it.


l_ERROR_ARR pks_ams_upload.ERROR_ARR := ERROR_ARR();


[Updated on: Thu, 26 March 2009 03:02]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #394223 is a reply to message #394217] Thu, 26 March 2009 03:52 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Also in your code sp_check_p1 the last out ERROR_ARR parameter is of Varchar2 type, you need to change it to array type.

Re: Returning an Array from a Procedure [message #394225 is a reply to message #394222] Thu, 26 March 2009 04:00 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: How to track/find which transaction is consuming much more time?
Next Topic: How to track which transaction is taking, much time in oracle 9i
Goto Forum:
  


Current Time: Sun Dec 11 02:29:21 CST 2016

Total time taken to generate the page: 0.08295 seconds