Re: SQL*Loader can a comma delimited file be imported and skip selected fields?

From: Thomas Kyte <>
Date: Sun, 30 May 1999 14:17:23 GMT
Message-ID: <>

A copy of this was sent to "Jeff Boehlert" <> (if that email address didn't require changing) On Fri, 28 May 1999 01:56:44 -0700, you wrote:

>Thank you Tom. I was sure someone had come up with some clever way to do
>this. I am not working in a Unix environmnet so I have opted to try your
>PL/SQL approach. As mentioned my file is comma delimited, optionally
>enclosed with quotes. When I try my file with this approach the parsing
>appears to have a few problems. See details here:
> FIELD1 position(1:4096) "delimited.word(:field1,1,chr(34),chr(44))",
> FIELD2 position(1:1) "delimited.word(:field1,4,chr(34),chr(44))"
>John Jones,"Elm St",Junk,123 Main Street
>Jeff Boehlert,abcdef," 5555","po box, 1848, abc"


>Fields having leading/trailing characters stripped??
>Thanx for your help, once I have this tuned it will be a great help. It sure would be nice if loader allowed us to map an inbound column to the bit bucket!!

It does in release 8.1... Keyword FILLER maps input data fields to the bit bucket.

sorry, i must have posted an older version of my package. Here is the correct one (that works correctly :)

create or replace package delimited

	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 );

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
		return replace( ltrim( rtrim( p_str, p_enc_by ), p_enc_by ), 
							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 )
		l_n			number  default 1;
		l_in_quote	boolean default FALSE;
		l_ch		char(1);
		l_len		number default nvl(length( p_str ),0);
		if ( l_len = 0 ) then
		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;
				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
		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 );
		when no_data_found then return NULL;

end delimited;

See for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
Oracle Service Industries
Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Sun May 30 1999 - 16:17:23 CEST

Original text of this message