Sure. SQL> set timing on SQL> variable some_string varchar2(100) SQL> begin 2 :some_string := 'Lazy developers will kill me'; 3 end; 4 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.16 SQL> declare 2 dummy varchar2(100); 3 begin 4 for i in 1 .. 50000 5 loop 6 dummy := substr(:some_string, 2, 10); 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.52 SQL> declare 2 dummy varchar2(100); 3 begin 4 for i in 1 .. 50000 5 loop 6 dummy := substring(:some_string, 2, 10); 7 end loop; 8 end; 9 / PL/SQL procedure successfully completed. Elapsed: 00:00:02.52 Apparently ~ 3 times slower.Given the obvious lack of mastery of PL/SQL (I have already met many developers who thought that select into from dual was the only way to assign a value to a variable in PL/SQL), I suspect it was some quick and dirty port from a DBMS that has a substring() function, such as MySQL or Sybase/SQL Server. You should teach your developers grep and sed.
-- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> On 08/31/2011 10:31 PM, Uzzell, Stephan wrote:
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*