Skip to Main Content
  • Questions

  • Configuring a SQL Loader control File to exclude the second row

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gav.

Asked: January 10, 2018 - 4:19 pm UTC

Last updated: January 15, 2018 - 9:52 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,

I am trying to configure a control file that excludes the second line of data from the load. The system is automated and I have been tasked to see if there is a solution to this. I am very new at this. I have been told about a discard file of lines that I want to exclude, but I have not found any information on how to do this by line number, is this even possible?

Many thanks in advanced.

Gav

and Connor said...

Easiest way is with simple OS redirection, eg

$ cat myfile.dat
I am line 1
I am line 2
I am line 3
I am line 4
I am line 5
I am line 6

$ gawk '{if ( NR != 2 ) {print}}' myfile.dat
I am line 1
I am line 3
I am line 4
I am line 5
I am line 6

$ mknod sqlldr.dat p 
$ gawk '{if ( NR != 2 ) {print}}' myfile.dat > sqlldr.dat & 
[1] 4946
$ sqlldr userid=x/y control=sqlldr.ctl data=sqlldr.dat


and you're good to go.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gav Cheal, January 15, 2018 - 9:20 am UTC

Many Thanks for you answer. I agree that this works. I was wondering if this could be done within the control file rather than doing it pre-process.

Many Thanks
Connor McDonald
January 15, 2018 - 9:52 am UTC

I'd be more inclined to remap the requirement using an external table so I have the full power of SQL at my disposal.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.