Home » RDBMS Server » Server Utilities » Rollback in Sql * loader
Rollback in Sql * loader [message #274188] Sun, 14 October 2007 23:16 Go to next message
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.
Re: Rollback in Sql * loader [message #274198 is a reply to message #274188] Mon, 15 October 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Rollback in Sql * loader [message #274213 is a reply to message #274198] Mon, 15 October 2007 02:00 Go to previous messageGo to next message
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 252 times)
Re: Rollback in Sql * loader [message #274215 is a reply to message #274213] Mon, 15 October 2007 02:03 Go to previous messageGo to next message
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 199 times)
Re: Rollback in Sql * loader [message #274227 is a reply to message #274213] Mon, 15 October 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Rollback in Sql * loader [message #274229 is a reply to message #274227] Mon, 15 October 2007 02:50 Go to previous messageGo to next message
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 258 times)
Re: Rollback in Sql * loader [message #274234 is a reply to message #274229] Mon, 15 October 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Rollback in Sql * loader [message #274246 is a reply to message #274229] Mon, 15 October 2007 03:59 Go to previous messageGo to next message
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 202 times)
Re: Rollback in Sql * loader [message #274254 is a reply to message #274246] Mon, 15 October 2007 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: Rollback in Sql * loader [message #274259 is a reply to message #274246] Mon, 15 October 2007 04:32 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64103
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
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:
  


Current Time: Sun Dec 04 22:51:03 CST 2016

Total time taken to generate the page: 0.09545 seconds