Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: column with default char not defaulting when inserting a record

Re: column with default char not defaulting when inserting a record

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 06 Aug 2007 11:37:32 -0700
Message-ID: <1186425449.644554@bubbleator.drizzle.com>


Baldwin wrote:

> On Aug 4, 3:02 am, DA Morgan <damor..._at_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..._at_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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Aug 06 2007 - 13:37:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US