3 Replies Latest reply on Jun 22, 2010 8:47 AM by 460049

    character to number conversion error

    767114
      hi
      am trying to upload csv file to oracle database using apex when i select the file using file browser and click on the button.when i upload the value of type number it gives the error:ORA-06502: PL/SQL: numeric or value error: character to number conversion error

      please give me the solution for this

      i need to write a pl/sql process in apex.
      can u tell me what exactly i shoud mension for uploading
      my table looks like

      coloumn type

      col1 number(2)
      col2 number(2)
      col3 number(2)
      col4 number(2)

      please tell me the steps i need to follow

      urgent requirement
        • 1. Re: character to number conversion error
          460049
          try using this code to remove some characters at the end of the input values.

          regexp_replace(:1, ''[[:cntrl:]]'', null))
          • 2. Re: character to number conversion error
            510477
            A couple of things you may run into:
            Conversion restrictions: when converting from numeric to character datatypes (and vice-versa) the implicit conversion is not always available. In your data load procedure, I recommend explicitly casting ALL non-text fields from character into their appropriate datatypes.

            Definitional restrictions: you may be trying to insert a numeric value that is too big for the specified precision+scale of your number datatype. You may have to round, etc. in order to fix this.

            Date restrictions: you can also get an error complaining about a numerical violation when dealing with dates. Make sure you are explicitly converting the dates according to the specified date format model.
            • 3. Re: character to number conversion error
              460049
              Or how about just putting everything in a table with varchar columns only. Then try one of these

              i) run a insert into orig_table select col1, cols2..collast from varchar_table

              ii) select to_number(col1) from varchar_table; test all the number columns with this and you will soon find which column has issues.

              I had the same error and I found that the last column was coming with a carriage return so I replaced all non printable characters on the last column.