1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to insert Default value if inserted value is null /?

Discussion in 'SQL PL/SQL' started by Vicky, May 20, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Frndz..

    If anyone try to insert 'NULL' into a column(which is nullable), I want to replace that one with a default value(assigned during table creation)...Is it possible to achieve that Without using triggers?!~

    I saw some options like "default on null" in Oracle 12c., But, Unfortunately.. mine is Oracle 10g...:(
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    if not to use the trigger, then it is possible to use default value or in through procedure which the insertion is carried out to use the necessary processing.
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The answer to the question you asked is 'No'. Prior to 12c, there is no way to create a table definition such that a DEFAULT value gets inserted into a column in cases where a NULL value is explicitly inserted into that column.

    Unless there is some reason not to use one, a trigger is generally the solution for this. Apparently you have a reason you don't want to use a trigger, but without indicating what that reason is, making an alternate suggestion is difficult.
     
  4. Sateesh.chandra90

    Sateesh.chandra90 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Simple while inserting values use nvl(columnname,0) or nvl(columname,null) .

    Insert into emp(empid,name,salary) values select empid,nvl(name,null),salary from temp;
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Sateesh.chandra90,

    Its not about handling nulls using NVL in statement level...Its about default constraint handling nulls @ table level....
     
  6. nilesh.dudhane

    nilesh.dudhane Active Member

    Messages:
    42
    Likes Received:
    2
    Trophy Points:
    135
    Location:
    India
    Hi Vicky

    Try following at the time of table creation
    CREATE TABLE TABLE NAME
    (COLUMN_NAME DATATYPE DEFAULT 'YOUR_VALUE');

    Thanks
    Nilesh
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Nilesh,

    The create statement using default can be useful, only if u're leaving a column while inserting and for existing records when U're newly created a column...

    Mine is different.. I'm seeking a way to handle with default values when null is been inserted into a column where default values has been already assigned during table creation itself......
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If someone has overridden your default value assignment with an explicit NULL there's nothing you can do at insert time to fix that outside of using a trigger unless you're using 12.1.0.x where you can create a 'default on null' constraint:


    Code (SQL):

    SQL> CREATE TABLE nulldflt(
      2          mynum   NUMBER DEFAULT ON NULL 0,
      3          mydata  varchar2(40));


    TABLE created.


    SQL>
    SQL> INSERT INTO nulldflt
      2  VALUES(NULL, 'Pingo pongo flopnorfer');


    1 ROW created.


    SQL>
    SQL> SELECT * FROM nulldflt;


         MYNUM MYDATA
    ---------- ----------------------------------------
             0 Pingo pongo flopnorfer


    SQL>
     

    In lower releases you need a before insert trigger to check the inserted value and see if it is NULL, then change it:


    Code (SQL):

    SQL> CREATE TABLE nulldflt(
      2          mynum   NUMBER DEFAULT 1,
      3          mydata  varchar2(40));


    TABLE created.


    SQL>
    SQL> CREATE TRIGGER null_on_insrt_trg
      2  BEFORE INSERT ON nulldflt
      3  FOR each ROW
      4  BEGIN
      5          IF :NEW.mynum IS NULL THEN
      6                  :NEW.mynum := 0;
      7          END IF;
      8  END;
      9  /


    TRIGGER created.


    SQL>
    SQL> INSERT INTO nulldflt
      2  VALUES(NULL, 'Pingo pongo flopnorfer');


    1 ROW created.


    SQL>
    SQL> SELECT * FROM nulldflt;


         MYNUM MYDATA
    ---------- ----------------------------------------
             0 Pingo pongo flopnorfer


    SQL>
    SQL> INSERT INTO nulldflt(mydata)
      2  VALUES('Pingo pongo flopnorfer');


    1 ROW created.


    SQL>
    SQL> SELECT * FROM nulldflt;


         MYNUM MYDATA
    ---------- ----------------------------------------
             0 Pingo pongo flopnorfer
             1 Pingo pongo flopnorfer


    SQL>
     

    Notice the default value is used if the column is removed from the insert list.
     
    Vicky likes this.
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes David.. I do understand it can be accomplished using triggers... But, team rejected trigger idea,...
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You really have no other choice other than upgrade to 12.1.0.2. The team needs to realize this.
     
  11. dazyparker

    dazyparker Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    When a user create a database then you have an option to specify the DEFAULT value. It helps to insulate database design issues from application code.You can change the default value of a column at some later date with a single ALTER TABLE command and application code will immediately start using the new values.
    Check the following link techrepublic.com/article/oracle-tip-how-to-use-default-values-with-database-columns/[/url]
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please read he ENTIRE thread before posting such responses. Had you done so you would see the original poster has implemented default values correctly.