OraFAQ Forum: Server Utilities » Synonym name in SQL Loader

Home » RDBMS Server » Server Utilities » Synonym name in SQL Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Synonym name in SQL Loader [message #71791]

Mon, 13 January 2003 07:29

Deborah
Messages: 18
Registered: June 2002

Junior Member

Can I put synonym name instead of table name
in the sql loader control script? I am having
some queer phenomena in some testings.

Also would the following work, if synonym name
is allowed:
....
2) INFILE *
3) TRUNCATE INTO TABLE SYN_ITEM_MSTR
.....
(stress is on line 3)

also can I do the following?

SQL> TRUNCATE TABLE SYN_ITEM_MSTR;

(SYN_ITEM_MSTR is a synonym to ITEM_MSTR table in
the same schema)

TIA,
Debby

Report message to a moderator

Re: Synonym name in SQL Loader [message #71792 is a reply to message #71791]

Mon, 13 January 2003 07:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

i beleive , you CANNOT TRUNCATE via synonym
first,
A synonym refers to object belonging others and you are not the owner of the object. 
becuase 
You cannot create synonym with the same name as the object in same schema
else
you need to have a different names for table and synonymn in the same schema. 
[i]

ORA-01471: cannot create a synonym with same name as object [/i]

TRUNCATE TABLE DDL is valid only for tables, clusters
 there is NO OPTION for a synonym.
[i] 
ERROR at line 1:
ORA-00942: table or view does not exist 
[/i]

Report message to a moderator

Re: Synonym name in SQL Loader Pretty Queer [message #71793 is a reply to message #71792]

Mon, 13 January 2003 08:03

Deborah
Messages: 18
Registered: June 2002

Junior Member

Well, while I tried to truncate a synonym,
SQL> TRUNCATE TABLE SYN_ITM_MSTR;

I found this error instead:

ORA-04020: deadlock detected while trying to lock object 3478168772389872276476874988
this was the first queer observation.

The synonym name and actual table name are different.
The owner is same, though (Not public syn either).

But The queer thing I mentioned earlier was that, in
the sqlloader script, the synonym name is working fine
even with "TRUNCATE INTO TABLE <SYN_NAME>" OPTION.

I am using Oracle 8.1.5

Bottomline: All I wanted to know is
"How safe is it to use syn name in sqlloader ctl file.
As I would like programmers to use the syn name rather
than the table name"

TIA,
Debby

Report message to a moderator

Previous Topic:	Moving data from MS Sql Server to Oracle
Next Topic:	any tool like personal oracle Navigator for oracle8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 13 18:07:02 CDT 2024