Home > Default > DBMS_CRYPTO.HASH produces a different hash result when I use it in sqlldr?

DBMS_CRYPTO.HASH produces a different hash result when I use it in sqlldr?

November 30Hits:0
Advertisement
Hello,
If I execute the following in sqlplus:
SELECT LOWER(SYS.DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(CONCAT('Salt','000000000')),3)) FROM DUAL;
It produces one hash value. However, if I use similar logic in a sqlldr control file:
OPTIONS (SKIP=7)
LOAD DATA
INFILE 'C:\data.csv'
TRUNCATE INTO TABLE information
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
HOSTNAME          CONSTANT 'Company',
HOSTSERVICENAME     CONSTANT 'LOB',
ACCOUNTNAME,
LASTNAME,
EXTERNAL_ACCOUNT_ID     "LOWER(SYS.DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(CONCAT('Salt',:accountname)),3))"
Where ACCOUNTNAME in the file data.csv is also '000000000'. What am I doing wrong?
Thanks so much,
Jay

Answers

user3362629 wrote:
If anyone is interested, the problem was with the column datatype of the table. sqlldr was attempting to load data into a column of type NVARCHAR2. When I changed the column type to VARCHAR2, the hash values from both sqlldr and sqlplus were the same. I'm not sure if this is an Oracle bug or if this expected behavior. This was using Oracle Enterprise Edition 11g.Jay,
that's very interesting. One possible explanation that I could think of: Your SQL*Plus attempt:
If I execute the following in sqlplus:
SELECT LOWER(SYS.DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(CONCAT('Salt','000000000')),3)) FROM DUAL;used a VARCHAR2 literal, but the SQL*Loader code probably used a NVARCHAR2 as input to the function. From a RAW data perspective, these two, although representing the same string, could be quite different.
You could try to do the same in SQL*Plus, by using a NVARCHAR2 input to the function. If you want to achieve that using a literal the problem is, that it's not that simple to use a NVARCHAR2 literal. You would need to use an NVARCHAR2 literal (N'000000000') and in addition probably turn on the so called "NCHAR String Literal Replacement", which means that you need to set an environment variable (ORA_NCHAR_LITERAL_REPLACE = TRUE).
For more information regarding these issues, look here:
Text literals: http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch7progrunicode.htm#CACHHIFE
NCHAR String Literal Replacement: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements003.htm#SQLRF00218
It's probably far simpler to use a NVARCHAR2 stored somewhere in the database, or use e.g. a concatenation of the NCHR() function to generate a NVARCHAR2 on the fly than dealing with these literal conversion issues mentioned above.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Read other 5 answers

Tags:

Related Articles

  • DBMS_CRYPTO.HASH produces a different hash result when I use it in sqlldr?November 30

    Hello, If I execute the following in sqlplus: SELECT LOWER(SYS.DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(CONCAT('Salt','000000000')),3)) FROM DUAL; It produces one hash value. However, if I use similar logic in a sqlldr control file: OPTIONS (SKIP=7) LOAD

  • Dbms_crypto hash for SHA-1November 30

    I need to implement Salted SHA {SSHA} passwords by updating LDAP using PL/SQL. These values should be stored in standard Base 64 encoded format. For this I am using DBMS_CRYTO, UTL_ENCODE and DBMS_LDAP. However, I am finding issues with RAW values. S

  • Encrypt and decrypt using dbms_crypto.hashOctober 11

    hi to all i am newbie here and i've just want to ask if how can i decrypt the encryted password. v_pass_string = 'qwerty'; lower(dbms_crypto.hash(to_clob(v_pass_string),3)) The out is: b1b3773a05c0ed0176787a4f1574ff0075f7521e and i want to output it

  • Hash created using DBMS_CRYPTO.Hash and md5sum, sha1sum not maching.November 30

    Hi, I have PL/SQL script MD5SH1.sql to generate md5 and sha1 hash, but hash generated by this script and hash generated by md5sum and sha1sum utility not matching. Pl. tell me what is wrong, ------------------MD5SH1.sql---------------- set serveroutp

  • Error in dbms_crypto.hashNovember 30

    I am trying to run following query in my database - select RAWTOHEX(dbms_crypto.hash(src => UTL_I18N.STRING_TO_RAW('local123'),typ => dbms_crypto.HASH_SH1)) from dual; But i am getting the following error message - ORA-00907: missing right parenthes

  • Dbms_crypto.hashNovember 30

    Hi , create table src a number, b clob, c varchar2(100) when i execute select a,b,c from src where a=20 ,the i get 1 row .but when i use select a,dbms_crypto.hash(b,2),c from src where a=20 then i get an error ORA-01405: fetched column value is NULL

  • SQL job running DTEXEC.EXE via PowerShell step produces wrong characters in result tables. Running the same DTEXEC.EXE in PowerShell x86 window produces correct results.November 30

    I've created a package in SSDT (Visual Studio 2012) to import DBF tables into MSSQL 2014 via a wizard. Source DBF files contain tables with Russian (Cyrillic) characters in records fields. Created package works fine, produces correct results (imports

  • Merge to HDR pro is producing very washed out results. Resulting images look terrible and bleached.November 30

    I posted here before, but was directed to go through the help menu in Photoshop, which brought me back here!! Please Help Me! Merge to HDR pro is producing very "bleached" and overexposed images. Thank you, RobThank you for the reply, Ed. I've d

  • DBMS_CRYPTO MD5 hash value does not match 3rd party MD5 free toolNovember 30

    Hello, I am using Oracle Version: 11.2.4. I have a problem where the MD5 value from DBMS_CRYPTO does not match the hash value from 3rd party MD5 free tool (MD5 Checksum Calculator 0.0.5.58 or WinMD5Free v1.20) and also the MD5 hash value calculated b

  • MD5 hash code in PLSQLNovember 30

    Hi everyone, I am building dynamic web pages using Oracle's plsql web toolkit. I am building a page that will pass information to a credit card authorizer. I need to create a MD5 hash string to pass to the credit card company. Is there an Oracle buil

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.872 s.