Re: ouch
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: "Chitale, Hemant Krishnarao" <Hemant.Chitale@xxxxxx>
- Date: Thu, 01 Sep 2011 11:15:19 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
