0

I am trying to run the below query in SQL developer which gives weird result in the output:

select utl_raw.cast_to_varchar2(dbms_lob.substr(message,100))
from XML_MESSAGE;

My problem is that the result shows the correct XML message for some rows but random value for others like this: x�[o�~߯���m0��$N�k��m��~�S�_���|d�G��qmD�7拔����

I am not sure why this happening. Is it because of casting BLOB to varchar2?

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
fiddle
  • 1,095
  • 5
  • 18
  • 33

1 Answers1

2

You should do substr on utl_raw.cast_to_varchar2 instead of the blob column message. So, the final query will be like the following:

select dbms_lob.substr(utl_raw.cast_to_varchar2(message),100) from XML_MESSAGE;
bprasanna
  • 2,423
  • 3
  • 27
  • 39