Oracle – Table Does Not Exist Error

oracleplsqlstored-procedurestemporary-tables

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?

Best Answer

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?

Related Question