Re: Help loading hex values in table using SQLload

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 23 Jan 1999 03:06:59 GMT
Message-ID: <36ab3b92.4761256@192.86.155.100>

A copy of this was sent to arivlin_at_my-dejanews.com (if that email address didn't require changing) On Sat, 23 Jan 1999 02:34:12 GMT, you wrote:

>Hi, all,
>I need to import a text file into table. Assume that the table has 2
>cols: Name, Value
>and file is '^' separated.
>Value is an integer value, but in the file it is represented as hex.
>something like that:
>John^0xF
>Bob^0x10
>Mike^0x11
>
>How do I import data? I do not mind importing data into a temp table
>that has both cols as char and then moving data from temp table to real
>table.
>I want data in real table to be
>
>Name Value
>-------- ----------
>John 15
>Bob 16
>Mike 17
>
>
>Thank you in advance
>Alex
>

if you have a function like:

SQL> create or replace function to_dec
  2 ( p_str in varchar2,
  3 p_from_base in number default 16 ) return number   4 is

  5          l_num   number default 0;
  6          l_hex   varchar2(16) default '0123456789ABCDEF';
  7  begin
  8          for i in 1 .. length(p_str) loop
  9                  l_num := l_num * p_from_base +
instr(l_hex,upper(substr(p_str,i,1)))-1;
 10          end loop;
 11          return l_num;

 12 end to_dec;
 13 /

Function created.

SQL> select to_dec( substr( '0xF', 3 ) ) from dual;

TO_DEC(SUBSTR('0XF',3))

---

                     15

and a ctl file like:

LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY '^'
(name char (30),
 value char(30) "to_dec( substr( :value,3) )" )
BEGINDATA
John^0xF
Bob^0x10
Mike^0x11

it'll work with the conventional path loader....

>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 22 1999 - 21:06:59 CST