Re: Howto do undo a default clause?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 1 Aug 2003 12:16:24 +0100
Message-ID: <3f2a4c09$0$18493$ed9e5944@reading.news.pipex.net>

"Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:3f2a40b9$0$31922$afc38c87_at_news.optusnet.com.au...
> "Tanel Poder" <tanel@@peldik.com> wrote in message
news:3f2a37c2$1_1_at_news.estpak.ee...
> >
> > Quite interesting issue, though ;)
> >
>
>
> Darn! I was afraid someone would say that...
> The problem I have is that we run a script
> that reads all this stuff and creates a XML
> schema from the info in dictionary.
>
> If there is nothing in default value, nothing
> gets into XML. Then the Java generator that
> creates the code for the DAOs for the system
> objects does the right thing.
>
> If there is something in there, even if it is
> the string 'NULL', it creates a <DEFAULT_VALUE>
> </DEFAULT_VALUE> tag. This in turn causes the
> code generator to throw a wobblie fit when it
> shoves the 'NULL' string into a NUMBER field!

I don't think this should happen see below.

SQL> desc work_table;

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 ID                                                 NUMBER
 DATA_CONTENT                                       VARCHAR2(50)

SQL> alter table work_table add noons number default 0;

Table altered.

a table with a number column 0 default zero.

SQL> desc work_table;

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 ID                                                 NUMBER
 DATA_CONTENT                                       VARCHAR2(50)
 NOONS                                              NUMBER

SQL> insert into work_table (id,data_content) values(1,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from work_table;

        ID DATA_CONTENT                                            NOONS
---------- -------------------------------------------------- ----------
         1 test                                                        0

it works ta da and not a single typo yet.

SQL> alter table work_table modify noons default null;

Table altered.

SQL> conn / as sysdba
Connected.
SQL> select default$
  2 from obj$,col$
  3 where obj$.obj#=col$.obj#
  4 and col$.name='NOONS';

DEFAULT$

null

there is the string null in col$

SQL> select obj$.obj#,default$
  2 from obj$,col$
  3 where obj$.obj#=col$.obj#
  4 and col$.name='NOONS';

OBJ#

DEFAULT$

28844
null

SQL> select default$ from col$ where obj#=28844;

DEFAULT$

null

and is different to columns with null undefined.

SQL> conn batch/batch
Connected.
SQL> insert into work_table(id,data
2 .

oops theres the typo :(

SQL> insert into work_table(id,data_content) values(2,'more stuff');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from work_table;

        ID DATA_CONTENT                                            NOONS
---------- -------------------------------------------------- ----------
         1 test                                                        0
         2 more stuff

you don't get a string null you get a real null value. I'd suggest that if the value null is being converted to the string 'NULL' by the java generator then this is where it is going wrong. (assuming it is actually going wrong).

NB this all is very similar to the asktom link dave posted. I think that what I learn from this is that in fact the default value of a column isn't ever really a default value in the sense of a constant, it is should be really thought of as the return value of a function - even if that function always returns the same result (like 0).

cheers

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Fri Aug 01 2003 - 06:16:24 CDT