Re: Unnecessary Fields in SQL*Loader?

A copy of this was sent to spam_at_meagain.com (Mark M.) (if that email address didn't require changing) On Wed, 28 Jul 1999 13:29:27 GMT, you wrote:

>I'm attempting to write a .CTL file SQL*Loader and have hit a road
>block.
>
>How do I handle fields in a variable length input file that I do not
>need?
>
>For example:
>
>BIDR_IDNTFCTN_NBR CHAR(10) TERMINATED BY '|',
>JUNK CHAR(5) TERMINATED BY '|',
>RQN_ID CHAR(15) TERMINATED BY '|',
>
>Suppose I had the above, with the field identified as JUNK not being
>necessary for the table I am trying to insert to. What would be an
>appropriate method of making SQL*Loader bypass this field?
>The problem seems to lie in the fact that the input is variable
>length, and I cannot specify a POSITION parameter for RQN_ID since I
>do not know whether there will be a value in JUNK or not.
>
>Does anyone know of any workarounds, or solutions that I just have not
>thought of.
>
>Much appreciated,
>
>-Mark M.

sqlldr with Oracle8i, release 8.1 can do this with the FILLER keyword.

before that, sqlldr by itself cannot do this with delimited data. If you are using UNIX, using "cut" on the file and piping the results of this into a named pipe (and having sqlldr read that pipe) is pretty efficient. For example:

$ mknod sqlldr.dat p
$ cut -f3- -d, < test.dat > sqlldr.dat & [1] 4946

$ sqlldr userid=tkyte/tkyte control=sqlldr.ctl data=sqlldr.dat

would just let sqlldr have at columns 3 on in the file test.dat (separated by commas)...

Or, for a solution that works in all releases/all platforms see

http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=483755757&fmt=text

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 28 1999 - 09:37:36 CDT