Re: Prepare phase failed ORA-00904: "TABTYPE": invalid identifier

Posted by satish on Aug 28, 2023; 5:24am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Prepare-phase-failed-ORA-00904-TABTYPE-invalid-identifier-tp12029p12035.html

Hi Erman,

Looks like there is no table TAB in test instance where we are facing the issue.
Can you please suggest what can be done in this case?

TEST:
======

SQL> desc tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNO                                                NUMBER(38)
 NAME                                               CHAR(20)
 CITY                                               CHAR(10)
 EMP                                                CHAR(3)


SQL> sho user
USER is "APPS"
SQL>

SQL> select dbms_metadata.get_ddl('TABLE','TAB') from dual;

DBMS_METADATA.GET_DDL('TABLE','TAB')
--------------------------------------------------------------------------------

  CREATE TABLE "APPS"."TAB"
   (    "SNO" NUMBER(*,0),
        "NAME" CHAR(20),
        "CITY" CHAR(10),
        "EMP" CHAR(3)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPS_TS_TX_DATA"


OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
TAB                            SYS                            VIEW
TAB                            PUBLIC                         SYNONYM
TAB                            SYSTEM                         SYNONYM
TAB                            APPS                           TABLE

SQL>




PROD:
======

SQL> desc tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TNAME                                     NOT NULL VARCHAR2(128)
 TABTYPE                                            VARCHAR2(13)
 CLUSTERID                                          NUMBER

SQL> select dbms_metadata.get_ddl('TABLE','TAB') from dual;
ERROR:
ORA-31603: object "TAB" of type TABLE not found in schema "APPS"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2588
ORA-06512: at "SYS.DBMS_METADATA", line 2827
ORA-06512: at "SYS.DBMS_METADATA", line 3608
ORA-06512: at "SYS.DBMS_METADATA", line 5008
ORA-06512: at "SYS.DBMS_METADATA", line 5336
ORA-06512: at "SYS.DBMS_METADATA", line 6702
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1

no rows selected


OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
TAB                            SYS                            VIEW
TAB                            PUBLIC                         SYNONYM
TAB                            SYSTEM                         SYNONYM

SQL>


Thank You