Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    14

    Unanswered: Inserting data in Hexadecimal mode

    Please

    I have a question about Inserting data in Hexadecimal mode:

    that's my table:
    Code:
    CREATE TABLE STAB (
      TYTABL varchar(1)  default ''  NOT NULL,
      CLTABL varchar(4)  default ''  NOT NULL,
      CDTABL varchar(30)  default ''  NOT NULL,
      DTFEFF char(8) default NULL
    );
    In MySql or DB2, i can use this insert query to insert data:
    Code:
    INSERT INTO STAB (TYTABL, CLTABL, CDTABL, DTFEFF) VALUES
    (x'31', x'20204145', x'202A20404040202B20656263', x'20407F')
    This insert query doesn't work in the ORACLE.
    Is there a method i can use to insert data in hexadecimal mode in ORACLE???.

    Thanx
    Last edited by arammal; 03-28-07 at 04:25.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Do you want to store them in binary form or to store them as text ? Text is always interpreted by Oracle and is converted from one character set to another when exchanged between a client and the database server, whereas binary data is never converted, always exchanged "as is".

    If you want to store them in binary form, then don't use VARCHAR2 columns but RAW columns and insert them from hex form with HEXTORAW. If you want to store them in text form, then convert the hex form to raw with HEXTORAW and then convert it to VARCHAR2 with UTL_RAW.CAST_TO_VARCHAR2.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Mar 2007
    Posts
    14
    Thank you very much rbaraer
    In Fact I used this method to insert The Hexadecimals in the Tables:
    Code:
    create or replace function to_dec
    ( p_str in varchar2, 
      p_from_base in number default 16 ) return number
    is
    	l_num   number default 0;
    	l_hex   varchar2(16) default '0123456789ABCDEF';
    begin
    	if ( p_str is null or p_from_base is null )
    	then
    		return null;
    	end if;
    	for i in 1 .. length(p_str) loop
    		l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
    	end loop;
    	return l_num;
    end to_dec;
    /
    Code:
    create or replace function hex2Char
    ( p_str in varchar2 ) return VARCHAR2
    is
    	v_ret	varchar2(15000) default '';
    	st      number default 1;
    begin
    	WHILE st < length(p_str) LOOP
    		v_ret 	:= v_ret || chr(to_dec(substr(p_str, st, 2)));
    		st 		:= st + 2;
    	END LOOP;
    	return v_ret;
    end hex2Char;
    /
    Code:
    INSERT INTO STAB (TYTABL, CLTABL, CDTABL, DTFEFF) VALUES(hex2Char('31'), hex2Char('20204145'), hex2Char('202A20404040202B20656263'), hex2Char('20407F'))
    But it seems that it takes so much time than the way you suggest.
    So Now I use
    Code:
    INSERT INTO STAB (TYTABL, CLTABL, CDTABL, DTFEFF) 
    VALUES(
    UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('31')), 
    UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('20204145')), 
    UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('202A20404040202B20656263')), 
    UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('20407F')))
    thank you again

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You're welcome
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I wonder what the advantange of this method is?
    Using
    Code:
    INSERT INTO STAB (TYTABL, CLTABL, CDTABL, DTFEFF) 
    VALUES('1', '  AE', ' * @@@ + ebc', ' @');
    seems a lot simpler to me.

  6. #6
    Join Date
    Mar 2007
    Posts
    14
    And to me too

    But, my project is:
    Migrate an AS400/DB2 to LINUX/ORACLE
    the problem is:
    there is some field in the DB2 that continue VARCHAR + BINARY + NUMERIC on the same time.

    So i select these fields in Hexadecimal EBCDIC then I convert the non Binary Data to ASCII, i let the Binary Data in the EBCDIC Format, then I insert them on to the Oracle DB.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Does this help?
    Code:
      1* select to_char(255,'XXX') HEX from dual
    SQL> /
    
    HEX
    ----
      FF
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •