1

I am trying to create an Oracle stored procedure that simply inserts a row into a table. There is a column in the table (MEAS_IND) that is type char(1 byte) and cannot be null. When I try to insert any one character into the column/table I get the error: 

ORA-01481: invalid number format model
ORA-06512: at "IFDC.PKG_FIELD_ASSISTANT", line 326
ORA-06512: at line 3
01481. 00000 -  "invalid number format model"
*Cause:    The user is attempting to either convert a number to a string
           via TO_CHAR or a string to a number via TO_NUMBER and has
           supplied an invalid number format model parameter.
*Action:   Consult your manual.

I have tried inserting ' ', 'N' and to_char(' '). There are other columns that cannot be null and seem to work fine. When I comment out MEAS_IND I get the error that it cannot be null, so I am assuming that the other columns are fine. Here is the code:

INSERT INTO myTable
          (
            cont_id
            , cms_uid
            , dwr_dt
            , prj_nbr
            , ln_itm_nbr
            , loc_seq_nbr
            , loc_instld
            , vend_id
            , rpt_qty
            , itm_cd
            , last_modfd_uid
            , last_modfd_dt
            , catg_nbr
            , rmrks_id, plan_pg_ref_nbr, ref_doc, fr_sta_itm, fr_sta_dstnc, fr_sta_offst_t, fr_sta_offst_dstnc
            , to_sta_itm, to_sta_dstnc, to_sta_offst_t, to_sta_offst_dstnc, meas_ind
          )
       VALUES
          (
            pContractId
            , pUSER_ID
            , pDRW_DATE
            , pProject_num
            , pPLN
            , vMaxLocSeq
            , pLocation
            , pVend_id
            , pAmount
            , pItemCode
            , pUSER_ID
            , to_number(sysdate, 'YYYYMMDD')
            , vCatigoryNumber
            , ' ', ' ', ' ', ' ', 0, ' ', 0
            , ' ', 0, ' ', 0, ' '
          ) ;

Thanks in advance

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
indianapolymath
  • 323
  • 1
  • 4
  • 15

1 Answers1

5

Instead of to_number(sysdate, 'YYYYMMDD'), do this:

to_number(to_char(sysdate, 'YYYYMMDD'))

Better yet, if it's not too late in the project then change the last_modfd_dt column to a DATE type. The DATE type will include the date and the time of day (hours, minutes, seconds but no fractional seconds). If you just want the date without the time, set it as trunc(sysdate).

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69