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?
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?
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.