CODEWITHSUNDEEP
oracle-databaseplsqllob
samg
samg

Reputation: 323

Running into issues when using BLOB with PLSQL

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?

Upvotes: 0

Views: 275

Answers (1)

kfinity
kfinity

Reputation: 9091

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.

Upvotes: 3

Related Questions