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

On Fri, 24 Jan 2003 17:53:08 +0300, "Deloni" <ply_at_chat.ru> 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.

Just off the top of my head, I'd look at building views, using the NVL function to translate the nulls to whatever else you want. Have never done this, so don't know if it would serve your purpose, but it might be worth exploring. Received on Fri Jan 24 2003 - 14:59:21 CST