Re: SQL Loader

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/11
Message-ID: <33f230bc.7259047@newshost>#1/1

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:

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 Mon Aug 11 1997 - 00:00:00 CDT