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

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

--
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 Corp