Automating error-checking script

[Ken011]

Dear all,

I have gotten away from Oracle for too long that it almost all I know have left me.

I got back to an oracle this past Thursday with an Oracle project.

The business rule species that any field name that doesn't have a value should be zero-filled.

There are a total of 10 tables and slightly over 100 field names. Of these field names, 16 are optional fieldnames. The rest are required fieldnames.

First, the most logical thing to do, I think, would have been to create the tables with default values like:

CREATE Table T1
(
charcol char default '0000',
intcol integer default 0000
)

This way, if any fieldname is left blank, the default value of that fieldname becomes the value.

This, I was told, didn't happen.

The next thing, I am thinking would be to write a script that assign a value of 000 or '000' depending on the data type.

My problem is that I have gotten away from Oracle for almost 5 years that I don't know where to begin.

Can someone, please present any helpful ideas.

Thank you so very much

 

[dbalearner]

Hi Ken,

One way (not necessarily the best optimised way) is to write a trigger for each table and assign the relevant values for these fileds for each table. That is for insert or update. That should work. For 10 tables it should be easy to manage.

The second option is to update these columns for all tables and then ALTER TABLE MODIFY COLUMN DEFAULT <DEFAULT_NAME>, for each relevant column of the table, so new rows will have default values.

Hope this helps

 

[Ken011]

thanks for the respond.

We don't have priviledges to update or modify these tables.

That is the weirdest thing I have heard.

 

[hoinz]

Hi,

what do you mean by
This, I was told, didn't happen. ??
Which part of your expectations was not met?

One possible problem I see here is the default of 0000 for an numeric column.
This will always become 0, just one 0.
(There is no numeric difference between 0 an 0000, is it?)

If you want to see 0000, you will have to adjust your output format, not your default value.

regards

 

[taupirho]

I agree with hoinz - your output needs to be changed. Something like:


select decode(c1,null,'0000',c1) from t1

 

[Beilstwh]

Do not use a decode, use a nvl. Nvl is designed for this specific task.

taupirho's method
select decode(c1,null,'0000',c1) from t1

Using nvl
select nvl(c1,'0000') from t1;

Bill
Oracle DBA/Developer
New York State, USA

 

[Ken011]

mates,

thanks very much for your response.

hoinz, what I meant by the following statement:

This, I was told, didn't happen. ??

was that during table creation, a default value should have been set for any of those optional field names but they didn't do that. If they did, we wouldn't have had to do this now.

And yes, I agree with you that:

there is no numeric difference between 0 an 0000 and I wasn't clear enough. I should have said a default of just 0 would have been fine.

I asked the same question as to why couldn't we just modify the db to fix this but they were told not to. go figure.

I need to write a simple annonymous script or just sql that prompts you to enter begin date and end date.

Thanks all for your assistance.

The script will pass 3 fieldnames as input param.

The way they want it done is to select from the tables fields that have null values, add zero values to them and insert them into another table and make this table available to them.

 

[hoinz]

Ken011,

thanks for clarification; I think I understand better now.
And I am afraid we will not be able to convince your boss.

Regarding the task to select from the tables fields that have null values, add zero values to them and insert them into another table :
I would use nvl() for it, like Beilstwh suggested.
Something like this:

insert into new_table
select nvl(charcol,'0000'), nvl(intcol,0)
from old_table
where charcol is NULL or intcol is NULL

hope this helps

 

[taupirho]

Beilstwh,

Given the original spec NVL would not work as required with a numeric column

ie NVL(intcol,'0000') => returns 0 NOT 0000

Thats why I used DECODE

 

[Ken011]

You guys have been great.

Thanks much. I will work with the info you guys have provided.

I will post results no matter what the results are.

Sorry for all the late responses-juggling 2 jobs.