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

  • 4
    table or view does not exist – swasheck Dec 5 '13 at 18:09
2

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?

  • Sorry about the mistake, I didn't see, I just changed the code to post it. – Logar314159 Dec 6 '13 at 15:37
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.