Re: ouch

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.freelists.org/webpage/oracle-l Received on Wed Aug 31 2011 - 16:01:20 CDT