This discussion is archived
7 Replies Latest reply: Jan 18, 2013 8:14 AM by Brian Bontrager RSS

column default set does not work at table

592815 Newbie
Currently Being Moderated
Hi Experts,

I created a table have 3 columns. one column is PK and other column set default as 0.
create table test
(
id NUMBER(12) not null,
salary NUMBER(10,2) default 0.00,
hour NUMBER(10,2) default 0.00
);

when user insert value at MS access form. salary and hours are blank. insert processing works well.
But salary and hour column is null. default value 0.00 does not be put into column during inserted data.

We use oracle 11.2 at window 64 bit 2008.
any suggestion?

newdba
  • 1. Re: column default set does not work at table
    sb92075 Guru
    Currently Being Moderated
    Oradb wrote:
    Hi Experts,

    I created a table have 3 columns. one column is PK and other column set default as 0.
    create table test
    (
    id NUMBER(12) not null,
    salary NUMBER(10,2) default 0.00,
    hour NUMBER(10,2) default 0.00
    );

    when user insert value at MS access form. salary and hours are blank. insert processing works well.
    If Insert Processing works well, then why are you here asking about it.
    What does MS Access form have to do with Oracle DB?
    does ID exist as NOT NULL value?

    But salary and hour column is null. default value 0.00 does not be put into column during inserted data.

    We use oracle 11.2 at window 64 bit 2008.
    any suggestion?

    newdba
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: column default set does not work at table
    592815 Newbie
    Currently Being Moderated
    Thanks for your help!
    ID exist at test table but salary and/ or hour column is null if front MS Access form field is blank and pass a null value.
  • 3. Re: column default set does not work at table
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Oradb wrote:
    Thanks for your help!
    ID exist at test table but salary and/ or hour column is null if front MS Access form field is blank and pass a null value.
    Check this
    http://www.techrepublic.com/article/oracle-tip-how-to-use-default-values-with-database-columns/5239302
  • 4. Re: column default set does not work at table
    Justin Cave Oracle ACE
    Currently Being Moderated
    A default value is used if and only if the column is omitted in the INSERT statement. If Access generates a statement
    INSERT INTO test( id, salary, hour )
      VALUES( 1, null, null )
    the default values will be ignored and both SALARY and HOUR will be NULL. If Access generates a statement
    INSERT INTO test( id )
      VALUES( 1 )
    the default values will be used for both SALARY and HOUR. If you are observing the both are NULL, that strongly implies that Access is generating a SQL statement that includes all three columns.

    Someone that is more familiar with Access may be able to tell you whether it is possible for Access to generate a statement like the second one rather than the first one. I would tend to suspect not unless you insert into a view that omits both the SALARY and HOUR columns but doing that likely complicates whatever it is that you're trying to achieve in Access.

    Justin
  • 5. Re: column default set does not work at table
    EdStevens Guru
    Currently Being Moderated
    Oradb wrote:
    Thanks for your help!
    ID exist at test table but salary and/ or hour column is null if front MS Access form field is blank and pass a null value.
    Are you saying Access doesn't display 0.00?

    What do you get if you cut Access out of the picture and select the data from sqlplus?
    select id,
           nvl(salary,'NULL SALARY')
           nvl(hour,'NULL HOUR')
    from test;
  • 6. Re: column default set does not work at table
    592815 Newbie
    Currently Being Moderated
    Thanks for your help.
    Yes. INSERT INTO test( id, salary, hour )
    VALUES( 1, null, null ). the field is null too.

    But developer told me, the user may not be like to enter value in Access form GUI. Developer want to me to set default value in database side.

    Any suggestion? As just said, I can ask developer to put nvl funtion but this way will add additional working for GUL developer.

    "A default value is used if and only if the column is omitted in the INSERT statement" is a good answer for developer
  • 7. Re: column default set does not work at table
    Brian Bontrager Expert
    Currently Being Moderated
    With a quick Google search I found that setting a default value for a form in access is almost trivial. ("ms access form default value"... 1st hit... office.microsoft.com)

    Your business rule appears to be "no entry for salary or hour in the GUI should be stored as 0"

    (My opinions/suggestion)

    Best option... take advantage of the feature of the GUI environment to set the default value. A tiny amount of work for the developer handles the business rule.

    Aggressive option... set the column to NOT NULL if it really must be "0" instead of null. This forces the GUI to pass a valid value. This ensures anyone who maintains the GUI later is aware of the business rule and handles it accordingly. You can still have the database default 0 for cases where those columns are not included in an INSERT.

    Worst option... create a trigger to redefine NULL to 0 for those columns when during an insert or update. This hides the business rule "null should be saved as 0" from the developer, or anyone later modifying the Access GUI. It shifts the work from the original GUI developer to everyone who has to maintain the GUI or the database later.

    Passive option... Ignore or change the business rule, continue to allow nulls, do not bother with defaults in the GUI. Anything that processes the data must add NVL() when handling salary or hour, shifting the work from the GUI developer to everyone else who uses the data.


    It is cheaper in the long run to have the GUI developer spend the small amount of time to get the insert right up front, rather than having multiple people re-handle the business rule after the fact, or add extra complexity in the database via a trigger.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points