Home » RDBMS Server » Server Utilities » SQL*LOADER ERROR (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
SQL*LOADER ERROR [message #593074] Tue, 13 August 2013 20:08 Go to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi,
i'm inserting varchar2 data as RAW converted using UTL_I18N.STRING_TO_RAW function in sql loader and i'm getting the below error message.

Record 1: Rejected - Error on table TEST_STR_TO_RAW, column TAG_VAL.
ORA-01465: invalid hex number

Record 2: Rejected - Error on table TEST_STR_TO_RAW, column TAG_VAL.
ORA-01465: invalid hex number

Record 3: Rejected - Error on table TEST_STR_TO_RAW, column TAG_VAL.
ORA-01465: invalid hex number

Record 4: Rejected - Error on table TEST_STR_TO_RAW, column TAG_VAL.
ORA-01465: invalid hex number


CREATE TABLE TEST_STR_TO_RAW
(
  REQ_ID   NUMBER,
  TAG_NM   VARCHAR2(20 BYTE),
  TAG_VAL  VARCHAR2(30 BYTE)
)


SQL LOADER CONTROL FILE
=========================
LOAD DATA
TRUNCATE
INTO TABLE TEST_STR_TO_RAW
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(REQ_ID INTEGER EXTERNAL,
TAG_NM CHAR,
TAG_VAL "decode(:TAG_NM,'DB_PASSWORD',UTL_I18N.STRING_TO_RAW(:TAG_VAL),:TAG_NM,:TAG_VAL)"
)


TEST DATA
============
12345~FILE_TYP~Delimited
12345~DELIMITER~|
12345~TARGET_DB~TEST
12345~USER_NM~TESTUSR
12345~DB_PASSWORD~TESTPWD

attached sql file has the the test table create script, sql loader control file and sample data. please let me know what is wrong in my script?

*BlackSwan placed data inline & inside {code} tags

[Updated on: Tue, 13 August 2013 20:18] by Moderator

Report message to a moderator

Re: SQL*LOADER ERROR [message #593075 is a reply to message #593074] Tue, 13 August 2013 20:26 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
Please clarify why you are using UTL_I18N.STRING_TO_RAW
The content of the file is text & the column is datatype VARCHAR2.

what is the results when the function is not used?
Re: SQL*LOADER ERROR [message #593076 is a reply to message #593075] Tue, 13 August 2013 20:33 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi,
my requirement is to store the passwords in a database table and hence i'm converting the password using UTL_I18N.STRING_TO_RAW not to storing as is.

i believe this function will convert varchar to raw datatype which can not be understand easily unless it reconvert back to string.
Re: SQL*LOADER ERROR [message #593078 is a reply to message #593076] Tue, 13 August 2013 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
[oracle@localhost ~]$ sqlldr user1/user1 control=raw.ctl 

SQL*Loader: Release 11.2.0.2.0 - Production on Tue Aug 13 19:10:20 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5
[oracle@localhost ~]$ sqlplus user1/user1

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 13 19:10:41 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from TEST_STR_TO_RAW;

    REQ_ID TAG_NM               TAG_VAL
---------- -------------------- ------------------------------
     12345 DB_PASSWORD          54455354505744

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ cat raw.ctl
LOAD DATA
infile 'raw.txt'
TRUNCATE
INTO TABLE TEST_STR_TO_RAW
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(REQ_ID INTEGER EXTERNAL,
TAG_NM CHAR,
TAG_VAL "decode(:TAG_NM,'DB_PASSWORD',UTL_I18N.STRING_TO_RAW(:TAG_VAL),:TAG_NM,:TAG_VAL)"
)
[oracle@localhost ~]$ 



I am still looking for the other records
Re: SQL*LOADER ERROR [message #593088 is a reply to message #593078] Wed, 14 August 2013 00:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
Decode expects to return the same data type no matter which value matches. If the first one matches, then it returns the first data type. If the second one matches, then it still expects to return the same data type as the first one, so it fails when it is different. You may want to return your encrypted password into a separate column to avoid the conflict.

Re: SQL*LOADER ERROR [message #593153 is a reply to message #593088] Wed, 14 August 2013 11:32 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi,
Thanks for your suggestions. but my requirement is i can not have another column to store the encrypted password as the table is structure is as it look like.
i have a data file where we have different tag_nm and tag_val for each tag coming from another system and i need to load these fields into my table just only encrypting the password if the user is provided else no encryption to any other tag_nm.

so basically the data file looks like an unpivot data.

any help would be appreciated.

Thanks
Fakru.Y
Re: SQL*LOADER ERROR [message #593156 is a reply to message #593153] Wed, 14 August 2013 12:35 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
Why not load it in "as-is" and then convert it via the package later, or use a trigger to do it?
Re: SQL*LOADER ERROR [message #593176 is a reply to message #593153] Wed, 14 August 2013 14:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2090
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is it mandate for you to NOT view the passwords using a db connection or you just want to encrypt the column for the data after committed and stored in datafile? For the latter, there are many ways. But, it is only to encrypt data from OS level.

Let me know.
Re: SQL*LOADER ERROR [message #593178 is a reply to message #593176] Wed, 14 August 2013 14:46 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi,
The password stored in the database table should not be as is, it should be encrypted and stored. this is all i want.

Thanks
Fakru.Y
Re: SQL*LOADER ERROR [message #593180 is a reply to message #593178] Wed, 14 August 2013 15:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
You can get around the problem with decode by using two different into table and when clauses as demonstrated below.

SCOTT@orcl12c_11gR2> host type test.dat
12345~FILE_TYP~Delimited
12345~DELIMITER~|
12345~TARGET_DB~TEST
12345~USER_NM~TESTUSR
12345~DB_PASSWORD~TESTPWD

SCOTT@orcl12c_11gR2> host type test.ctl
LOAD DATA
TRUNCATE
INTO TABLE TEST_STR_TO_RAW
WHEN tag_nm='DB_PASSWORD'
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(REQ_ID POSITION(1) INTEGER EXTERNAL,
TAG_NM CHAR,
TAG_VAL "UTL_I18N.STRING_TO_RAW(:TAG_VAL)"
)
INTO TABLE TEST_STR_TO_RAW
WHEN tag_nm!='DB_PASSWORD'
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(REQ_ID POSITION(1) INTEGER EXTERNAL,
TAG_NM CHAR,
TAG_VAL
)

SCOTT@orcl12c_11gR2> CREATE TABLE TEST_STR_TO_RAW
  2  (
  3    REQ_ID   NUMBER,
  4    TAG_NM   VARCHAR2(20 BYTE),
  5    TAG_VAL  VARCHAR2(30 BYTE)
  6  )
  7  /

Table created.

SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=test.ctl data=test.dat log=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Aug 14 13:06:50 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 5

Table TEST_STR_TO_RAW:
  1 Row successfully loaded.

Table TEST_STR_TO_RAW:
  4 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c_11gR2> select * from test_str_to_raw
  2  /

    REQ_ID TAG_NM               TAG_VAL
---------- -------------------- ------------------------------
     12345 DB_PASSWORD          54455354505744
     12345 FILE_TYP             Delimited
     12345 DELIMITER            |
     12345 TARGET_DB            TEST
     12345 USER_NM              TESTUSR

5 rows selected.

Re: SQL*LOADER ERROR [message #593182 is a reply to message #593180] Wed, 14 August 2013 15:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
Using a trigger, as suggested by joy division also works, as demonstrated below.

SCOTT@orcl12c_11gR2> host type test.dat
12345~FILE_TYP~Delimited
12345~DELIMITER~|
12345~TARGET_DB~TEST
12345~USER_NM~TESTUSR
12345~DB_PASSWORD~TESTPWD

SCOTT@orcl12c_11gR2> host type test.ctl
LOAD DATA
TRUNCATE
INTO TABLE TEST_STR_TO_RAW
FIELDS TERMINATED BY "~"
TRAILING NULLCOLS
(REQ_ID INTEGER EXTERNAL,
TAG_NM CHAR,
TAG_VAL
)

SCOTT@orcl12c_11gR2> CREATE TABLE TEST_STR_TO_RAW
  2  (
  3    REQ_ID   NUMBER,
  4    TAG_NM   VARCHAR2(20 BYTE),
  5    TAG_VAL  VARCHAR2(30 BYTE)
  6  )
  7  /

Table created.

SCOTT@orcl12c_11gR2> create or replace trigger encrypt_password
  2    before insert on test_str_to_raw
  3    for each row
  4    when (new.tag_nm='DB_PASSWORD')
  5  begin
  6    :new.tag_val := UTL_I18N.STRING_TO_RAW(:new.TAG_VAL);
  7  end;
  8  /

Trigger created.

SCOTT@orcl12c_11gR2> show errors
No errors.
SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=test.ctl data=test.dat log=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Aug 14 13:15:29 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 5

Table TEST_STR_TO_RAW:
  5 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c_11gR2> select * from test_str_to_raw
  2  /

    REQ_ID TAG_NM               TAG_VAL
---------- -------------------- ------------------------------
     12345 FILE_TYP             Delimited
     12345 DELIMITER            |
     12345 TARGET_DB            TEST
     12345 USER_NM              TESTUSR
     12345 DB_PASSWORD          54455354505744

5 rows selected.

Re: SQL*LOADER ERROR [message #593187 is a reply to message #593182] Wed, 14 August 2013 16:17 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi Thanks for your suggestion and solution. i'm not clear on your control file in previous example written using WHEN clause, i did follow the same approach using WHEN clause except POSITION(1), but i did not get succeed. what does POSITION(1) will do and how it resolves the issue?
Re: SQL*LOADER ERROR [message #593189 is a reply to message #593187] Wed, 14 August 2013 16:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7969
Registered: November 2002
Location: California, USA
Senior Member
fakru.y wrote on Wed, 14 August 2013 14:17
Hi Thanks for your suggestion and solution. i'm not clear on your control file in previous example written using WHEN clause, i did follow the same approach using WHEN clause except POSITION(1), but i did not get succeed. what does POSITION(1) will do and how it resolves the issue?


Any time that you use multiple when clauses, the position has to be reset for each when clause after the first one. You don't need it with the first when clause, but I tend to use it just for consistency. Without the position on the subsequent when clauses, SQL*Loader may start trying to read data at a different position and not be able to load it.

Re: SQL*LOADER ERROR [message #593191 is a reply to message #593189] Wed, 14 August 2013 18:35 Go to previous message
fakru.y
Messages: 34
Registered: May 2007
Member
Hi Barbara Boehmer,
Thanks for the detailed explanation. i'm clear now. thanks all for your valuable solutions and suggestions.
Previous Topic: SQL Loader utility
Next Topic: SELECT on external is very slow
Goto Forum:
  


Current Time: Fri Aug 22 19:31:27 CDT 2014

Total time taken to generate the page: 0.09214 seconds