CODEWITHSUNDEEP
oracle-databaseoracle10g
neel.1708
neel.1708

Reputation: 315

Need a oracle function to conver string to bytes array

I have a Column :

LOGIN_PWD  ->  RAW(256 BYTE)

I have to use a insert query to insert a hashed password string into this column. So is there any oracle function that can covert a string to bytes like 

INSERT INTO TABLE_NAME (LOGIN_PWD) VALUES (convert.toBytes('hashed password'));

I did this using java program by using string.getBytes() methode on my local machine but for other env i cant use same program need to do it using query

Upvotes: 6

Views: 15365

Answers (1)

Alex Poole
Alex Poole

Reputation: 191520

If your requirement is as simple as you stated then you can use the UTL_RAW.CAST_TO_RAW function:

INSERT INTO TABLE_NAME (LOGIN_PWD)
VALUES (UTL_RAW.CAST_TO_RAW('hashed password'));

SQL Fiddle demo.

With, for example, the plain string 'hashed password' hashed using Md5, which is 6a25a2b265d917ea91447daa81b2506d, the raw value stored in the table is:

SELECT DUMP(LOGIN_PWD) FROM TABLE_NAME;

DUMP(LOGIN_PWD)
------------------------------------------------------------------------------------------------------------------
Typ=23 Len=32: 54,97,50,53,97,50,98,50,54,53,100,57,49,55,101,97,57,49,52,52,55,100,97,97,56,49,98,50,53,48,54,100

Which matches what I get from getBytes() on the same hashed value in Java.

If you want to get it back to text for some reason you can use UTL_RAW.CAST_TO_VARCHAR2:

SELECT UTL_RAW.CAST_TO_VARCHAR2(LOGIN_PWD) FROM TABLE_NAME;

UTL_RAW.CAST_TO_VARCHAR2(LOGIN_PWD)
-----------------------------------
6a25a2b265d917ea91447daa81b2506d

Upvotes: 13

Related Questions