Re: SQL Loader

Thomas Kyte wrote:
>
> On Fri, 08 Aug 1997 17:38:53 -0400, Tansel Ozkan <tansel_at_openix.com> wrote:
>
> >Hi everybody,
> >
> >Does anybody out there know if there is a way to specify column numbers
> >in a control file to load from a pipe delimited text file, whose fields
> >are not fixed-length? For example, I want to read 3rd, 5th and 18th
> >columns out of a 30 column file into an oracle table.
> >
> >Thanks..
> >
> >Tansel Ozkan
> >Macrosoft Inc.
>
> You can do it with pl/sql as follows. I will supply you a package you would put
> in the database, and then your control file would look something like:

Thanks a lot Thomas,

But it seems like cutting the fields in a UNIX script and then loading them to ORACLE is an easier solution. The main reason is that the text file is quite big and I would be running into space problems if I load the master table into oracle.

Theoratically your solution is a good one though..

Tansel

>
> LOAD DATA
> INFILE *
> REPLACE
> INTO TABLE DELIMITED_TEST
> (
> FIELD1 position(1:4096) "delimited.word(:field1,1,NULL,chr(9))",
> FIELD2 position(1:1) "delimited.word(:field1,4,NULL,chr(9))"
> )
> BEGINDATA
> John Doe JunkField#1 MoreJunk 123 Main Street
> Mrs. ReallyLongLastName xxx short 345 River Road
>
> ------------ eof ----------------------------------------------
>
> That is, you would use 'positional' notation and have field1 be as big as the
> biggest line. Every other field in the file would be postition(1:1). You would
> then use calls to 'delimited.word' on :field1 to get the i'th word out of the
> whole line. in effect, you have replaced the sqlldr line parser with your own.
>
> My delimited.word routine has the prototype:
>
> function word( p_str in varchar2,
> p_n in varchar2,
> p_enclosed_by in varchar2 default '''',
> p_separated_by in varchar2 default ',' ) return varchar2
>
> So, when you call it, you give the:
>
> - whole line
> - which 'word' you want from it (in the example above, we get words 1 & 4)
> - what might 'enclose' the words (defaults to a single QUOTE. The above example
> shows how to turn off enclosing characters so that Quotes would be considered
> part of the string itself)
> - and what separates fields in the string. CHR(9) is a TAB for example. It
> defaults to a comma.
>
> Since you have the code to the 'parser', you can make it do whatever you want
> now.
>
> create or replace package delimited
> as
> function word( p_str in varchar2,
> p_n in varchar2,
> p_enclosed_by in varchar2 default '''',
> p_separated_by in varchar2 default ',' )
> 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;
>
> g_words vcArray;
> g_empty vcArray;
> g_last_string varchar2(4096);
>
> function de_quote( p_str in varchar2, p_enc_by in varchar2 ) return varchar2
> is
> l_enc_len number default nvl(length(p_enc_by),0);
> l_str_len number default nvl(length(p_str),0);
> l_tmp_str varchar2(200) default
> substr( p_str, l_enc_len+1, l_str_len-2*l_enc_len );
> begin
> return replace( l_tmp_str, p_enc_by||p_enc_by, p_enc_by );
> end de_quote;
>
> procedure parse( p_str in varchar2,
> p_delim in varchar2,
> p_sep in varchar2 )
> is
> l_n number default 1;
> l_in_quote boolean default FALSE;
> l_ch char(1);
> l_len number default nvl(length( p_str ),0);
> begin
> if ( l_len = 0 ) then
> return;
> end if;
>
> g_words := g_empty;
> g_words(1) := NULL;
>
> for i in 1 .. l_len loop
> l_ch := substr( p_str, i, 1 );
> if ( l_ch = p_delim ) then
> l_in_quote := NOT l_in_quote;
> end if;
> if ( l_ch = p_sep AND NOT l_in_quote ) then
> l_n := l_n + 1;
> g_words(l_n) := NULL;
> else
> g_words(l_n) := g_words(l_n)||l_ch;
> end if;
> end loop;
>
> for i in 1 .. l_n loop
> g_words(i) := de_quote( g_words(i), p_delim );
> end loop;
> end parse;
>
> function word( p_str in varchar2,
> p_n in varchar2,
> p_enclosed_by in varchar2 default '''',
> p_separated_by in varchar2 default ',' ) return varchar2
> is
> begin
> if ( g_last_string is NULL or p_str <> g_last_string ) then
> g_last_string := p_str;
> parse( p_str, p_enclosed_by, p_separated_by );
> end if;
> return g_words( p_n );
> end;
>
> end delimited;
> /
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Aug 20 1997 - 00:00:00 CDT