This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Nov 26, 2012 10:48 AM by ranit B RSS

ORA-01465: invalid hex number

969730 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Check this:
    select rawtohex('0X0002') from dual;
    Cheers,
    Manik.
  • 2. Re: ORA-01465: invalid hex number
    969730 Newbie
    Currently Being Moderated
    select is working but insert not
  • 3. Re: ORA-01465: invalid hex number
    Manik Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    create table test(

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

    Oracle version9
  • 7. Re: ORA-01465: invalid hex number
    Manik Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    my raw size 2 byte
  • 12. Re: ORA-01465: invalid hex number
    969730 Newbie
    Currently Being Moderated
    not working
  • 13. Re: ORA-01465: invalid hex number
    rp0428 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    >
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points