Need a oracle function to conver string to bytes array

Question

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

Answer 1

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