Re: ouch
Date: Thu, 01 Sep 2011 11:15:19 -0500
Message-ID: <4E5FAF97.5050606_at_ardentperf.com>
On 9/1/2011 1:31 AM, Chitale, Hemant Krishnarao wrote:
> 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.
I think we're unnecessarily vilifying the developers. Almost for sure, this is an application that was written for a different platform (maybe MSSQL) and perhaps even still runs primarily on that platform. Probably, somebody somewhere just decided they wanted to run this MSSQL application on Oracle.
We already established that the ideal solution is to go through all the code and update every occurrence of substring with substr. But I'm pointing out that it would be infinitely useful to have a better interim solution, if one is available.
At a minimum, I believe that substr is available as a built-in pl/sql function. I don't have a test system handy at the moment, but it seems to me that you should be able to do something like this:
FUNCTION "SUBSTRING"
(INSTRING IN varchar2,
STARTPOS IN number,
LENGTH IN number)
RETURN varchar2 IS
BEGIN
RETURN(SUBSTR(INSTRING,STARTPOS,LENGTH));
END;
Obviously this is not ideal, but the developers are *already* doing
whatever's possible to replace occurrences of this function with calls
to substr (assuming it's not a packaged 3rd party app). Does this
actually work like I think it would? Is there an even better approach?
The obvious benefit of something like this is that the O.P. could snap this function into his database with far less testing than 200 SQL statement changes, and see instant improvement in whatever business problem the function is contributing to. Then he can evaluate the cost and benefit of the SQL statement updates, and as long as it's worth the time investment, he can move forward with updating those.
-Jeremy
-- http://www.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago -- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 01 2011 - 11:15:19 CDT