Declare array of TABLE%TYPE

2019-09-06 18:56发布

I am using the to receive collection from Java to PL/SQL procedure to insert records into table.

CREATE OR REPLACE TYPE PROJECT_TYPE IS OBJECT
(
    project_id NUMBER,
    project_desc VARCHAR2 (10),
    project_title VARCHAR2 (25)
);

PROCEDURE project_values (parray IN project_type)
IS
BEGIN
    INSERT INTO  projects (project_id, project_desc, project_title)
        SELECT  *
          FROM  TABLE (parray);
END;

Instead of the above I would like to use

TYPE array IS TABLE OF projects%ROWTYPE INDEX BY VARCHAR2 (25);

and I have tried to call as

INSERT INTO  projects (project_id, project_desc, project_title)
select * from table(p_array);

I am getting the following errors

PLS-00382: expression is of wrong type
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

How can I use associative arrays instead of type object there by declaring all columns explicitly? if I could use projects%ROWTYPE then I do not need to declare all columns.

And what is the best approach? use associative arrays of TABLE%TYPE or declare all columns as TYPE object?

Edit 1

I would like to know the following and can this be achieved in database level?

  1. Can I use Table of array if I am would like to pass array from Java to a procedure? If so can I declare it at package level or it should be at schema level?

  2. Instead of manually writing all columns while creating type as object, if I can create array of table type, then it will be easy to manage.

0条回答
登录 后发表回答