Re: Porting to Oracle - problem with empty strings/nulls

>>>>> "DA" == DA Morgan <damorgan_at_exesolutions.com> writes:

 DA> Deloni wrote:
>> Hello guys,
>>
>> I need and advice on how to turn NULLs into empty strings back.
>>
>> We have a legacy system (standalone desktop application) written
>> in Smalltalk that uses MS Access via ODBC as the back-end. Our
>> management decided to port the application to Oracle 9.2 (partly
>> due to marketing reasons, partly for further enhancement).
>>
>> I have created a DB on Oracle and converted all the data from the
>> Access DB. Unfortunately, it is well-known that Oracle treats
>> empty strings as NULLs, and our legacy applications is somewhat
>> poorly designed - it doesn't have a separate persistent layer but
>> a lots of SQLs spreaded all over the code instead. What is the
>> worst, there are many places in the code where a string fetched
>> from the DB is checked if it is empty, so the application became
>> totally out of order. There is absolutely impossible for us to
>> change all these SQLs and equality checks in the code, so we need
>> a solution 'in general'.
>>
>> Could you please suggest any solution (if such exists :-) how to
>> get empty strings instead of NULLs as results of SQLs in Oracle?
>> May be you know of an Oracle ODBC driver providing such a feature?
>> Could I achieve the goal, for example, by using triggers or views
>> ore any other Oracle features?
>>
>> Thanks in advance. -- Best regards, Deloni, SPb, Russia

 DA> NVL(NULL, '')

 DA> But likely this is related to your background in MS Access and is
 DA> more a product of your trying to kludge Oracle into being a
 DA> Access than it is learning Oracle Concepts and Architecture and
 DA> taking advantage of the product's strengths.

 DA> You can do it. But then lots of people do things they shouldn't.

 DA> It would be better if you asked how to deal with a business  DA> problem than suggesting a solution that is, at best, bad design.

 DA> Daniel Morgan

I think Daniel is 100% on the money here. From reading the OPs post, I don't think the nvl(NULL, '') solution is what he is after because he doesn't want to change the sql in their application. However, I don't think there is any way of doing what the OP wants without changing the SQL. More importantly, I suspect that even if a solution is found which avoids changing the SQL of the application, other problems will be revealed simply because the SQL will not be taking advantage of Oracle features which will make the application work well.

While it might seem like changing the sql of the application is too large a task to deal with or too complex or whatever, I think it is very likely that you will end up getting the application ported, but spending a lot more time identifying and solving lots of little performance problems (and possibly just straight out bugs). You are likely to end up spending months tweaking Oracle parameters all over the place only to have minimal impact at the end of the day because all the sql is dodgy or inefficient. It really is false economy to believe you can save time porting to another dbms by not examining how the application does its SQL and being prepared to re-write it. I also suspect, given that you are coming from an MS Access backend, that you will be able to drastically reduce the amount of SQL in your application it you take advantage of triggers, stored procedures, inline views etc.

Porting should be a lot more than just changing ODBC drivers and moving your tables and data to a different dbms.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!