Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question
4  
table or view does not exist –  swasheck Dec 5 '13 at 18:09

2 Answers 2

up vote 2 down vote accepted

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?

share|improve this answer
    
Sorry about the mistake, I didn't see, I just changed the code to post it. –  Logar314159 Dec 6 '13 at 15:37

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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