Automatic type generation
I have created a function in a PL / SQL package that uses a custom type defined as a table of numbers. I am using an object of this type in a SQL query with a SELECT COLUMN_VALUE statement like this:
Type definition in package:
type T_IDS is table of my_table.col_id%type;
Request within the 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 see a new type generated in the schema_name / type section.
Once I installed this in a test environment, it failed to compile with 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 version (local and test) is the same, 11g. Is there a way to activate this generation in the DBMS?
serve 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;
/
source to share
what you are doing is wrong. you should create SQL types and not use pl / sql types to access the function TABLE
.
now that WHY does it really sort of worked in your dev env.
The pl / sql table creation problem is for pipelined functions and has been around for a while, but you are using this in a non-pipeline function and, as such, MUST BE FIXED. In 11g release 1 (11.1.0.7 to be precise), however, the bug means it actually compiles. If you actually tried to run it even though you would get the 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 behavior in 11.2.0.3. now the error occurs 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 a SQL type with create type
and use this 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
source to share