CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX;
It throws the error:
PL/SQL: ORA-00942: table or view does not exist
What is the problem?
If you do intend on working with only in-memory PL/SQL type-based tables, this is what you need to do:
CREATE OR REPLACE PROCEDURE ProcedureX AS TYPE AVERAGE_COUNT_TBL IS TABLE OF NUMBER; AVERAGE_COUNT AVERAGE_COUNT_TBL; CURSOR C IS SELECT 10 FROM DUAL; BEGIN OPEN C; FETCH C BULK COLLECT INTO AVERAGE_COUNT; CLOSE C; END ProcedureX; /
The cursor is just used to demonstrate.
I’ll add that your question is poor, as you didn’t even take the time to make sure that
AVERAGE_COUNT_TBL matched with itself on the next row – you used
AVG_TRANS_HOUR_TBL instead. If you expect random internet people to spend time writing quality answers, would it hurt to make sure your question was of sufficient quality?
A PL/SQL table is NOT the same as a temporary table in Oracle.
An Oracle temporary table is created with they syntax
CREATE TEMPORARY TABLE xxx ...
and is a DBMS table the contents of which are transient, vanishing either at the end of the transaction or the end of the session. It can be used in any SQL just like a normal table.
A PL/SQL table which you have defined above cannot be used in SQL but must be referenced programmatically. See http://docs.oracle.com/cd/A57673_01/DOC/server/doc/PLS23/ch4.htm for an overview on how to do this.