OraFAQ Forum: Server Utilities » Rollback in Sql * loader

Home » RDBMS Server » Server Utilities » Rollback in Sql * loader

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

Rollback in Sql * loader [message #274188]

Sun, 14 October 2007 23:16

rk1982
Messages: 24
Registered: September 2007

Junior Member

Hi,I want to rollback all records of a file,if loading of that file through sql* loader is not successful.
Can you please help me how to do it.

Report message to a moderator

Re: Rollback in Sql * loader [message #274198 is a reply to message #274188]

Mon, 15 October 2007 01:13

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In decreasing preferable order:
- Use external table
- Use direct load
- Use a buffer larger enough to contain all the data you want to load.

Regards
Michel

Report message to a moderator

Re: Rollback in Sql * loader [message #274213 is a reply to message #274198]

Mon, 15 October 2007 02:00

rk1982
Messages: 24
Registered: September 2007

Junior Member

I try to create external table and load data into it.
I log in to sys account and run the attached sql it will create table but not load data.kindly see the attached sql

Attachment: load.sh
(Size: 0.11KB, Downloaded 1197 times)

Report message to a moderator

Re: Rollback in Sql * loader [message #274215 is a reply to message #274213]

Mon, 15 October 2007 02:03

rk1982
Messages: 24
Registered: September 2007

Junior Member

very sorry i attache wrong file.kindly find it here

Attachment: Load.sql
(Size: 0.65KB, Downloaded 1132 times)

Report message to a moderator

Re: Rollback in Sql * loader [message #274227 is a reply to message #274213]

Mon, 15 October 2007 02:43

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Don't use SYS but for maintenance purpose.

What is the question now?

Regards
Michel

Report message to a moderator

Re: Rollback in Sql * loader [message #274229 is a reply to message #274227]

Mon, 15 October 2007 02:50

rk1982
Messages: 24
Registered: September 2007

Junior Member

See i try to run attached sql.The external table is created but when i am trying to select it its giving me error.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "date_format": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, nullif, oracle_date, oracle_number, raw, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01007: at line 8 column 28
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Attachment: Load.sql
(Size: 0.69KB, Downloaded 1151 times)

Report message to a moderator

Re: Rollback in Sql * loader [message #274234 is a reply to message #274229]

Mon, 15 October 2007 03:13

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just a syntax error, check with the documentation.
Read again the error message, it gives a clue to what you have to do.

Regards
Michel

Report message to a moderator

Re: Rollback in Sql * loader [message #274246 is a reply to message #274229]

Mon, 15 October 2007 03:59

rk1982
Messages: 24
Registered: September 2007

Junior Member

I removed the previous mistake but now it is reporting that my data file(a.dat) is not in the direcotory name
xtern_data_dir as '/arbor1/arbor/Rohit' but a.dat is present in this path /arbor1/arbor/Rohit
Can you suggest me the possible reason of this.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file a.dat in XTERN_DATA_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Attachment: Load.sql
(Size: 0.76KB, Downloaded 1121 times)

Report message to a moderator

Re: Rollback in Sql * loader [message #274254 is a reply to message #274246]

Mon, 15 October 2007 04:19

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The directory must be on the server and oracle instance owner must be able to access it.

Regards
Michel

Report message to a moderator

Re: Rollback in Sql * loader [message #274259 is a reply to message #274246]

Mon, 15 October 2007 04:32

rk1982
Messages: 24
Registered: September 2007

Junior Member

Sir,Directory is on server only on which oracle is installed.
I am using AIX.can me idea how to check oracle have acess to directory.
resolve my problem

arbor@billdev:/arbor1/arbor/Rohit 102 : ls -l a.dat
-rwxrwxrwx 1 arbor arboradm 176 Oct 11 23:48 a.dat
arbor@billdev:/arbor1/arbor/Rohit 103 :

arbor@billdev:/oracle/product/9206 112 :

[mod-edit] removed illiterate IM speak.

[Updated on: Mon, 15 October 2007 08:08] by Moderator

Report message to a moderator

Re: Rollback in Sql * loader [message #274274 is a reply to message #274259]

Mon, 15 October 2007 05:16

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Connect as the OS oracle instance owner and execute:
$ id
$ groups
$ ps -eaf | grep smon
$ head /arbor1/arbor/Rohit/a.dat

Regards
Michel

Report message to a moderator

Previous Topic:	need to restore dmp file into 9i
Next Topic:	need to load data from different sheets of the same xl file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 15:36:15 CDT 2024