Default values [message #247233] |
Mon, 25 June 2007 03:30  |
newtooracle123
Messages: 24 Registered: May 2007
|
Junior Member |
|
|
Hi,
I am having a table where in the date defaults to sysdate and username to 'system'
eg:
CREATE TABLE TEST(
USERNAME VARCHAR2(10) DEFAULT 'SYSTEM' NULL,
CREATE_DATE DATE DEFAULT SYSDATE NULL
)
I have a sql loader which loads the data to this table from a comma seperated file.
something like this
___________________________
username,date of creation
tom,6/6/2006
,
tim,3/7/2006
___________________________
The coloumns are nullable. So if there is a null I want the value to be SYSTEM and SYSDATE.
But something seems to be wrong. This doesnt work.
Is it that oracle doesnt put default values when it encounters a null??
How can I achieve my requirement
Thanks in advance!!!
|
|
|
|
|
Re: Default values [message #247353 is a reply to message #247233] |
Mon, 25 June 2007 11:52   |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
default is only used it you do NOT specify the column on an insert. For example if your insert was
insert into test(create_date) values(sysdate);
Then SYSTEM would be automatically inserted into username. The only way to do what you want to do is with a before insert trigger.
|
|
|
Re: Default values [message #247385 is a reply to message #247233] |
Mon, 25 June 2007 14:17   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | And the control file is?
|
If you use SQL Loader (as you stated in your first post), you could try NULLIF Clause.
By the way I never heard about DataStage (you mentioned by the way later) before. Is it this tool? Maybe you should rather study its documentation or ask in its own forum.
|
|
|
|
Re: Default values [message #247451 is a reply to message #247428] |
Mon, 25 June 2007 23:17  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Similar is not identical.
Does your tool have exactly the same syntax that SQL*Loader?
Does your tool generate the same statements?
Does your tool have direct mode?
Does your tool...
Regards
Michel
|
|
|