Home » SQL & PL/SQL » SQL & PL/SQL » Default values
Default values [message #247233] Mon, 25 June 2007 03:30 Go to next message
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 #247235 is a reply to message #247233] Mon, 25 June 2007 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the control file is?
And the Oracle version is?

Btw, read and follow How to format your posts.

Regards
Michel
Re: Default values [message #247247 is a reply to message #247235] Mon, 25 June 2007 04:28 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Sorry...

database : orcle 10g
I am using a tool called DataStage to load the file to database.

Re: Default values [message #247353 is a reply to message #247233] Mon, 25 June 2007 11:52 Go to previous messageGo to next message
Bill B
Messages: 1482
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #247428 is a reply to message #247233] Mon, 25 June 2007 20:34 Go to previous messageGo to next message
newtooracle123
Messages: 24
Registered: May 2007
Junior Member
Thank u..

DataStage is a ETL tool.
Mike's suggestion worked.

I said I was using SQL Loader in my first post coz i knew very few people will know about the tool DataStage. The functionality I was performing was similar to that of a SQL Loader.
Re: Default values [message #247451 is a reply to message #247428] Mon, 25 June 2007 23:17 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Parallel threads on Deletes
Next Topic: need to merge the records
Goto Forum:
  


Current Time: Sun Dec 04 14:50:16 CST 2016

Total time taken to generate the page: 0.16766 seconds