Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL table :: ORA-00902: invalid datatype (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
PL/SQL table :: ORA-00902: invalid datatype [message #659374] Fri, 13 January 2017 11:38 Go to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Hi All,

I am trying to test the feature in 12c wherein we can use table operator in locally defined PL/SQL types.
Below is the code:

create table test_a(a number,b number)
/
create table test_b(a number,b number)
/
insert into test_a
select 1,1 from dual connect by level <=1000
/
commit
/
CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS table of test_a%rowtype;
  PROCEDURE test1;
END;
/
CREATE OR REPLACE PACKAGE BODY test_api AS
  PROCEDURE test1 IS
    l_tab1 t_row;
    v_err varchar2(500);
    v_err1 varchar2(500);
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM  test_a;
  insert into test_b
   SELECT * FROM TABLE(l_tab1);
   commit;
   exception 
    when others then
     v_err := SQLCODE;
     v_err1 := SQLERRM;
     dbms_output.put_line(v_err||'::-'||v_err1);
  END;
END;
/

But when I am running it I am getting below error:

SET SERVEROUTPUT ON
EXEC test_api.test1;

anonymous block completed
-902::-ORA-00902: invalid datatype

Can someone advise whats wrong here??
Thanks
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659375 is a reply to message #659374] Fri, 13 January 2017 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 26187
Registered: January 2009
Location: SoCal
Senior Member
>Can someone advise whats wrong here??
yes, remove, eliminate & delete the whole & complete EXCEPTION handler; then rerun the procedure & post results back here.
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659376 is a reply to message #659375] Fri, 13 January 2017 11:51 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
@BlackSwan - Thanks, below is the output once I remove the exception handler:

Error report -
ORA-00902: invalid datatype
ORA-06512: at "TARDBO.TEST_API", line 10
ORA-06512: at line 1
00902. 00000 -  "invalid datatype"
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659377 is a reply to message #659376] Fri, 13 January 2017 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 26187
Registered: January 2009
Location: SoCal
Senior Member
L_TAB1 is datatype T_ROW which does not exist in TEST_B table.

BTW, do NOT do in PL/SQL that which can be done in plain SQL.

INSERT INTO TEST_B SELECT * FROM TEST_A;
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659380 is a reply to message #659377] Fri, 13 January 2017 13:15 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Thanks. Is there a workaround for this? Basically I am trying to test the feature in 12c wherein I can insert into test_b using a locally defined table of PL/SQL.
I have achieved the same using Object types and types on them defined globally but want it in 12c using locally defined objects.
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659386 is a reply to message #659380] Fri, 13 January 2017 18:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_a (a NUMBER, b NUMBER)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_b (a NUMBER, b NUMBER)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_a SELECT 1, 1 FROM DUAL CONNECT BY LEVEL <= 10
  2  /

10 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE test_api
  2  AS
  3    TYPE t_row IS TABLE OF test_a%ROWTYPE;
  4    PROCEDURE test1;
  5  END test_api;
  6  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY test_api
  2  AS
  3    PROCEDURE test1
  4    IS
  5  	 l_tab1  t_row;
  6    BEGIN
  7  	 SELECT * BULK COLLECT INTO l_tab1 FROM test_a;
  8  	 FORALL i IN 1 .. l_tab1.COUNT
  9  	   INSERT INTO test_b VALUES l_tab1(i);
 10    END test1;
 11  END test_api;
 12  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXEC test_api.test1

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_b
  2  /

         A          B
---------- ----------
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1

10 rows selected.

[Updated on: Fri, 13 January 2017 18:47]

Report message to a moderator

Re: PL/SQL table :: ORA-00902: invalid datatype [message #659387 is a reply to message #659386] Fri, 13 January 2017 21:27 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Thanks Barbara. But I was just wondering if there is some limitation still in 12c while using table operator for local collections or I am doing something wrong here. It would be great if I can get some suggestions on how to achieve the insert on the target table using the table operator using this new feature in Oracle 12c. Or simply its not possible Confused
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659388 is a reply to message #659387] Fri, 13 January 2017 21:52 Go to previous messageGo to next message
BlackSwan
Messages: 26187
Registered: January 2009
Location: SoCal
Senior Member
post URL to where the table operator is documented
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659389 is a reply to message #659387] Fri, 13 January 2017 22:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
You can do the following, but it should be slower than using forall.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_a (a NUMBER, b NUMBER)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_b (a NUMBER, b NUMBER)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> INSERT INTO test_a SELECT 1, 1 FROM DUAL CONNECT BY LEVEL <= 10
  2  /

10 rows created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE test_api
  2  AS
  3    TYPE t_row IS TABLE OF test_a%ROWTYPE;
  4    PROCEDURE test1;
  5  END test_api;
  6  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY test_api
  2  AS
  3    PROCEDURE test1
  4    IS
  5  	 l_tab1  t_row;
  6    BEGIN
  7  	 SELECT * BULK COLLECT INTO l_tab1 FROM test_a;
  8  	 FOR i IN
  9  	   (SELECT * FROM TABLE (l_tab1))
 10  	 LOOP
 11  	   INSERT INTO test_b VALUES (i.a, i.b);
 12  	 END LOOP;
 13    END test1;
 14  END test_api;
 15  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXEC test_api.test1

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_b
  2  /

         A          B
---------- ----------
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1
         1          1

10 rows selected.
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659390 is a reply to message #659389] Fri, 13 January 2017 22:29 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
@BlackSwan : Didn't find links with the exact example of insertion, but there are few just used to display the values :
https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1#nested-table-example
But as the description for this example says, we can now use the table operator with local pl/sql collections so was just trying to verify if can do inserts using the same.
@Barbara : Thanks again. Indeed the for loop would slow things now. I was expecting something similar and pretty straight forward like insert into table_name select * from table(<collection>) which we can do if we use objects types defined globally.
It does seem we do have some restrictions like we have to first insert the values fetched by the table operator into some variables and then only can use the corresponding values.
Re: PL/SQL table :: ORA-00902: invalid datatype [message #659391 is a reply to message #659390] Fri, 13 January 2017 22:59 Go to previous message
BlackSwan
Messages: 26187
Registered: January 2009
Location: SoCal
Senior Member
Realize & understand that SQL & PL/SQL are two entirely different languages that are processed by different "engines".
the SQL engine know nothing about PL/SQL objects
Previous Topic: ORA-14155
Next Topic: Regarding Hints
Goto Forum:
  


Current Time: Tue Oct 16 14:07:15 CDT 2018