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

Bu tTom, why was the pl/sql behavior the wrong one? doesn't ltrim('x', null) equal x or am i misunderstanding something??

In article <aabscs0sfn_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>In article <3cc96c21$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com
> says...
>>
>>
>>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.
>>
>
>yes, in PLSQL only -- the plsql behavior differed from the sql behavior and
> they
>fixed that.
>
>
>>
>>
>>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 - 13:51:06 CDT