9 Replies Latest reply on Feb 11, 2010 8:12 AM by Pradeep Gupta

    ORA-01438 error while inserting a record.

    Pradeep Gupta
      Hi ,

      Below is my table desc.

      SQL> desc IEE_TEST
      Name Null? Type
      ----------------------------------------------------- -------- ------------------------------------
      OPERATING_COMPANY VARCHAR2(10)
      METER_MULTIPLIER NUMBER(12,4)
      PULSE_MULTIPLIER NUMBER(7,9)
      ACCOUNTCODE_TYPEID VARCHAR2(10)
      METER_TIMEZONE VARCHAR2(20)
      RECORDINGDEVICE_TIMEZONE VARCHAR2(20)
      CHANGESTODST


      and when i try to insert a new record it get ORA error ORA-01438 sometime in strange way.

      SQL> insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y');
      insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y')
      *
      ERROR at line 1:
      ORA-01438: value larger than specified precision allows for this column


      SQL> insert into IEE_TEST values('NSP_MN',,300,.000300000,'BI','Central','US','Y');

      1 row created.

      Even in both precision digits are 9 but I noticed 1 thing, if 1 and 2 digit (i.e .300300000 or .030300000) greater than 0 then it throws error.

      Please assist me in this.
        • 1. Re: ORA-01438 error while inserting a record.
          Avinash Tripathi
          Hi,
          Please post your database version and table creation script.

          SQL> insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y');
          insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y')
          *
          ERROR at line 1:
          ORA-01438: value larger than specified precision allows for this column


          SQL> insert into IEE_TEST values('NSP_MN',,300,.000300000,'BI','Central','US','Y');

          1 row created.
          Your above two statements are not same. I can see two commas before 300, it means values are going in two different columns rather than one.


          values('NSP_MN',300,.300300000,'BI','Central','US','Y');
          values('NSP_MN',,300,.000300000,'BI','Central','US','Y');



          Cheers,
          Avinash

          Edited by: Avinash Tripathi on Feb 11, 2010 12:18 PM

          Edited by: Avinash Tripathi on Feb 11, 2010 12:18 PM
          • 2. Re: ORA-01438 error while inserting a record.
            659537
            create table IEE_TEST(
            OPERATING_COMPANY VARCHAR2(10),
            METER_MULTIPLIER NUMBER(12,4),
            PULSE_MULTIPLIER NUMBER(9,9),
            ACCOUNTCODE_TYPEID VARCHAR2(10),
            METER_TIMEZONE VARCHAR2(20),
            RECORDINGDEVICE_TIMEZONE VARCHAR2(20),
            CHANGESTODST  varchar2(1))
            
            
            insert into IEE_TEST values('NSP_MN',300.00,.300300000,'BI','Central','US','Y');
            From documentation :
            You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, then Oracle returns an error message
            [http://download-uk.oracle.com/docs/cd/A97630_01/server.920/a96540/sql_elements2a.htm]




            pls check this?

            Edited by: Kapil Aradhye on 10-Feb-2010 23:02
            • 3. Re: ORA-01438 error while inserting a record.
              Pradeep Gupta
              table creation script is

              create table IEE_TEST(
              OPERATING_COMPANY VARCHAR2(10),
              METER_MULTIPLIER NUMBER(12,4),
              PULSE_MULTIPLIER NUMBER(7,9),
              ACCOUNTCODE_TYPEID VARCHAR2(10),
              METER_TIMEZONE VARCHAR2(20),
              RECORDINGDEVICE_TIMEZONE VARCHAR2(20),
              CHANGESTODST varchar2(1));

              And ignore that 2 comma before 300 that was added by mistake.

              SQL> insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y');
              insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y')
              *
              ERROR at line 1:
              ORA-01438: value larger than specified precision allows for this column


              SQL> insert into IEE_TEST values('NSP_MN',300,.000300000,'BI','Central','US','Y');

              1 row created.


              And oracle version is 9.2.0.8.0

              Thanks

              Edited by: user4486815 on Feb 10, 2010 11:41 PM
              • 4. Re: ORA-01438 error while inserting a record.
                730428
                The number precision must be specified as follows:
                NUMBER(P,S)
                P is the precision, that is to say the total number of significative digits (both integer part and decimal part),
                S is the scale, the number of decimal digits

                P has never to be less than S.

                If you want a number column that can store numbers up to 9999999.999999999 you have to declare it this way:
                PULSE_MULTIPLIER NUMBER(16,9),
                Max
                [My Italian Oracle blog| http://oracleitalia.wordpress.com/2010/02/07/aggiornare-una-tabella-con-listruzione-merge/]
                1 person found this helpful
                • 5. Re: ORA-01438 error while inserting a record.
                  Pradeep Gupta
                  table creation script is

                  create table IEE_TEST(
                  OPERATING_COMPANY VARCHAR2(10),
                  METER_MULTIPLIER NUMBER(12,4),
                  PULSE_MULTIPLIER NUMBER(7,9),
                  ACCOUNTCODE_TYPEID VARCHAR2(10),
                  METER_TIMEZONE VARCHAR2(20),
                  RECORDINGDEVICE_TIMEZONE VARCHAR2(20),
                  CHANGESTODST varchar2(1));

                  And ignore that 2 comma before 300 that was added by mistake.

                  SQL> insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y');
                  insert into IEE_TEST values('NSP_MN',300,.300300000,'BI','Central','US','Y')
                  *
                  ERROR at line 1:
                  ORA-01438: value larger than specified precision allows for this column

                  SQL> insert into IEE_TEST values('NSP_MN',300,.000300000,'BI','Central','US','Y');

                  1 row created.

                  And oracle version is 9.2.0.8.0

                  Thanks
                  • 6. Re: ORA-01438 error while inserting a record.
                    Pradeep Gupta
                    Thanks Max, it really works,

                    if i defile PULSE_MULTIPLIER as NUMBER(9,9) then it doesn't give error anymore.
                    • 7. Re: ORA-01438 error while inserting a record.
                      659537
                      Why PULSE_MULTIPLIER NUMBER(7,9) is defined in this way ? This is not a good practice.

                      Check Max or my earlier post in this thread.
                      • 8. Re: ORA-01438 error while inserting a record.
                        730428
                        Please mark as answered.

                        max
                        • 9. Re: ORA-01438 error while inserting a record.
                          Pradeep Gupta
                          Kapil , it was defined already and I have little idea about sql.

                          Anyway thnx guys . I have marked it answered!!