CODEWITHSUNDEEP
arraysplsql
Jim Vercoelen
Jim Vercoelen

Reputation: 1078

Plsql return associative array function

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

Upvotes: 0

Views: 6136

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

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;
/

Upvotes: 2

Related Questions