In attempting to use the Cast(Collect(myObject) as TableOfMyObject)) I've run across what appears to be a bug. I've created object types in another schema and when I attempt to use the Collect statement it fails with ORA-00902: invalid datatype. However, if change my current schema to that user, ALTER SESSION SET CURRENT_SCHEMA = schemaOfMyObject, it works. Upon further investigation, it appears Oracle fails in an attempt to create a system type ie, "SYSTPJd9nJewNKkTgVAAVF4olbA==" without a schema. Note, multiple attempts result in multiple failed system object types.
Is this a bug?
Also, it works just fine with primitive types, ie CREATE TYPE scott.number_ntt AS TABLE OF NUMBER;
| How to Reproduce the Bug |
|---|
/* Connect as user other than scott */ CREATE OR REPLACE TYPE scott.vertex_t AS OBJECT (x NUMBER, y NUMBER); / CREATE OR REPLACE TYPE scott.vertex_ntt AS TABLE OF vertex_t; / BEGIN DECLARE vertex_table scott.vertex_ntt; BEGIN SELECT CAST(COLLECT(x) AS scott.vertex_ntt) INTO vertex_table FROM ( SELECT scott.vertex_t(1,2) x FROM dual UNION ALL SELECT scott.vertex_t(3,4) FROM dual ); END; END; / /* OUTPUT */Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as userOtherThanScott@yourdatabase SQL> Type created Type created ORA-00902: invalid datatype ORA-06512: at line 6 |
| Workaround using Set Current Schema |
|---|
/* Connect as user other than scott */ CREATE OR REPLACE TYPE scott.vertex_t AS OBJECT (x NUMBER, y NUMBER); / CREATE OR REPLACE TYPE scott.vertex_ntt AS TABLE OF vertex_t; / /* Workaround - Set current schema, everything else remains unchanged */ ALTER SESSION SET CURRENT_SCHEMA = scott; BEGIN DECLARE vertex_table scott.vertex_ntt; BEGIN SELECT CAST(COLLECT(x) AS scott.vertex_ntt) INTO vertex_table FROM ( SELECT scott.vertex_t(1,2) x FROM dual UNION ALL SELECT scott.vertex_t(3,4) FROM dual ); END; END; / /* OUTPUT */Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as userOtherThanScott@yourdatabase SQL> Type created Type created Session altered PL/SQL procedure successfully completed |