Skip to Main Content
  • Questions

  • Truncate Table Command not able to recognize Table in EBR enabled Oracle database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Piyush.

Asked: August 13, 2019 - 10:34 pm UTC

Last updated: August 15, 2019 - 2:06 am UTC

Version: 19C

Viewed 1000+ times

You Asked

We are using EBR enabled Oracle Database and hence have ability to have same name for a table and View.

Following are two objects in FUSION schema:

wsh_interface_errors TABLE FUSION
WSH_INTERFACE_ERRORS VIEW FUSION

Now when trying to run below SQL:
Truncate table FUSION.wsh_interface_errors it errors out with following error:

ORA-01702: a view is not appropriate here

Is there a way to achieve this, apart from renaming the views etc as these are standard product tables/views so would not like to rename them etc.

Thanks, Piyush

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/is8hx607919v54d4gk625koua

and Connor said...

Well...its not the same name, because tables and view share the same namespace

SQL> create table t ( x int );

Table created.

SQL> create view t as select * from t;
create view t as select * from t
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object


From your question, it looks to me like the table is in lower case, so you can do:

truncate table "wsh_interface_errors"

Just always remember...truncate is brutal...there's no way back from a truncate :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

EBR setup for Fusion

John Keymer, August 14, 2019 - 9:35 am UTC

OP - are you sure that it is setup like that? In EBR for E-Business Suite (so I'm presuming Fusion would be the same), you get this:

Owning Schema (WSH):
  wsh_interface_errors  [table]
  wsh_interface_errors# [editioning view] -> wsh_interface_errors

Apps (Fusion) Schema:
  wsh_interface_errors [synonym] -> wsh_interface_errors#


The key point being the Fusion schema is a synonym pointing to the editioning view. So if you are going to truncate, you would instead need to:

truncate table wsh.wsh_interface_errors;


All that said - you have to reference the owning schema directly anyway - as I found out in my question here ( https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9540972000346226903 ). From that post, Connor references the following Oracle documentation ( https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SYNONYM.html#GUID-A806C82F-1171-478E-A910-F9C6C42739B2 ) which clearly states that using a synonym for a truncate operation is not supported:


You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE.

You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.
Connor McDonald
August 15, 2019 - 2:06 am UTC

Thanks for the extra EBS insight John!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.