1 2 Previous Next 17 Replies Latest reply: Nov 26, 2012 12:48 PM by ranit B RSS

    ORA-01465: invalid hex number

    969730
      Hello

      I have simple table and one column type raw I have simple insert query but not working

      INSERT INTO xDESCRIPTION (errorcode,XDESCRIPTION) VALUES ( '0x0002','Test'); and I see this error ORA-01465: invalid hex number
        • 1. Re: ORA-01465: invalid hex number
          Manik
          Check this:
          select rawtohex('0X0002') from dual;
          Cheers,
          Manik.
          • 2. Re: ORA-01465: invalid hex number
            969730
            select is working but insert not
            • 3. Re: ORA-01465: invalid hex number
              Manik
              Try inserting this way as your column data type is RAW.
              select hextoraw(rawtohex('0X0002')) from dual;
              Cheers,
              Manik.
              • 4. Re: ORA-01465: invalid hex number
                Purvesh K
                966727 wrote:
                select is working but insert not
                Without Table Structures, we are helpless. Please post the Table structures and sample instances of Working as well as not working Inserts.
                Do not forget to post your Version.

                This worked for me:
                create table test_table
                (
                  col1      varchar2(30),
                  col2      varchar2(50)
                );
                
                insert into test_table values ('0x0002', 'Test');
                Do you have anything different that what I tried above?

                --Edit:- Added test data.

                Edited by: Purvesh K on Nov 26, 2012 3:21 PM
                • 5. Re: ORA-01465: invalid hex number
                  ranit B
                  try this..
                  select UTL_RAW.cast_to_raw('0x0002') from dual;
                  I guess this should work coz. it'll convert to raw type which is actually your column type i.e. raw

                  HTH
                  Ranit B.
                  • 6. Re: ORA-01465: invalid hex number
                    969730
                    create table test(

                    EID     NUMBER(10,0),
                    ERRORCODE     RAW,
                    XDESCRIPTION     CLOB
                    );

                    Oracle version9
                    • 7. Re: ORA-01465: invalid hex number
                      Manik
                      Worked for me as well.
                      CREATE TABLE test
                      (
                         EID            NUMBER (10, 0),
                         ERRORCODE      RAW (10),
                         XDESCRIPTION   CLOB
                      );
                      
                      
                      INSERT INTO csms_dbo06.test
                           VALUES (1, HEXTORAW (RAWTOHEX ('0X0002')), 'TEST');
                      
                      COMMIT;
                      Cheers,
                      Manik.
                      • 8. Re: ORA-01465: invalid hex number
                        ranit B
                        This works for me...
                        CREATE TABLE r1
                        (
                           EID            NUMBER (10, 0),
                           ERRORCODE      RAW (10),
                           XDESCRIPTION   CLOB
                        );
                        
                        INSERT INTO r1
                             VALUES (1, UTL_RAW.cast_to_raw ('0x0002'), 'TEST');
                        
                        commit;
                        • 9. Re: ORA-01465: invalid hex number
                          ranit B
                          INSERT INTO csms_dbo06.test
                          VALUES (1, HEXTORAW (RAWTOHEX ('0X0002')), 'TEST');
                          Hi Manik,

                          Why to go for 2 conversions (Function used twice) when Oracle has provided a functionality for this?
                          UTL_RAW.cast_to_raw

                          From docs - docs.oracle.com/html/A96196_01/ap_urpkg.htm
                          >
                          UTL_RAW.CAST_TO_RAW

                          UTL_RAW.CAST_TO_RAW converts a value of data type VARCHAR2 into a raw value with the same number of bytes. The input value is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type RAW.
                          • 10. Re: ORA-01465: invalid hex number
                            Manik
                            Agreed.. UTL_RAW can be used.. (infact better than double conversion.. but I was just extending my example :) )

                            Cheers,
                            Manik.
                            • 11. Re: ORA-01465: invalid hex number
                              969730
                              my raw size 2 byte
                              • 12. Re: ORA-01465: invalid hex number
                                969730
                                not working
                                • 13. Re: ORA-01465: invalid hex number
                                  rp0428
                                  >
                                  not working
                                  >
                                  So? You were ask twice to post the table DDL and the exact query you are using and you don't seem to want to do that.

                                  And you haven't posted your 4 digit Oracle version.

                                  How do you expect anyone to help you if they can't see what you are doing?

                                  You don't need any of those conversions; just insert the correct data. This works for me.
                                  CREATE TABLE r1
                                  (
                                     EID            NUMBER (10, 0),
                                     ERRORCODE      RAW (2),
                                     XDESCRIPTION   CLOB
                                  );
                                   
                                  INSERT INTO r1
                                       VALUES (1, '0002', 'TEST');
                                   
                                  commit;
                                  Oracle will convert that string '0002' to the proper raw format automatically.
                                  select dump(errorcode) from r1
                                  
                                  DUMP(ERRORCODE)
                                  Typ=23 Len=2: 0,2
                                  No conversion functions are necessary; just don't include the '0x' in the string.
                                  • 14. Re: ORA-01465: invalid hex number
                                    rp0428
                                    >
                                    Why to go for 2 conversions (Function used twice) when Oracle has provided a functionality for this?
                                    UTL_RAW.cast_to_raw

                                    From docs - docs.oracle.com/html/A96196_01/ap_urpkg.htm
                                    UTL_RAW.CAST_TO_RAW
                                    
                                    UTL_RAW.CAST_TO_RAW converts a value of data type VARCHAR2 into a raw value with the same number of bytes. The input value is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type RAW. 
                                    Not exactly. Did you read that section? 'converts a value . . . with the same number of bytes'. That isn't what OP is asking for.
                                    1 2 Previous Next