OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL*LOADER ERROR (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*LOADER ERROR [message #593074]

Tue, 13 August 2013 20:08

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

Attachment: Create_table_Script.sql
(Size: 0.51KB, Downloaded 2285 times)

[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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: SQL*LOADER ERROR [message #593076 is a reply to message #593075]

Tue, 13 August 2013 20:33

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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593078 is a reply to message #593076]

Tue, 13 August 2013 21:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: SQL*LOADER ERROR [message #593088 is a reply to message #593078]

Wed, 14 August 2013 00:10

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593153 is a reply to message #593088]

Wed, 14 August 2013 11:32

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

Report message to a moderator

Re: SQL*LOADER ERROR [message #593156 is a reply to message #593153]

Wed, 14 August 2013 12:35

joy_division
Messages: 4963
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?

Report message to a moderator

Re: SQL*LOADER ERROR [message #593176 is a reply to message #593153]

Wed, 14 August 2013 14:39

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593178 is a reply to message #593176]

Wed, 14 August 2013 14:46

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

Report message to a moderator

Re: SQL*LOADER ERROR [message #593180 is a reply to message #593178]

Wed, 14 August 2013 15:08

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593182 is a reply to message #593180]

Wed, 14 August 2013 15:18

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593187 is a reply to message #593182]

Wed, 14 August 2013 16:17

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?

Report message to a moderator

Re: SQL*LOADER ERROR [message #593189 is a reply to message #593187]

Wed, 14 August 2013 16:30

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: SQL*LOADER ERROR [message #593191 is a reply to message #593189]

Wed, 14 August 2013 18:35

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.

Report message to a moderator

Previous Topic:	SQL Loader utility
Next Topic:	SELECT on external is very slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:35:28 CST 2025