Re: SQL*Loader question

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/03/19
Message-ID: <4il3sk$2es_at_inet-nntp-gw-1.us.oracle.com>#1/1

Rama Mohan <mohan_at_mnsinc.com> wrote:

>Gerard H. Pille wrote:
>>
>> In article <4ic0o8$nh9_at_pegasus.rutgers.edu>, Eric G
>> (ericg_at_pegasus.rutgers.edu) says...
>> !>
>> !>I have read the Utilities manual and I can't find the answer.
>> !>
>> !>I am loading a variable length file with delimiters.
>> !>The file has more information than I need to load in my table.
>> !>My question is, how do I skip some of the fields from the file.
>> !>I know how to do it if it were a fixed length file, just use the
>> !positions that you want to use.
>> !>
>> !>Does anyone out there know how to do this?
>> !>
>> !>Thanks for your help.
>> !>
>> !>Sincerely,
>> !>
>> !>Eric Gottesman
>> !>AT&T
>> !>ejgottesman_at_attmail.com
>>
>> SQL*Loader doesn't allow you to skip fields.
>>
>> A very poor solution might be to overload some fields in the same column.
>>
>> --
>> Kind reGards
>> \ / |
>> X |
>> / \ s
>> Gerard
 

>Why dont you try to use a text processing utility like awk or perl to remove
>the unnecessary fields and then load.
>Removing fields will be trivial task in awk
 

>Regards
>Mohan

I do it this way. I have a pl/sql package that can parse the line around quotes and comma's and then can return the i'th 'word' from a line. It caches each line so that it is parsed once per row NOT once per column in a row. The following is my .ctl file:

---

LOAD DATA
INFILE *
REPLACE
INTO TABLE DELIMITED_TEST
(

  FIELD1 position(1:4096) "delimited.word(:field1,1)",
  FIELD3 position(1:4096) "delimited.word(:field1,3)",
  FIELD5 position(1:4096) "delimited.word(:field1,5)"

)
BEGINDATA

"Field1...","Field2...","Field3...","Field4...","Field5...","Field6..."
"xField1...","xField2...","xField3...","xField4...","xField5...","xField6..."
"yField1...","yField2...","yField3...","yField4...","yField5...","yField6..."
-----------------------------------------------------------

And the following is the delimited package:

---

create or replace package delimited
as

        function word( str in varchar2, n in varchar2 )
        return varchar2;
 
        pragma restrict_references( word, WNDS, RNDS );

end;
/  

create or replace package body delimited as

        type vcArray is table of varchar2(2000) index by binary_integer;  

        words           vcArray;
        last_string     varchar2(4096);
 
        procedure parse
        is
                temp    varchar2(4096) default last_string || ',';
                n               number;
        begin
                for i in 1 .. 10000 loop
                        exit when ( temp is NULL );
                        n := instr( temp, ',' );
                        words(i) := rtrim( ltrim( substr( temp, 1, n-1 ), '"' );
                        temp := substr( temp, n+1 );
                end loop;
        end parse;
 
        function word( str in varchar2, n in varchar2 ) return varchar2
        is
        begin
                if ( last_string is NULL or str <> last_string ) then
                        last_string := str;
                        parse;
                end if;
                return words( n );
        end;
 

end delimited;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government

---

opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Tue Mar 19 1996 - 00:00:00 CET