Wednesday, June 8, 2011

ORA-04043 , ORA-00942 : object does not exist

Today, i have face very usual error. I have imported a table from ms-excess into "scott" schemas. when i check the table in scott schemas,i found it was there.And when try to access the table it throws as error "ORA-00942 " . I get puzzled.

SQL> select * from tab;
TNAME                          TABTYPE              CLUSTERID
-------------------------      --------------                ----------
BONUS                          TABLE
DEPT                             TABLE
EMP                               TABLE
SALGRADE                   TABLE
Table11                          TABLE

SQL> select * from table11;
select * from table11
ERROR at line 1:
ORA-00942: table or view does not exist

then, i have decided to describe the table, and got the error "ORA-04043"  .

SQL> desc Table11
ORA-04043: object Table11 does not exist

SQL> desc dept
 Name                                      Null?                           Type
 --------------------------            -------------             ---------------------
 DEPTNO                               NOT NULL           NUMBER(2)
 DNAME                                                               VARCHAR2(14)
 LOC                                                                    VARCHAR2(13)

While in case of table "dept"  it is working fine,then i have decide to rename the table, so that it may solved.

SQL> rename Table11 to emp1;
rename Table11 to emp1
ERROR at line 1:
ORA-04043: object TABLE11 does not exist

After some analysis i come to conclusion that the table are export from the ms-excess and ms-excess support the char datatype i.e, it is right padded . So to solve this issue, i  put the table in double quotes to excess the table. For detail click here 

SQL> desc "Table11"
 Name                                         Null?                           Type
 -----------------------------               --------             ------------------------
 ID                                                                          VARCHAR2(20)
 ACCOUNTNO                                                      BINARY_DOUBLE
 TEMPLATENO                                                     BINARY_DOUBLE
 DEFAULTTEMPLATE                                          BINARY_DOUBLE

ORA_04043 is an special error and cause due to various reason. Few Possible causes are :
- An attempt was made to rename an index or a cluster, or some other object that cannot be renamed.
- An invalid name for a table, view, sequence, procedure, function, package, or package body was entered.

Enjoy     :-) 

No comments: