Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tom Kyte procedure that works in 8i will not work in 9i any ideas?
In article <3cc84067$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com says...
>
>I have been using a procedure
>(http://osi.oracle.com/~tkyte/SkipCols/delimited.sql)
>that Thomas Kyte wrote. It parses strings into columns by giving it the
>string, the delimiter and the enclosed by(optional) . it worked fine in Oracle
>8i 8.1.6 but in 9i it does nothing returns null
>
>the troubling thing is that the procedure is VERY simple, just a couple of
>arrays, some for loops and some substr, some ltrim and rtrim and that is about
>it. We have gone over every line and there is NO reason it should not work,
>it is very troubling since if such a simple procedure does not work what about
>a more complex one?!
>
>system is Oracle 9.0.1.0.0
>on Sun 10k box running Sun 2.6
>
>
>here it is:
>
>
>
cannot repro on:
DELIMITED.WORD('THIS,IS,A,TEST',1)
this
DELIMITED.WORD('THIS,IS,A,TEST',2)
is
DELIMITED.WORD('THIS,IS,A,TEST',3)
a
DELIMITED.WORD('THIS,IS,A,TEST',4)
test
tkyte_at_TKYTE9I.US.ORACLE.COM> select * from v$version;
BANNER
I would defintely encourage you to patch up from 9.0.1.0 tho before proceeding!
>
>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
> begin
> 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 )
> 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 );
> exception
> when no_data_found then return NULL;
> end;
>
>end delimited;
>/
>
>
>
>
-- Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Thu Apr 25 2002 - 20:56:34 CDT