Hi, I've a problem with default value of integer data type: my
software work fine with oracle version 8, but fail with 11g version.
The problem is on sql insert command because are often omitted some
columns for use the default value. If the column omitted has a integer
data type, on oracle 8 the command is succesful executed; viceversa
the command faild on 11g oracle version: the response message return
an unexpected null value on a not-null column. I repeat: Only on
integer data type column.

I suppose that there is a configuration value that is possible to
modified but I don't know what and here.


Thanks
Best Regards

On 29 mrt, 15:32, "web.Hunter" <alberto.cerio...@gmail.com> wrote:
> Hi, I've a problem with default value of integer data type: my
> software work fine with oracle version 8, but fail with 11g version.
> The problem is on sql insert command because are often omitted some
> columns for use the default value. If the column omitted has a integer
> data type, on oracle 8 the command is succesful executed; viceversa
> the command faild on 11g oracle version: the response message return
> an unexpected null value on a not-null column. I repeat: Only on
> integer data type column.
>
> I suppose that there is a configuration value that is possible to
> modified but I don't know what and here.
>
> Thanks
> Best Regards

i'm not sure if i understand your situation correctly. You have a
table in oracle 8 that has a default value on one of the columns, and
the same table without the default value in oracle 11? Or do both
tables have a default value on that column?

select table_name, column_name , data_default from user_tab_columns
where table_name = 'YOURTABLE' ;

should return any default value's on columns. To put a default value
on a column, you have to use the

alter table YOURTABLE modify (....

and the rest you should know or find in the docs

H.

web.Hunter wrote:
> Hi, I've a problem with default value of integer data type: my
> software work fine with oracle version 8, but fail with 11g version.
> The problem is on sql insert command because are often omitted some
> columns for use the default value. If the column omitted has a integer
> data type, on oracle 8 the command is succesful executed; viceversa
> the command faild on 11g oracle version: the response message return
> an unexpected null value on a not-null column. I repeat: Only on
> integer data type column.
>
> I suppose that there is a configuration value that is possible to
> modified but I don't know what and here.
>

If table definitions are equal (especially the
number column - integers are numbers); then this should
work:

insert into a values (col_1, col_2,,col4);

col_3 being you number column:

....
col_3 NUMBER NOT NULL default 42,
col_4 ...

If not, 11G evaluates in a different manner (table definition
against statement) than 8 did (given values, including defaults
on table).
If not, you may have found a a bug. Make sure you test this
against the latest patch version available
--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

On 29 mar, 16:32, "web.Hunter" <alberto.cerio...@gmail.com> wrote:
> Hi, I've a problem with default value of integer data type: my
> software work fine with oracle version 8, but fail with 11g version.
> The problem is on sql insert command because are often omitted some
> columns for use the default value. If the column omitted has a integer
> data type, on oracle 8 the command is succesful executed; viceversa
> the command faild on 11g oracle version: the response message return
> an unexpected null value on a not-null column. I repeat: Only on
> integer data type column.
>
> I suppose that there is a configuration value that is possible to
> modified but I don't know what and here.
>
> Thanks
> Best Regards

Can you reproduce the error when using sqlplus?
If so, please post the output along with the table definitions.

On 29 Mar, 17:15, Helma <helma.vi...@hotmail.com> wrote:
> On 29 mrt, 15:32, "web.Hunter" <alberto.cerio...@gmail.com> wrote:
>
> > Hi, I've a problem with default value of integer data type: my
> > software work fine with oracle version 8, but fail with 11g version.
> > The problem is on sql insert command because are often omitted some
> > columns for use the default value. If the column omitted has a integer
> > data type, on oracle 8 the command is succesful executed; viceversa
> > the command faild on 11g oracle version: the response message return
> > an unexpected null value on a not-null column. I repeat: Only on
> > integer data type column.
>
> > I suppose that there is a configuration value that is possible to
> > modified but I don't know what and here.
>
> > Thanks
> > Best Regards
>
> i'm not sure if i understand your situation correctly. You have a
> table in oracle 8 that has a default value on one of the columns, and
> the same table without the default value in oracle 11? Or do both
> tables have a default value on that column?
>
> select table_name, column_name , data_default from user_tab_columns
> where table_name = 'YOURTABLE' *;
>
> should return any default value's on columns. *To put a default value
> on a column, you have to use the
>
> alter table YOURTABLE modify (....
>
> and the rest you should know or find in the docs
>
> H.

Hi Helma,
thanks for the interest.
my problem is about the different behavior between 8 and 11g oracle
version.
The tables are the same and also the code, but if
I send to db a command without some column the result are differents
p.e.
the tableA have col_3 defined NUMBER NOT NULL default 5
the command is "insert into tableA (col_1,col_2, col_4) values
('a','b',5)"
(I don't have defined the value of column 3)

result
with oracle 8 the commend insert a record
with 11g version I have a error like "unexpected null value on
a not-null column."

This problem is on integer data type column only.

I suppose that there is a configuration value that is possible to
modified but I don't know which and where.

bye

On 30 Mar, 12:19, Frank van Bortel <frank.van.bor...@gmail.com> wrote:
> web.Hunter wrote:
> > Hi, I've a problem with default value of integer data type: my
> > software work fine with oracle version 8, but fail with 11g version.
> > The problem is on sql insert command because are often omitted some
> > columns for use the default value. If the column omitted has a integer
> > data type, on oracle 8 the command is succesful executed; viceversa
> > the command faild on 11g oracle version: the response message return
> > an unexpected null value on a not-null column. I repeat: Only on
> > integer data type column.
>
> > I suppose that there is a configuration value that is possible to
> > modified but I don't know what and here.
>
> If table definitions are equal (especially the
> number column - integers are numbers); then this should
> work:
>
> insert into a values (col_1, col_2,,col4);
>
> col_3 being you number column:
>
> ...
> col_3 * NUMBER NOT NULL default 42,
> col_4 ...
>
> If not, 11G evaluates in a different manner (table definition
> against statement) than 8 did (given values, including defaults
> on table).
> If not, you may have found a a bug. Make sure you test this
> against the latest patch version available
> --
>
> Regards,
> Frank van Bortel
>
> Top-posting in UseNet newsgroups is one way to shut me up

Hi, Frank
I've send more details to Helma

Alberto wrote:

>
> This problem is on integer data type column only.
>
> I suppose that there is a configuration value that is possible to
> modified but I don't know which and where.
>

try this, using SQL*Plus:

create table blah (
col_1 number NOT NULL,
col_2 number NOT NULL,
col_3 number DEFAULT 2 NOT NULL,
col_4 number)
/
insert into blah(col_1, col_2) values (1,2);
select * from blah;

using 10.2.0.3, I got:
SQL> insert into blah(col_1, col_2) values (1,2);

1 row created.

SQL> select * from blah;

COL_1 COL_2 COL_3 COL_4
---------- ---------- ---------- ----------
1 2 2


--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up