Home » RDBMS Server » Server Utilities » Control file to search and skip rows (Oracle 10 g/Windows XP)
Control file to search and skip rows [message #349202] Fri, 19 September 2008 05:25 Go to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi All,
I have written a control file that reads data from a text file and uploads it in a table in database.
But the problem I am facing is that the text file that I used to upload data contains queries also which I need to skip to be able to upload the data. My objective is to upload data in the database without having to open the text file and counting how many rows to skip.
Is there any way by which I could find out the occurence of the name of the first column and start uploading data from the next row into my table?I need to acheive this because the text file I am uploading the data from is quite large and its not possible to open it every time and count and skip rows.
Any help would be appreciated.
Thanks a lot in advance.
Re: Control file to search and skip rows [message #349210 is a reply to message #349202] Fri, 19 September 2008 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See WHEN clause.

Regards
Michel
Re: Control file to search and skip rows [message #349228 is a reply to message #349210] Fri, 19 September 2008 06:51 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi Michel,
I saw the when clause but I guess it is ued for comparing the value of a column while loading the data,whereas what I am looking for is that I want to find out the occurence of the name of the first column in the text file and start uploading data from the subsequent rows.
In my case I want to skip all the rows that appear before the occurrence of the name of the first column but the number of rows that occur before my first column appears are variable ,otherwise I could have simply mentioned rows to skip in my control file.
Thanks and Regards.

[Updated on: Fri, 19 September 2008 06:53]

Report message to a moderator

Re: Control file to search and skip rows [message #349274 is a reply to message #349228] Fri, 19 September 2008 10:39 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Ideally it would be best to obtain a text file without the query that produced the data included. If the data was spooled from an Oracle source, then this can be accomplished by setting echo off before spooling. Similar things can be done in other languages. If you have no control over this and are stuck with it, then you need to identify something that can be used in the WHEN clause, so that Oracle can determine if this is a row to be loaded or not. For example if each line of your query begins with SELECT or FROM or WHERE or AND and so on and no such word would be valid as the first word of the first column of valid data, then you might be able to use something like "WHEN first_col <> 'SELECT' AND first_col <> 'FROM' ...". If any such rows would not meet the criteria for loading, then they would get rejected anyhow. If you still don't understand, then please post a sample data file and sample control file, and create table statement for the table that you want to load into.
Previous Topic: Create a External Table in Oracle 10g:==> ERROR: KUP-01005
Next Topic: IMPORT from file
Goto Forum:
  


Current Time: Fri Dec 09 23:07:50 CST 2016

Total time taken to generate the page: 0.17001 seconds