Board index » delphi » Null Values vs Defaulkt Column Value (ORACLE)

Null Values vs Defaulkt Column Value (ORACLE)

hi, i'm using ODAC to access to a Oracle 8i database under D5.

i have a table that has about 20 fields, all of the fields have a default
value and NOT NULL flag on creation,
but when i try to insert a new record and don't fill all the fields i get an
error message saying that are fields that need to have a value!
shouldn't default value automaticaly fill in those fields?

Pedro Baptista
Local Informtico

 

Re:Null Values vs Defaulkt Column Value (ORACLE)


I'm not sure about Oracle, but I know with SQL Server, default values ONLY
come into play when the INSERT statement omits the column completely.  For
example

create table test (
  col1 int not null default 10,
  col2 int not null)

1) insert into test (col1) values (1)
2) insert into test (col2) values (1)
3) insert into test (col1, col2) values (null, 1)

Query 1 results in an error because col2 was omitted and no default is
available
Query 2 works because col1 was omitted but the default took care of that
Query 3 fails because col1 does not allow nulls.  the default does NOT
substitute for the literal null value.

When the query components write updates back to your database, they don't
omit the empty columns from the INSERT statement, they pass a statement that
looks like Query #3 above.

--
Steve
http://www.krell-software.com
Database Development Tools for MS SQL Server

Quote
"Pedro Baptista" <Ridl...@netcabo.pt> wrote in message

news:3c063d79$1_2@dnews...
Quote
> hi, i'm using ODAC to access to a Oracle 8i database under D5.

> i have a table that has about 20 fields, all of the fields have a default
> value and NOT NULL flag on creation,
> but when i try to insert a new record and don't fill all the fields i get
an
> error message saying that are fields that need to have a value!
> shouldn't default value automaticaly fill in those fields?

> Pedro Baptista
> Local Informtico

Re:Null Values vs Defaulkt Column Value (ORACLE)


I'm not sure about Oracle, but I know with SQL Server, default values ONLY
come into play when the INSERT statement omits the column completely.  For
example

create table test (
  col1 int not null default 10,
  col2 int not null)

1) insert into test (col1) values (1)
2) insert into test (col2) values (1)
3) insert into test (col1, col2) values (null, 1)

Query 1 results in an error because col2 was omitted and no default is
available
Query 2 works because col1 was omitted but the default took care of that
Query 3 fails because col1 does not allow nulls.  the default does NOT
substitute for the literal null value.

When the query components write updates back to your database, they don't
omit the empty columns from the INSERT statement, they pass a statement that
looks like Query #3 above.

--
Steve
http://www.krell-software.com
Database Development Tools for MS SQL Server

Quote
"Pedro Baptista" <Ridl...@netcabo.pt> wrote in message

news:3c063d79$1_2@dnews...
Quote
> hi, i'm using ODAC to access to a Oracle 8i database under D5.

> i have a table that has about 20 fields, all of the fields have a default
> value and NOT NULL flag on creation,
> but when i try to insert a new record and don't fill all the fields i get
an
> error message saying that are fields that need to have a value!
> shouldn't default value automaticaly fill in those fields?

> Pedro Baptista
> Local Informtico

Re:Null Values vs Defaulkt Column Value (ORACLE)


The answer Steve gave also applies to Oracle/ODAC: The default is used only
if you omit the column from the insert sql.

Quote
"Pedro Baptista" <Ridl...@netcabo.pt> wrote in message

news:3c063d79$1_2@dnews...
Quote
> hi, i'm using ODAC to access to a Oracle 8i database under D5.

> i have a table that has about 20 fields, all of the fields have a default
> value and NOT NULL flag on creation,
> but when i try to insert a new record and don't fill all the fields i get
an
> error message saying that are fields that need to have a value!
> shouldn't default value automaticaly fill in those fields?

> Pedro Baptista
> Local Informtico

Re:Null Values vs Defaulkt Column Value (ORACLE)


Could you try to set it to some value? Like 123. DB would it if there's an
on-insert autonumber triggers. Problem may arrise when you insert many records
at once, because of ID is unique, then you could use some hand-writen
ID-generator, or simply use value of current time as Integer.

Quote
Pedro Baptista wrote:
> hi, i'm using ODAC to access to a Oracle 8i database under D5.

> i have a table that has about 20 fields, all of the fields have a default
> value and NOT NULL flag on creation,
> but when i try to insert a new record and don't fill all the fields i get an
> error message saying that are fields that need to have a value!
> shouldn't default value automaticaly fill in those fields?

> Pedro Baptista
> Local Informtico

--
Dejan Marjanovic

TWC
Freiburg, Germany

E-Mail: de...@twc.de

Other Threads