ORA-00942 error, table will be created

Question

I'm having a problem with getting ORA-00942 errors, saying the table doesn't exist. It's true that it doesn't, but before the point I'm getting the errors at, it should be creating the table if it doesn't exist. Thus, when it gets to that point, the table should be there:

BEGIN
  SELECT COUNT(*) INTO tCount
  FROM user_tables
  WHERE table_name='MY_TABLE';
  IF tCount=0 THEN
    EXECUTE IMMEDIATE 'CREATE TABLE MY_TABLE
      (T_ID NUMBER(38,0),
      T_DATE DATE,
      T_COUNT NUMBER(38,0),
      CONSTRAINT MY_TABLE_pk PRIMARY KEY (T_ID,T_DATE) )';
  END IF;

  LOOP
    FOR idx IN 1 .. CursorTable.COUNT LOOP
      SELECT COUNT(*) INTO pk_check
      FROM MY_TABLE
      WHERE T_ID=CursorTable(idx).T_ID AND T_DATE=CursorTable(idx).T_DATE;
      IF (pk_check=0) THEN
        INSERT INTO MY_TABLE
          (T_ID,
          T_DATE,
          T_COUNT)
        VALUES 
          (CursorTable(idx).T_ID,
          CursorTable(idx).T_DATE,
          CursorTable(idx).T_COUNT);
      END IF;
    END LOOP;
  END LOOP;
END;
/

Your code won't compile if the table doesn't exist. Apply EXECUTE IMMEDIATE on SELECT count ... and INSERT too. — Multisync, Nov 26 '14 at 14:54
possible duplicate of [EXECUTE IMMEDIATE Temp table in oracle does not get created ORA-00942](http://stackoverflow.com/questions/26212557/execute-immediate-temp-table-in-oracle-does-not-get-created-ora-00942) — user272735, Nov 27 '14 at 06:08

score 0 · Answer 1 · answered Nov 26 '14 at 15:19

You have this problem because your table creation and then the subsequent selection(LOOP) is inside the same BEGIN...END block.

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not cause an implicit commit. referred from (http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html)

Try to move the selection (LOOP) outside the BEGIN..END and it should work.

ORA-00942 error, table will be created

1 Answers1