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

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

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

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

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

Daniel Morgan Received on Fri Jan 24 2003 - 14:24:31 CST