Re: Tom Kyte procedure that works in 8i will not work in 9i any ideas?

Try with a null enclosed by. As you noted Tom in a the theard ( Can someome run this in oracle 9i for me and tell if it returns results), it seems ltrim and rtrim deal with nulls differently in 817 and 9i.

In article <aaac4i010rl_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>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
>----------------------------------------------------------------
>Oracle9i Enterprise Edition Release 9.0.1.0.0 - Beta
>PL/SQL Release 9.0.1.0.0 - Beta
>CORE 9.0.1.0.0 Production
>TNS for 32-bit Windows: Version 9.0.1.0.0 - Production
>NLSRTL Version 9.0.1.0.0 - Production
>
>
>
>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;
>>/
>>
>>
>>
>>
>
Received on Fri Apr 26 2002 - 10:05:27 CDT