Re: ouch

From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 01 Sep 2011 09:07:49 -0600
Message-ID: <4E5F9FC5.3010405_at_evdbt.com>

Hemant,
It is not true that every piece of code using "substring()" has to be rewritten. Having the custom function simply RETURN SUBSTR() and eliminate the SELECT from DUAL is sufficient...

    SQL> create FUNCTION substring(str IN varchar2, pos IN number, len     IN number)

       2          RETURN varchar2
       3  IS
       4  BEGIN
       5          return substr(str, pos, len);
       6  END substring;
       7  /

    Function created.

    Elapsed: 00:00:00.28
    SQL> show errors
    No errors.
    SQL>
    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:00.11
    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

As Andy demonstrated yesterday, it is likely that native compilation will make the performance of this workaround even more acceptable.

By the way, the reason the developer wrote the SELECT from DUAL in the first place is because T-SQL (i.e. SQL Server's procedural language) permits variable assignment using a SELECT clause without a FROM clause. The developer probably thought it was no big deal to add FROM DUAL clause, rather than bothering to learn how variable assignments are done in PL/SQL.

Hope this helps...

Thanks!

-Tim

On 9/1/2011 12:31 AM, Chitale, Hemant Krishnarao wrote:
>> Can you just create it as a public alias pointing to substr?
> Unfortunately, NO, that cannot be done.
>
> So every piece of code that uses "substring()" has to be modified. Hopefully, this might be just a few stored procedures. However, more likely, it might be many separate SQL statements, not all of them in script files. Developers would continue writing code that calls substring() until they are .... taught .... to do the right thing. Users might be using "substring()" in adhoc queries.
>
>
> Hemant K Chitale
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeremy Schneider
> Sent: Thursday, September 01, 2011 11:46 AM
> To: tim_at_evdbt.com
> Cc: SUzzell_at_MICROS.COM; Oracle L
> Subject: 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
>
>
> This email and any attachments are confidential and may also be privileged. If you are not the addressee, do not disclose, copy, circulate or in any other way use or rely on the information contained in this email or any attachments. If received in error, notify the sender immediately and delete this email and any attachments from your system. Emails cannot be guaranteed to be secure or error free as the message and any attachments could be intercepted, corrupted, lost, delayed, incomplete or amended. Standard Chartered PLC and its subsidiaries do not accept liability for damage caused by this email or any attachments and may monitor email traffic.
>
> Standard Chartered PLC is incorporated in England with limited liability under company number 966425 and has its registered office at 1 Aldermanbury Square, London, EC2V 7SB.
>
> Standard Chartered Bank ("SCB") is incorporated in England with limited liability by Royal Charter 1853, under reference ZC18. The Principal Office of SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the United Kingdom, SCB is authorised and regulated by the Financial Services Authority under FSA register number 114276.
>
> If you are receiving this email from SCB outside the UK, please click http://www.standardchartered.com/global/email_disclaimer.html to refer to the information on other jurisdictions.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

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

Received on Thu Sep 01 2011 - 10:07:49 CDT