Re: ouch
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Wed, 31 Aug 2011 22:45:33 -0500
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
--
//www.freelists.org/webpage/oracle-l
- Follow-Ups:
- RE: ouch
- From: Chitale, Hemant Krishnarao
Other related posts: