Re: SQL - PL/SQL Question. Please Help!!

I guess that an explanation for my need to do this is in order.

Before converting our data over to Oracle my company was running an old legacy system utilizing DataFlex Code utilizing data stored in DataFlex Tables.

We were able to utilize a 3rd party utility that allows us to retain our 1000+ DataFlex code files and use data converted over to Oracle. The DataFlex language does not recognize Null values. This is a major problem now with our code as the 3rd party utility that converted our data over to Oracle converted all empty fields not used in any index as Null. There is nothing in the DataFlex code language that will allow us to recognize or substitue a Null value; therefore, the solution for us right now is to convert ALL Null values (VarChar2, Date, and Number) over to Non-Null values such as ' ' for VarChar2 Null values, 0 for Number Null values, and TO_DATE("01-01-0001","MM-DD-YYYY") for Date Null values.

Screwy situation I know!!!! The only other solution would be to scrap the DataFlex Code Language and rewrite our entire system!! Management says "NO WAY"!!! I am the only programmer here and that would take at least ........ well, you get the picture.

I have come up with a solution to convert all the Null values that will allow me to monitor it as it is happening. Now I am attempting to manage the the Rollback Segments that are failing to extend due to the massive transactions that are happening due to this change. Fun, Fun, Fun!!!!!

I hope that this sheds a little light on the "WHY" of it all!!!

Having DBA fun in Tehachapi, California

George - SSI

Kenneth C Stahl wrote in message <37690B57.52E58031_at_lucent.com>...
>Not sure why you would want to do this. It isn't a very good use of your
>database and you'll waste space - especially with the ' ' in varchar2
fields.
>
>Why not just build a view that uses nvl() for the columns that you want to
>modify. That way your actual data stays pure, but the presention comes out
the
>way that you want.
>
>for example, say you create a table like this:
>
>create mytable (abc number(5);
> def varchar2(5));
>
>then, if you want to see null values in abc as 0 and null values in def as
' ',
>your view would be defined:
>
>create or replace view myview as
>select nvl(abc,0) abc,
> nvl(dev,' ') def;
>
>
>
>Ken
>
>"George Gifford
>
>> Does anyone have any suggestions or pl/sql code that will accept a
tablename
>> and then update/replace ALL Null Numeric fields with a 0, and all Null
>> VarChar2 Fields with a space (' ')??
>>
>> Or a PL/SQL Block that will run through each record in a table and update
>> specific fields with a default value???
>>
>> Any help would be greatly appreciated!!!
>>
>> George - SSI
>
Received on Thu Jun 17 1999 - 12:12:34 CDT