Re: ouch

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Wed, 31 Aug 2011 22:45:33 -0500
Message-ID: <4E5EFFDD.4090503_at_ardentperf.com>

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_at_MICROS.COM]
> *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

Received on Wed Aug 31 2011 - 22:45:33 CDT