SQL & PL/SQL

hello community,

I'm new to Oracle and I face new problems with this database every hour... I already grew grey hair in my beard
because of this :( after many hours of fighting with my DB I thought I'm now capable of inserting a BLOB into my tables, but - oh wonder - I am not!

Oracle DB: *10g Database Express Edition*

so, my problem which causes me massive headache is:
I try to insert a BLOB into a BLOB declared field which is initalized with EMPTY_BLOB() when creating the insert. afterwards I want
to update the row and insert a BLOB object into this field.

my table looks like this:
ID | Foreign_Key_1 | Foreign_Key_2 | StartTime | EndTime | BLOB_1 | BLOB_2

all I want to do is inserting a BLOB object into the field BLOB_1. so I tried the following (just for testing):

<SQL>
DECLARE
lob_test BLOB := NULL;
v_string VARCHAR2(4000 CHAR) := NULL;
BEGIN
DBMS_LOB.createtemporary(lob_test,TRUE,DBMS_LOB.session);
DBMS_LOB.open(lob_test, DBMS_LOB.lob_readwrite);
v_string := 'test';
DBMS_LOB.writeappend(lob_test, LENGTH(v_string), v_string);
DBMS_LOB.close(lob_test);
UPDATE TABLE_1 SET BLOB_1=lob_test WHERE ID='1';
END;
</SQL>

looks pretty good so far, doesn't it?

now, the error comes in and I can't get any sense out of this:
"DECLARE lob_test BLOB := NULL; v_string VARCHAR2(4000" "ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error"


what is Oracle trying to tell me with that errormessage? what am I doing wrong?

any help is much appreciated ;)

cheers

Edited by: 846725 on 23.03.2011 08:33

Edited by: Ramirez on 24.03.2011 01:09