Re: Tom, how serious is that problem - ltrim/rtrim changed with regards to NULL'ness

In article <3cc98933$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com says...
>
>
>But Tom how can ltrim or rtrim ('x', null) be null. The doc says
>
>'LTRIM removes characters from the left of char, with all the leftmost
>characters that appear in set removed'
>
>Null is not present in x, how can removing it return null, should it not
>return x. That is unless there is a null in x that is part of varchar2 and
>removing it clears out the value. Barring that souldn't the way that pl/sql
>does it be the correct one?

page 4.2 of the sql ref (816 doc set):

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.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

ltrim/rtrim were always supposed to return NULL - plsql had a bug in that regards.

>
>In article <aabsnm0t7k_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>>In article <3cc9690d$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com
>> says...
>>>
>>>
>>>Thomas, how serious is that? How often do you ltrim/rtrim a column with
>>>nulls? A lot I bet. To me it seems like a very serious problem with possible
>>
>>>widespread ramifications, since it changes in a fundatmental way the way ltrim
>>
>>>and rtrim work
>>>
>>
>>well, maybe -- maybe not. There was ambiguity here:
>>
>>
>>ops$tkyte_at_ORA815.US.ORACLE.COM> select ltrim('x',null) from dual;
>>
>>L
>>-
>>
>>
>>ops$tkyte_at_ORA815.US.ORACLE.COM> exec dbms_output.put_line( ltrim('x',null) )
>>x
>>
>>PL/SQL procedure successfully completed.
>>
>>
>>ops$tkyte_at_ORA815.US.ORACLE.COM> begin
>> 2 for x in ( select ltrim('x',null) a, 'x' b from dual )
>> 3 loop
>> 4 dbms_output.put_line ( 'x.a = ' || x.a );
>> 5 dbms_output.put_line ( 'x.b = ' || ltrim(x.b,null) );
>> 6 end loop;
>> 7 end;
>> 8 /
>>x.a =
>>x.b = x
>>
>>PL/SQL procedure successfully completed.
>>
>>The behavior of LTRIM in plsql *was wrong*.
>>
>>That has been fixed.
>>
>>As with all fixes -- that can sometimes affect you....
>>So,
>>
>>>In article <aaad0l012ie_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com>
>>>wrote:
>>>>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