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

From: julio <julio33_at_whomail.com>
Date: 25 Apr 2002 12:47:14 -0500
Message-ID: <3cc84067$0$20803$45beb828@newscene.com>

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:

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 Thu Apr 25 2002 - 12:47:14 CDT