Running into issues when using BLOB with PLSQL

Question

I have the following PLSQL code:

declare 
v_exec_obj_strng    varchar2(4000); 
p_map_name varchar2(40) := 'TEST'; 
p_key      number  := 4; 
p_checksum  varchar2(40) := '111111111111111'; 
p_value     blob := 11111111111111111111; 

begin 
v_exec_obj_strng := 'insert into my_table(name, key, chksum, value) values (''' || p_map_name || ''', ' || p_key || ', ''' || p_checksum || ''', ''' || p_value || ''')';
dbms_output.put_line(v_exec_obj_strng); 
end;
/

and I am getting this error: PLS-00320: the declaration of the type of this expression is incomplete or malformed

thoughts?

kfinity · Accepted Answer · 2019-10-04 17:20:43Z

3

You can't just assign an integer value to a blob like that.

You can assign a RAW value to a BLOB using TO_BLOB:

p_value     blob := to_blob(UTL_RAW.CAST_FROM_NUMBER(11111111111111111111));

Or look at the DBMS_LOB package for more examples of how to assign values to BLOBs. Especially look at CONVERTTOBLOB and LOADBLOBFROMFILE.

Edit: to clarify, DBMS_LOB.CONVERTTOBLOB only works for character data, and it's overly complicated. Please just give an example of what you're actually trying to do.

edited Oct 4, 2019 at 17:20

answered Oct 4, 2019 at 16:47

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

samg Over a year ago

Would you be able to show me how to use the CONVERTTOBLOB based on my code above?

kfinity Over a year ago

Ehh... no, it's too messy. But I edited my example to show how to insert a number into a blob.

Collectives™ on Stack Overflow

Running into issues when using BLOB with PLSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related