Re: ouch

IMHO, nobody has answered the obvious question though... if there is a
requirement to have a "substring()" fuction until the day when all
queries are updated, then what's the right way to do it?  Can you just
create it as a public alias pointing to substr?  Is there any way to do
this which preserves all features of using the substr function itself?
-Jeremy

On 8/31/2011 4:01 PM, Tim Gorman wrote:
> Stephan,
>
> If you want to demonstrate the utter wrongness of this, try...
>
> SQL> set timing on
> SQL> declare
>   2       v_str varchar2(1000);
>   3  begin
>   4       for i in 1..100000 loop
>   5              v_str := substring('This stinks',5,1);
>   6       end loop;
>   7  end;
>   8  /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:03.34
> SQL>
> SQL> declare
>   2       v_str varchar2(1000);
>   3  begin
>   4       for i in 1..100000 loop
>   5              v_str := substr('This stinks',5,1);
>   6       end loop;
>   7  end;
>   8  /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.02
>
> Gosh, after seeing that, I had to go rinse my brain out... blech!
>
> Hope this helps...
>
> -Tim
>
>     -----Original Message-----
>     *From:* Uzzell, Stephan [mailto:SUzzell@xxxxxxxxxx]
>     *Sent:* Wednesday, August 31, 2011 02:31 PM
>     *To:* 'Oracle L'
>     *Subject:* ouch
>
>     Found a gem in one of my databases today:
>
>      
>
>     FUNCTION                        "SUBSTRING"
>
>     (INSTRING IN varchar2,
>
>     STARTPOS IN number,
>
>     LENGTH   IN number)
>
>     RETURN varchar2
>
>     IS
>
>       RESULT varchar2(8000);
>
>     BEGIN
>
>     select substr(INSTRING, STARTPOS, LENGTH)
>
>     into RESULT
>
>     from DUAL;
>
>     RETURN(RESULT);
>
>     END;
>
>      
>
>     I?m offended by the sheer aesthetics of this. And I want to go
>     yell at the developers that this is wrong. But I?d need a better
>     argument than that it offends me. Is there some way to quantify
>     the impact (if any) of wrapping a built-in function like this?
>
>      
>
>     *Stephan Uzzell*
>
>      
>


-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago



--
http://www.freelists.org/webpage/oracle-l


Other related posts: