Hi,

I got this very interesting issue. I configured a column of a table
defaulted to 'A'. Whenever I insert a record into the table without
specifying a value in that column, the column is not defaulted to 'A'.
I am able to default other char column without any problem. But for
this one column, for some reason, it is not defaulting to 'A'. My
table got 34 columns. There are foreign keys but not set to the column
with the issue. I got a lot of triggers for this table, but i tried
disabling them and inserted a record and still didn't work.


Here is the example. the table and column names are not the actual
names.
-----------
Here are my sql statements.

insert into mytable(id, name)
values(33, 'testme')

select id, name, status
from mytable
where name = 'testme'
-----------
Here is the table definition.

create table mytable
(
id NUMBER(10) not null,
name VARCHAR2(60) not null,
status CHAR(1) default 'A'
)
-----------


Any help is greatly appreciated. thanks
Baldwin

On Aug 3, 3:16 pm, Baldwin <ibaldwi...@gmail.com> wrote:
> Hi,
>
> I got this very interesting issue. I configured a column of a table
> defaulted to 'A'. Whenever I insert a record into the table without
> specifying a value in that column, the column is not defaulted to 'A'.
> I am able to default other char column without any problem. But for
> this one column, for some reason, it is not defaulting to 'A'. My
> table got 34 columns. There are foreign keys but not set to the column
> with the issue. I got a lot of triggers for this table, but i tried
> disabling them and inserted a record and still didn't work.
>
> Here is the example. the table and column names are not the actual
> names.
> -----------
> Here are my sql statements.
>
> insert into mytable(id, name)
> values(33, 'testme')
>
> select id, name, status
> from mytable
> where name = 'testme'
> -----------
> Here is the table definition.
>
> create table mytable
> (
> id NUMBER(10) not null,
> name VARCHAR2(60) not null,
> status CHAR(1) default 'A'
> )
> -----------
>
> Any help is greatly appreciated. thanks
> Baldwin

Gee, which release of Oracle are you using? In 10.2.0.2 it works
without issue:

SQL> create table mytable
2 (
3 id NUMBER(10) not null,
4 name VARCHAR2(60) not null,
5 status CHAR(1) default 'A'
6 )
7 /

Table created.

SQL> insert into mytable(id, name)
2 values(33, 'testme')
3 /

1 row created.

SQL> select *
2 from mytable;

ID
NAME S
----------
------------------------------------------------------------ -
33
testme A

When you can let us know which release of Oracle this is we can help
you further.


David Fitzjarrell

Baldwin wrote:
> Hi,
>
> I got this very interesting issue. I configured a column of a table
> defaulted to 'A'. Whenever I insert a record into the table without
> specifying a value in that column, the column is not defaulted to 'A'.
> I am able to default other char column without any problem. But for
> this one column, for some reason, it is not defaulting to 'A'. My
> table got 34 columns. There are foreign keys but not set to the column
> with the issue. I got a lot of triggers for this table, but i tried
> disabling them and inserted a record and still didn't work.
>
>
> Here is the example. the table and column names are not the actual
> names.
> -----------
> Here are my sql statements.
>
> insert into mytable(id, name)
> values(33, 'testme')
>
> select id, name, status
> from mytable
> where name = 'testme'
> -----------
> Here is the table definition.
>
> create table mytable
> (
> id NUMBER(10) not null,
> name VARCHAR2(60) not null,
> status CHAR(1) default 'A'
> )
> -----------
>
>
> Any help is greatly appreciated. thanks
> Baldwin

No issue in any version of 10g or 11g on Windows or Linux (Oracle or
RedHat).

SQL> create table mytable (
2 id NUMBER(10) not null,
3 name VARCHAR2(60) not null,
4 status CHAR(1) default 'A');

Table created.

SQL>
SQL> insert into mytable
2 (id, name)
3 values
4 (33, 'testme');

1 row created.

SQL> SELECT * FROM mytable;

ID NAME S
--------- --------- -
33 testme A

SQL>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Aug 4, 3:02 am, DA Morgan <damor...@psoug.org> wrote:
> Baldwin wrote:
> > Hi,
>
> > I got this very interesting issue. I configured a column of a table
> > defaulted to 'A'. Whenever I insert a record into the table without
> > specifying a value in that column, the column is not defaulted to 'A'.
> > I am able to default other char column without any problem. But for
> > this one column, for some reason, it is not defaulting to 'A'. My
> > table got 34 columns. There are foreign keys but not set to the column
> > with the issue. I got a lot of triggers for this table, but i tried
> > disabling them and inserted a record and still didn't work.
>
> > Here is the example. the table and column names are not the actual
> > names.
> > -----------
> > Here are my sql statements.
>
> > insert into mytable(id, name)
> > values(33, 'testme')
>
> > select id, name, status
> > from mytable
> > where name = 'testme'
> > -----------
> > Here is the table definition.
>
> > create table mytable
> > (
> > id NUMBER(10) not null,
> > name VARCHAR2(60) not null,
> > status CHAR(1) default 'A'
> > )
> > -----------
>
> > Any help is greatly appreciated. thanks
> > Baldwin
>
> No issue in any version of 10g or 11g on Windows or Linux (Oracle or
> RedHat).
>
> SQL> create table mytable (
> 2 id NUMBER(10) not null,
> 3 name VARCHAR2(60) not null,
> 4 status CHAR(1) default 'A');
>
> Table created.
>
> SQL>
> SQL> insert into mytable
> 2 (id, name)
> 3 values
> 4 (33, 'testme');
>
> 1 row created.
>
> SQL> SELECT * FROM mytable;
>
> ID NAME S
> --------- --------- -
> 33 testme A
>
> SQL>
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

We are using 10g. We dont have any problem in other columns with
default values except for this one column. Actually this is our issue
in our two tables.

We also tried to insert and explicitly define the value like :

insert into mytable(id, name, status)
values(33, 'testme', 'A')

And it doesnt work.

But if you update the column like:

update mytable
set status = 'A'
where id = 33

And it completely worked.

Baldwin wrote:
> On Aug 4, 3:02 am, DA Morgan <damor...@psoug.org> wrote:
>> Baldwin wrote:
>>> Hi,
>>> I got this very interesting issue. I configured a column of a table
>>> defaulted to 'A'. Whenever I insert a record into the table without
>>> specifying a value in that column, the column is not defaulted to 'A'.
>>> I am able to default other char column without any problem. But for
>>> this one column, for some reason, it is not defaulting to 'A'. My
>>> table got 34 columns. There are foreign keys but not set to the column
>>> with the issue. I got a lot of triggers for this table, but i tried
>>> disabling them and inserted a record and still didn't work.
>>> Here is the example. the table and column names are not the actual
>>> names.
>>> -----------
>>> Here are my sql statements.
>>> insert into mytable(id, name)
>>> values(33, 'testme')
>>> select id, name, status
>>> from mytable
>>> where name = 'testme'
>>> -----------
>>> Here is the table definition.
>>> create table mytable
>>> (
>>> id NUMBER(10) not null,
>>> name VARCHAR2(60) not null,
>>> status CHAR(1) default 'A'
>>> )
>>> -----------
>>> Any help is greatly appreciated. thanks
>>> Baldwin
>> No issue in any version of 10g or 11g on Windows or Linux (Oracle or
>> RedHat).
>>
>> SQL> create table mytable (
>> 2 id NUMBER(10) not null,
>> 3 name VARCHAR2(60) not null,
>> 4 status CHAR(1) default 'A');
>>
>> Table created.
>>
>> SQL>
>> SQL> insert into mytable
>> 2 (id, name)
>> 3 values
>> 4 (33, 'testme');
>>
>> 1 row created.
>>
>> SQL> SELECT * FROM mytable;
>>
>> ID NAME S
>> --------- --------- -
>> 33 testme A
>>
>> SQL>
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
>
> We are using 10g. We dont have any problem in other columns with
> default values except for this one column. Actually this is our issue
> in our two tables.
>
> We also tried to insert and explicitly define the value like :
>
> insert into mytable(id, name, status)
> values(33, 'testme', 'A')
>
> And it doesnt work.
>
> But if you update the column like:
>
> update mytable
> set status = 'A'
> where id = 33
>
> And it completely worked.

If you explicitly state it is isn't working you are just covering for
it. Do this and post the results:

set long 1000000
SELECT dbms_metadata.get_ddl('TABLE', '<your_table_name') FROM dual;
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org