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

In article <3cc9a0e3$0$3947$45beb828_at_newscene.com>, julio33_at_whomail.com says...
>
>
>Bu tTom, why was the pl/sql behavior the wrong one? doesn't ltrim('x', null)
>equal x or am i misunderstanding something??
>

page 4.2, sql ref from 816:

SQL Functions
SQL functions are built into Oracle and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user functions written in PL/SQL. User functions are described in "User-Defined Functions" on page 4-118. For information about functions used with Oracle interMedia, see Oracle8i interMedia Audio, Image, and Video User?s Guide and Reference.

If you call a SQL function with an argument of a datatype other than the datatype
expected by the SQL function, Oracle implicitly converts the argument to the expected datatype before performing the SQL function. See "Data Conversion" on page 2-36.

If you call a SQL function with a null argument, the SQL function automatically returns null. The only SQL functions that do not necessarily follow this

behavior are CONCAT, NVL, and REPLACE.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

plsql did it wrong, ltrim/rtrim always should have returned NULL given NULL inputs.

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

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