Convert CHAR to BLOB?

[r***@gmail.com]

I have a stored procedure that takes a blob as an input parameter. I
check to see if the parameter is null by using CURSOR passCur IS
SELECT NVL(PASSWORD, '***@ssw0rd')
FROM DUAL;

PASSWORD is my Blob (it's a bytestream of a hashed password). If
nothing is provided, i want to put the word ***@ssw0rd into the field.

I open the cursor and try to fetch the result into a temporary variable
and always get this error.
Error -932: ORA-00932: inconsistent datatypes: expected BLOB got CHAR

Is there something i can do to make sure i put in a BLOB value?

Thanks,
Rob

[Hexathioorthooxalate]

The error says it all. Below is one way you could use to get you on the
right track
Regards
Hex

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE fred(a IN BLOB) IS
2 b BLOB;
3 BEGIN
4 b:=a;
5 IF 0=DBMS_LOB.GETLENGTH(b) THEN
6 b:=TO_BLOB(UTL_RAW.CAST_TO_RAW('***@ssw0rd'));
7 END IF;
8
9 --Whatever, here I am outputting the size here
10 DBMS_OUTPUT.PUT_LINE('Your blob contains ' ||
TO_CHAR(DBMS_LOB.GETLENGTH(b)) || ' bytes');
11
12 END;
13 /

Procedure created.

SQL>
SQL>
SQL> EXEC fred(UTL_RAW.CAST_TO_RAW('four'));
Your blob contains 4 bytes

PL/SQL procedure successfully completed.

SQL> --8 bytes is the length of '***@ssw0rd'
SQL> EXEC fred(EMPTY_BLOB());
Your blob contains 8 bytes

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT *
2 FROM V$VERSION;

BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.1.0.2.0 - Production
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL>

Post by r***@gmail.com
I have a stored procedure that takes a blob as an input parameter. I
check to see if the parameter is null by using CURSOR passCur IS
FROM DUAL;
PASSWORD is my Blob (it's a bytestream of a hashed password). If
I open the cursor and try to fetch the result into a temporary variable
and always get this error.
Error -932: ORA-00932: inconsistent datatypes: expected BLOB got CHAR
Is there something i can do to make sure i put in a BLOB value?
Thanks,
Rob

[r***@gmail.com]

Just a follow up question. I have been able to get the character
string to convert to a blob fine now. The problem is that the hash
value is not being set right. I figured out what the byte array
represented as a hex string is, now i just need to convert the hex
string into varchar. I tried using HEXTOCHAR() and got an error that
it must be declared. I'm on Oracle 10g. Is this in some package i
need to reference?

Thanks,
Rob

[Michael O'Shea]

Hi, AFAIK there is no single built in function to perform your
"HEXTOCHAR" functionality. However you can do it indirectly through
code not unlike that demonstrated below.

SQL>
SQL> SELECT
UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('5465737420537472696E67')) HEX2CHAR
FROM DUAL;

HEX2CHAR
--------------------
Test String

SQL>
SQL>

Regards

TESSELLA ***@tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: [phone] Fax: [phone]
www.tessella.com Registered in England No. 1466429

Post by r***@gmail.com
Just a follow up question. I have been able to get the character
string to convert to a blob fine now. The problem is that the hash
value is not being set right. I figured out what the byte array
represented as a hex string is, now i just need to convert the hex
string into varchar. I tried using HEXTOCHAR() and got an error that
it must be declared. I'm on Oracle 10g. Is this in some package i
need to reference?
Thanks,
Rob