Home » RDBMS Server » Server Utilities » sqlldr / external table questions (Any OS/ Oracle 10g and up)
sqlldr / external table questions [message #468893] Mon, 02 August 2010 07:53 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Hi,
These are a few questions which I have come across while loading data in oracle tables. Although, all are doable, is there any elegant way to handle these? Let's discuss

1) Select from an external table. Most of the times the output is in the order the data is in the base file. However, no one can guarentee this order. Suppose the base file has no identity column in specific order existing. How should I use the "order by" clause to guarentee order? I do not want to modifiy the file in the OS level to add an ordered identity column.

2) I want to skip the last 10 records from a file when loading a table using sqlldr or external table. To generalize things, let's say I want to skip record number 3, 5 and 17 from a file. This is doable if #1 is answered Smile

3) "When" clause cannot be used in sqlldr control file when operating on delimiter seperated data. Alternative to "When" is what?

Regards,
Prajjwal
Re: sqlldr / external table questions [message #474519 is a reply to message #468893] Mon, 06 September 2010 16:13 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

1) Select from an external table. Most of the times the output is in the order the data is in the base file. However, no one can guarentee this order. Suppose the base file has no identity column in specific order existing. How should I use the "order by" clause to guarentee order? I do not want to modifiy the file in the OS level to add an ordered identity column.


Although data is not stored in or retrieved in any particular order in tables in the database, I believe external tables are an exception, since they are reading directly from the operating system file. SQL*Loader processes the rows in the order that they are in the operating system file and external tables are really SQL*Loader behind the scenes.

Quote:

2) I want to skip the last 10 records from a file when loading a table using sqlldr or external table. To generalize things, let's say I want to skip record number 3, 5 and 17 from a file. This is doable if #1 is answered :)


In SQL*Loader, you can add a sequence during the load. With external tables, you can add a sequence when inserting from the external table.

Quote:

3) "When" clause cannot be used in sqlldr control file when operating on delimiter seperated data. Alternative to "When" is what?


You can use a when clause in SQL*Loader with delimited data. You just have to reset the position of the first column to 1 after each when clause, by adding position(1) to the first field.





Previous Topic: TRAILING NULLCOLS vs TERMINATED BY....
Next Topic: A DATE datatype question (SQL*Loader)
Goto Forum:
  


Current Time: Thu Mar 28 04:16:59 CDT 2024