Re: Can someome run this in oracle 9i for me and tell if it returns results

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 25 Apr 2002 19:11:33 -0700
Message-ID: <aaad0l012ie@drn.newsguy.com>

In article <3cc85ad7$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com says...
>
>
>please!
>
>we have a procedure that runs fine in 8i but not in 9i. Should work in both.
>can someone run in 9i 9.0.1.0.0
>

Interesting, the behavior of this

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;

differes in 817 from 815 (started before 9i) IF p_enc_by is NULL.

Suggested fix:

                if ( p_delim is NOT NULL )
                then
                    for i in 1 .. l_n loop
                        g_words(i) := de_quote( g_words(i), p_delim );
                    end loop;
                end if;

in the procedure PARSE. That'll fix it.

Looks like ltrim/rtrim changed with regards to NULL'ness

ops$tkyte_at_ORA815.US.ORACLE.COM> exec dbms_output.put_line( 'ltrim = ' || ltrim('x',null) );
ltrim = x

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'ltrim = ' || ltrim('x',null) );
ltrim =

PL/SQL procedure successfully completed.

and that is the underlying cause.

>
>here is the statment that will run it
>
> select delimited.word('number1_at_number2@number3@', 1, '', '@')
>, delimited.word('number1_at_number2@number3@', 2, '', '@') from dual
>
>
>here is the source
>
>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 

Received on Thu Apr 25 2002 - 21:11:33 CDT