Receiving ORA-01465: invalid hex number when using HEXTORAW
778901Sep 11 2011 — edited Sep 12 2011I'm trying to use hextoraw to convert a value in a CSV file read from an external table. Works on some systems and not on others. Is there a way that I can avoid this or program around this? Any ideas what the problem is? It fails with an “invalid hex value” .
This is for Oracle 10.2.0.4 Here is the code and the external table that I’m using.
select HEXTORAW(REPLACE(EnkryptedPassword,'0x')) FROM CUSTOMER1; <--- Failes or ORA-01465 (CSV file data below)
CREATE TABLE CUSTOMER1
(
UserID VARCHAR2(100 CHAR),
FirstName VARCHAR2(50 CHAR),
LastName VARCHAR2(50 CHAR),
Locked VARCHAR2(50 CHAR),
CustomerID VARCHAR2(100 CHAR),
Pwd VARCHAR2(100 CHAR),
EnkryptedPassword VARCHAR2(100 CHAR)
)
ORGANIZATION external
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ARCHIVE
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
MISSING FIELD VALUES ARE NULL
)
LOCATION ('PWDCONVERSION2')
)
REJECT LIMIT 1
/
COMMIT
/
Inside the PWDCONVERSION2 file:
AFFN2W4@gas.com,JOHN,SMITH,1,C_2M8C2F_CM_NC,C_2M8C2F_CM_NC,0x7C4141938A1F69944BFCA0BED5BB3FEA3CA92A7