Plsql return associative array function

Question

I have a question about return an array on plsql function. I already found a lot of information about using arrays

But I am struggling on a function which should return an array.

Source examples: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99931

As I understand from googling around, a way to return an array is first creating a table array apart and then use this type inside the function.

What I actually trying to accomplish is to return an array which is created inside the function.

Not sure if I am clear but here I have me code:

CREATE OR REPLACE FUNCTION createChecksumArray( checksumNumber VARCHAR2 )
RETURN textGroupArray 
AS
  ..    
  TYPE textGroupArray  IS TABLE OF VARCHAR2(30)
  INDEX BY             BINARY_INTEGER;
  textGroups           textGroupArray;
  ..
BEGIN
  ..
  textGroups := textGroupArray();
  ..
  (A LOOP)
    -- add textgroup into the array
    IF textGroup != 0 THEN
      arrayCount               := arrayCount + 1;
      textGroups( arrayCount ) := TRIM( textGroup );
    END IF;
  (END THE LOOP)
  ..
  RETURN textGroups;

I firstly made the code outside a function, for testing if the array was working. The result from the test by outputting the array:

spaced text: 3536 2029 2712 3456 789

I couldn't manage to return the array declared inside the function tho. Not sure if this is possible, so the question is: Is this possible and if so, how? Wat am I doing here wrong.

I couldn't found a question posted on Stackoverflow containing this specific subject. But sorry if my searching wasn't that good.

Also, if you don't like my posting, please add an edit so I can learn from you.

Full code/function

Dmitriy · Accepted Answer · 2015-11-21 19:42:16Z

2

You need to create type first, and then use if in function declaration. Create a package and declare type there.

create or replace package my_pkg as

  type textGroupArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  FUNCTION createChecksumArray(checksumNumber VARCHAR2) RETURN textGroupArray;

end my_pkg;
/

create or replace package body my_pkg as
    FUNCTION createChecksumArray( checksumNumber VARCHAR2 )
    RETURN textGroupArray 
    is
      ..    
      TYPE textGroupArray  IS TABLE OF VARCHAR2(30)
      INDEX BY             BINARY_INTEGER;
      textGroups           textGroupArray;
      ..
    BEGIN
      ..
      textGroups := textGroupArray();
      ..
      (A LOOP)
        -- add textgroup into the array
        IF textGroup != 0 THEN
          arrayCount               := arrayCount + 1;
          textGroups( arrayCount ) := TRIM( textGroup );
        END IF;
      (END THE LOOP)
      ..
      RETURN textGroups;
    end;

end my_pkg;
/

edited Nov 21, 2015 at 19:42

answered Nov 21, 2015 at 18:06

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jim Vercoelen Over a year ago

Hello Dmitry! Thanks for response. Trying to use your code but I definitely doing something wrong.. pastebin.ca/3265839

Dmitriy Over a year ago

@JimVercoelen Sorry, I had a mistake (now corrected). Correct is create package ... as and function .... is.

Dmitriy Over a year ago

@JimVercoelen Your code have two errors: 1) one of your variables inside function has the same name with function parameter, 2) you don't need line textGroups := textGroupArray();

Jim Vercoelen Over a year ago

Dmitry! the packages are compiling. Big thanks for that. But now I have struggles by calling this package. I need to create an array inside an other function which can be set to the createChecksumArray function.

Jim Vercoelen Over a year ago

If you have some spare time, could you look into this? pastebin.ca/3265974 It is a small function which chould declare a new array and after that set to the function..

|

Collectives™ on Stack Overflow

Plsql return associative array function

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related