Automatic type generation

Question

I've created a function in a PL/SQL package that uses a custom type defined as table of numbers. I use an object of this type in an SQL query with the SELECT COLUMN_VALUE instruction like this :

The type definition in the package:

type T_IDS is table of my_table.col_id%type;

The query inside a procedure in the package body:

l_ids_list T_IDS ;
begin
select col_ids bulk collect into T_IDS from my_table;
select sum(t.rec_value) into total_value 
          from my_table t where t.col_id in (
            select column_value from Table(l_ids_list) );

Everything works fine and when I compile this code, I can see a new type generated under my schema_name/type section.

Once I installed this on test environment, it fails the compilation with the errors :

Error: PLS-00642: local collection types not allowed in SQL statements
Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Database versions (local and test) are exactly the same, 11g. Is there a way to activate such a generation on the DBMS?

exemple to reproduce :

create table my_table (
col_id number,
rec_value number
);

insert into my_table (col_id, rec_value) values (1,100);
insert into my_table (col_id, rec_value) values (2,200);
insert into my_table (col_id, rec_value) values (3,300);
insert into my_table (col_id, rec_value) values (4,400);

commit;

package creation :

create or replace package test_pck as

type T_IDS is table of my_table.col_id%type;

procedure test_list;

end test_pck;
/

create or replace
package body test_pck as

procedure test_list is
  l_ids_list T_IDS ;
  total_value number;
begin
  select col_id bulk collect into l_ids_list from my_table;
  select sum(t.rec_value) into total_value 
            from my_table t where t.col_id in (
              select column_value from Table(l_ids_list) );
end test_list;
end test_pck;
/

Answer 1

what you are doing is wrong. you should be creating SQL types and not using pl/sql types to access the TABLE function.

now as to WHY it actually sorta worked in your Dev env.

The silent pl/sql table creation is intended for pipelined functions and has been around for a while, but you're using this in a non-pipelined function and as such THIS SHOULD FAIL. In 11g release 1 (11.1.0.7 to be precise) though, a bug means that it actually compiles. If you actually tried to run it though you would get an error:

SQL> create package body foo
  2  as
  3
  4    procedure test
  5     is
  6             l_ids_list T_IDS ;
  7             total_value number;
  8     begin
  9             select col_id bulk collect into l_ids_list from my_table;
 10             select sum(t.rec_value) into total_value
 11               from my_table t
 12              where t.col_id in (select column_value from Table(l_ids_list));
 13     end;
 14  end;
 15  /

Package body created.

SQL> exec foo.test;
BEGIN foo.test; END;

*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "TEST.FOO", line 10
ORA-06512: at line 1
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

now, Oracle FIXED this buggy behaviour in 11.2.0.3. now the error is thrown at compile time:

SQL> create package body foo
  2  as
  3
  4    procedure test
  5     is
  6             l_ids_list T_IDS ;
  7             total_value number;
  8     begin
  9             select col_id bulk collect into l_ids_list from my_table;
 10             select sum(t.rec_value) into total_value
 11               from my_table t
 12              where t.col_id in (select column_value from Table(l_ids_list));
 13     end;
 14  end;
 15  /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/3     PL/SQL: SQL Statement ignored
12/48    PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

12/54    PLS-00642: local collection types not allowed in SQL statements
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

in short, create an SQL type with create type and use that instead:

SQL> create type T_IDS as table of number;
  2  /

Type created.

SQL> create package body foo
  2  as
  3
  4    procedure test
  5     is
  6             l_ids_list T_IDS ;
  7             total_value number;
  8     begin
  9             select col_id bulk collect into l_ids_list from my_table;
 10             select sum(t.rec_value) into total_value
 11               from my_table t
 12              where t.col_id in (select column_value from Table(l_ids_list));
 13     end;
 14  end;
 15  /

Package body created.

SQL> exec foo.test

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production