Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Unanswered: Default Values not being set

    I have a table where I've defined various fields to have a default value when nothing is supplied by the end user.
    I'm using oracle 10.2 with SQL Developer as a front end to manage my databse...

    When i use my custom application to save a record to the database, all these fields that are supposed to have a default value are saved as NULL instead.

    I also tried changing the setting "cannot be null" to TRUE for these fields, just in case that has an impact. But when I try to save a record with this setting, I get the error:
    ORA-01400: cannot insert NULL into ("WTDB_D"."WTB"."TESTFIELD1")
    where TESTFIELD1 is the field i have specified "Cannot be null" and I've also specified that I want a default value of "111"

    Can you point me in the right direction?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by chubbypama
    I have a table where I've defined various fields to have a default value when nothing is supplied by the end user.
    How? Even SQL Developer is able to re-generate the CREATE TABLE scripts in the "SQL" tab (=the last one) to post it here (when the table is large, only relevant columns are satisfactory).
    Quote Originally Posted by chubbypama
    When i use my custom application to save a record to the database, all these fields that are supposed to have a default value are saved as NULL instead.
    Depends on which INSERT statement does that "custom application" issue. "Not supplying anything" means that the given column is not involved in the INSERT statement at all (so only list of all non-default columns with their values is specified). Are you sure that "custom application" really issues this? If so, prove it.

  3. #3
    Join Date
    Jul 2007
    Posts
    54

    thanks for the response...

    So... here's a part of my sql to create the table in question:
    CREATE TABLE "WTDB_D"."WTB"
    Code:
       (	"InterviewDate" DATE NOT NULL ENABLE, 
    	"BNP7" NUMBER(*,0) DEFAULT 101, 
    	"BNP6" NUMBER(*,0) DEFAULT 101, 
    	"BNP5" NUMBER(*,0) DEFAULT 101, 
    	"BNP4" NUMBER(*,0) DEFAULT 101, 
    	"BNP3" NUMBER(*,0) DEFAULT 101, 
    	"BNP2" NUMBER(*,0) DEFAULT 101, 
    	"BNP1" NUMBER(*,0) DEFAULT 101, 
    	"NDI10" NUMBER(*,0) DEFAULT 101, 
    	"NDI9" NUMBER(*,0) DEFAULT 101, 
    	"NDI8" NUMBER(*,0) DEFAULT 101, 
    etc..
    As far as your other comment about the insert statement, you've given me some food for thought. I used the drag / drop method in vb forms to create my database connection and adapters... so I'll have to review the insert statement that's being executed.
    So i guess you're saying that I need to alter the statement to ensure that every field is always named in the insert statement? What do I supply as values to ensure that the 101 is inserted?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What do I supply as values to ensure that the 101 is inserted?
    101 perhaps?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2007
    Posts
    54
    Quote Originally Posted by anacedent
    >What do I supply as values to ensure that the 101 is inserted?
    101 perhaps?
    I guess I phrased my question incorrectly. I thought the idea of having a default value was that you didn't have to supply all the various field / column names that aren't explicitly set by the end user.
    If that's not the case, how is the Default Value intended to be used?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chubbypama
    I guess I phrased my question incorrectly. I thought the idea of having a default value was that you didn't have to supply all the various field / column names that aren't explicitly set by the end user.
    That's true, but only if you omit the column in the INSERT statement.

    If you explicitely pass a null value for the column, no default will be applied (because you asked to store a null value).

    The following insert will try to store a null value in col2:
    Code:
    INSERT INTO my_table (col1, col2) VALUES (42, null)
    The following insert will apply any default value to col2:
    Code:
    INSERT INTO my_table (col1) VALUES (42)

  7. #7
    Join Date
    Jul 2007
    Posts
    54

    default insert statement generated by wizard

    i created my data entry form via the wizard - aka. i dropped a table adapter etc on to my form and it created an insert statment for me based on my select.
    so does this mean then that i need to manually check each field that doesn't contain a value and either include it in the insert statement with 999 as the value, or exclude the column name and value to let oracle assign 999?
    That's going to be a lot of work! I have over 200 columns / fields in this table.

    do you have any other suggestions or tips?
    I guess one other option might be to set the value of every field to 999 on the form, when the user creates a new record.
    ???

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> create table DEFAULT_VALS (
      2  ID   NUMBER NOT NULL,
      3  VAL1 NUMBER DEFAULT -1,
      4  VAL2 NUMBER DEFAULT -2);
    Table created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(1,NULL,1);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(2,2,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(3,3,3);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(4,NULL,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL1) VALUES(5,5);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL2) VALUES(6,6);
    1 row created.
    SQL> SELECT * FROM DEFAULT_VALS;
    	ID	 VAL1	    VAL2
    ---------- ---------- ----------
    	 1		       1
    	 2	    2
    	 3	    3	       3
    	 4
    	 5	    5	      -2
    	 6	   -1	       6
    6 rows selected.
    What problem are you trying to solve?
    Last edited by anacedent; 06-08-09 at 19:26.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2007
    Posts
    54
    Quote Originally Posted by anacedent
    Code:
    SQL> create table DEFAULT_VALS (
      2  ID   NUMBER NOT NULL,
      3  VAL1 NUMBER DEFAULT -1,
      4  VAL2 NUMBER DEFAULT -2);
    Table created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(1,NULL,1);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(2,2,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(3,3,3);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS VALUES(4,NULL,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL1) VALUES(5,5);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL2) VALUES(6,6);
    1 row created.
    SQL> SELECT * FROM DEFAULT_VALS;
    	ID	 VAL1	    VAL2
    ---------- ---------- ----------
    	 1		       1
    	 2	    2
    	 3	    3	       3
    	 4
    	 5	    5	      -2
    	 6	   -1	       6
    6 rows selected.
    What problem are you trying to solve?
    ID 5 and 6 is what I'm trying to accomplish. But the INSERT statement that was auto generated for me explicitly lists all column names and presum ably supplies a value as well.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As you can see, if/when a value is provided, the value goes into the column.
    Code:
    SQL> create table DEFAULT_VALS (
      2  ID   NUMBER NOT NULL,
      3  VAL1 NUMBER DEFAULT -1,
      4  VAL2 NUMBER DEFAULT -2);
    Table created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(1,NULL,1);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(2,2,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(3,3,3);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(4,NULL,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL1) VALUES(5,5);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL2) VALUES(6,6);
    1 row created.
    SQL> SELECT * FROM DEFAULT_VALS;
    	ID	 VAL1	    VAL2
    ---------- ---------- ----------
    	 1		       1
    	 2	    2
    	 3	    3	       3
    	 4
    	 5	    5	      -2
    	 6	   -1	       6
    6 rows selected.
    When a column is not specified, the DEFAULT value is used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jul 2007
    Posts
    54

    thanks

    Quote Originally Posted by anacedent
    As you can see, if/when a value is provided, the value goes into the column.
    Code:
    SQL> create table DEFAULT_VALS (
      2  ID   NUMBER NOT NULL,
      3  VAL1 NUMBER DEFAULT -1,
      4  VAL2 NUMBER DEFAULT -2);
    Table created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(1,NULL,1);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(2,2,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(3,3,3);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID, VAL1, VAL2) VALUES(4,NULL,NULL);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL1) VALUES(5,5);
    1 row created.
    SQL> INSERT INTO DEFAULT_VALS (ID,VAL2) VALUES(6,6);
    1 row created.
    SQL> SELECT * FROM DEFAULT_VALS;
    	ID	 VAL1	    VAL2
    ---------- ---------- ----------
    	 1		       1
    	 2	    2
    	 3	    3	       3
    	 4
    	 5	    5	      -2
    	 6	   -1	       6
    6 rows selected.
    When a column is not specified, the DEFAULT value is used.
    Yes, I got that from your previous post. I guess I was hoping you'd answer my question about modifying the auto generated insert statement. Is there any way to accomplish this without checking every field to see if it has been changed? Right now, the system keeps track of what's been changed in the dataset - I don't have to worry about that at all. However, if now I have to keep track of what hasn't been set by the user, I do have to check each field one by one. That's going to be very cumbersome as I have over 200+ fields.
    ??

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What automagical software "keeps track of what's been changed in the dataset"
    You may have "designed" yourself into an ugly situation.

    200+ columns in a table???
    My initial reaction is that I suspect improvements might be obtained by following Third Normal Form table design rules.

    >That's going to be very cumbersome
    Those who live by the GUI, die by the GUI.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Jul 2007
    Posts
    54
    don't worry about it, i'll ask around / check around somewhere else.
    Thanks anyways.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chubbypama
    But the INSERT statement that was auto generated for me explicitly lists all column names and presum ably supplies a value as well.
    Then the problem is with the part of the software that auto-generated the INSERT statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •