PLSQL Subtype of Nested Table PLS-00355: use of pl/sql table not allowed in this context-CodePudding

Edit: as per the answers, this works in 21C but not before. Looks like it is an Oracle bug that was fixed. The workaround in previous versions is provided as the accepted answer.

In PL/SQL, a subtype of a nested table behaves inconsistently compared to a regular nested table. It seems like it is not possible to use a subtype of a nested table. Is this correct?

create or replace package test_subtype_pkg
is
    type t_foos is table of varchar2(10);
    subtype t_bars is t_foos;
    
    procedure main;
end;
/

The following l_bars := t_bars() fails to compile with PLS-00355: use of pl/sql table not allowed in this context

create or replace package body test_subtype_pkg
is
    procedure main
    is
        l_foos t_foos;
        l_bars t_bars;
    begin
        l_foos := t_foos(); -- compiles correctly
        l_bars := t_bars(); -- PLS-00355: use of pl/sql table not allowed in this context
    end;
end;
/

However, the following compiles without error. During run time it fails with ORA-06531: Reference to uninitialized collection:

create or replace package body test_subtype_pkg
is
    procedure main
    is
        l_bars t_bars;
    begin
        l_bars.extend; -- At run time, ORA-06531: Reference to uninitialized collection    
        l_bars(1) := 'foo';
    end;
end;
/

Is it possible to use a subtype of a nested table?

CodePudding user response：

You can use the sub-type but it only appears to work if you call the constructor from the base-type:

create or replace package test_subtype_pkg
is
    type t_foos is table of varchar2(10);
    subtype t_bars is t_foos;
    
    procedure main;
end;
/

create or replace package body test_subtype_pkg
is
    procedure main
    is
        l_foos t_foos;
        l_bars t_bars;
    begin
        l_foos := new t_foos();
        l_bars := new t_foos();
        l_bars.extend;
        l_bars(1) := 'foo';
    end;
end;
/

Note: the NEW keyword is optional.

Compiles without errors and then:

BEGIN
  test_subtype_pkg.main();
END;
/

Works without a runtime error.

Note: You get the runtime error in your second example because you do not initialise the collection as you never call a constructor.

Oracle 18 fiddle

CodePudding user response：

I don't have 18c; this is 21cXE and both codes work OK.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

SQL> create or replace package test_subtype_pkg
  2  is
  3      type t_foos is table of varchar2(10);
  4      subtype t_bars is t_foos;
  5
  6      procedure main;
  7  end;
  8  /

Package created.

You said that this failed on your database; it works for me:

SQL> create or replace package body test_subtype_pkg
  2  is
  3      procedure main
  4      is
  5          l_foos t_foos;
  6          l_bars t_bars;
  7      begin
  8          l_foos := t_foos(); -- compiles correctly
  9          l_bars := t_bars(); -- PLS-00355: use of pl/sql table not allowed in this context
 10      end;
 11  end;
 12  /

Package body created.

SQL> exec test_subtype_pkg.main;

PL/SQL procedure successfully completed.

SQL>

Your code that compiles, but doesn't work - it is because you really didn't initialize the collection (see line #5 which shows how to do it):

SQL> create or replace package body test_subtype_pkg
  2  is
  3      procedure main
  4      is
  5          l_bars t_bars := t_bars();
  6      begin
  7          l_bars.extend; -- At run time, ORA-06531: Reference to uninitialized collection
  8          l_bars(1) := 'foo';
  9      end;
 10  end;
 11  /

Package body created.

SQL> exec test_subtype_pkg.main;

PL/SQL procedure successfully completed.

SQL>