Opening a cursor from a 2D array in PLsql (Oracle 19g) - Stack Overflow

admin管理员组

文章数量:1278976

The provided anonymous procedure demonstrates what I'm trying to achieve. Basically, I want to open a cursor from a 2D table. The actual plsql procedure (not shown here) has an OUT sys_refcursor parameter, which should be opened from a 2D table. The syntax below works for 1D tables but I need to support 2d tables as well.

declare

type s_arr is table of varchar2(255) index by pls_integer;
type d_arr is table of s_arr index by pls_integer;
cities d_arr;
cities_cur sys_refcursor;
begin

cities(1)(1) := 'Vienna';
cities(1)(2) := 'Graz';
cities(2)(1) := 'Milan';
cities(2)(2) := 'Turin';

--this line doesn't compile ( PLS-00382 expression is of wrong type )
open cities_cur for select ct.* from TABLE(cities) ct;

dbms_output.put_line(cities(1)(1));

end;
/

How can this be done? Is this even suppported?

Thanks

The provided anonymous procedure demonstrates what I'm trying to achieve. Basically, I want to open a cursor from a 2D table. The actual plsql procedure (not shown here) has an OUT sys_refcursor parameter, which should be opened from a 2D table. The syntax below works for 1D tables but I need to support 2d tables as well.

declare

type s_arr is table of varchar2(255) index by pls_integer;
type d_arr is table of s_arr index by pls_integer;
cities d_arr;
cities_cur sys_refcursor;
begin

cities(1)(1) := 'Vienna';
cities(1)(2) := 'Graz';
cities(2)(1) := 'Milan';
cities(2)(2) := 'Turin';

--this line doesn't compile ( PLS-00382 expression is of wrong type )
open cities_cur for select ct.* from TABLE(cities) ct;

dbms_output.put_line(cities(1)(1));

end;
/

How can this be done? Is this even suppported?

Thanks

• oracle-database
• plsql
• sys-refcursor

Share Improve this question Follow asked Feb 24 at 10:14 dsp_userdsp_user 2,12122 gold badges1717 silver badges2626 bronze badges 1

• "The syntax below works for 1D tables" No, it does not fiddle. – MT0 Commented Feb 24 at 10:53

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Associative arrays, with an INDEX BY clause, are PL/SQL only data-types; you can use them in a PL/SQL scope but not in an SQL scope.

To use an array in an SQL scope you need to declare it as a collection (nested table) type or as a VARRAY.

For example:

CREATE TYPE s_arr IS TABLE OF VARCHAR2(255);
CREATE TYPE d_arr IS TABLE OF s_arr;

Then you can use:

DECLARE
  cities     d_arr;
  cities_cur sys_refcursor;
  city       VARCHAR2(255);
BEGIN
  cities := d_arr(
    s_arr('Vienna', 'Graz'),
    s_arr('Milan', 'Turin')
  );

  OPEN cities_cur FOR
    SELECT cta.COLUMN_VALUE
    FROM   TABLE(cities) ctd,
           TABLE(ctd.COLUMN_VALUE) cta;

  LOOP
    FETCH cities_cur INTO city;
    EXIT WHEN cities_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(city);
  END LOOP;
END;
/

Which outputs:

Vienna
Graz
Milan
Turin

fiddle

本文标签： Opening a cursor from a 2D array in PLsql (Oracle 19g)Stack Overflow