[canton07]

My oracle version is 10.2.0.3.0.

In Toad I have no problem
1) to create table trg_farinvc as select
2) select * from all_objects where object_name like 'TRG_FARINVC%'
3) drop table TRG_FARINVC PURGE
3) create table trg_farinvc as select --> create table again no problem.
4) drop table TRG_FARINVC PURGE --> everything is good so far.

My script run via sqlplus:
Declare
...
Begin
Execute immediate 'CREATE TABLE trg_farinvc as select... ';
End;
.
/
Run
PROBLEM:
ORA-00955: name is already used by an existing object

BUT THE TABLE was already purged in TOAD prior running this script.

Thanks,
Trang

[ordba]

SQL> create table testing_tbl1 as select * from testing_tbl;

Table created.

SQL> select table_name from user_tables where table_name like 'TESTING%';

TABLE_NAME
------------------------------
TESTING_TBL
TESTING_TBL1

2 rows selected.

SQL> drop table testing_tbl1 purge;

Table dropped.

SQL> select table_name from user_tables where table_name like 'TESTING%';

TABLE_NAME
------------------------------
TESTING_TBL

1 row selected.

SQL> declare
2 begin
3 execute immediate 'create table testing_tbl1 as select * from testing_tbl';
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> select table_name from user_tables where table_name like 'TESTING%';

TABLE_NAME
------------------------------
TESTING_TBL
TESTING_TBL1

2 rows selected.

SQL>

Does this help ?

[ordba]

Run this before you execute Statement from SQL*Plus

Select owner,table_name from all_tables where table_name like 'TRG_FARINVC%' ;

Show user

Are you connecting as same user from TOAD and SQL*Plus ?

[canton07]

Thank you for answering my question. I should not put "run" which call the program again and create a table again.

Trang