1
create table testblob
(
  id number,
  desc blob
);

Column DESC is loaded by web application ( Zipped XML is loaded into testblob.desc)

Aim : PL SQL to extract the content and send a readable xml to process using XML Parser in Informatica.

Suggest some method to extract compressed/zipped BLOB into string ( which contains the XML content) supporting huge values.

DB : 11g.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Dinesh
  • 21
  • 1
  • 3
  • What do you mean? Like, base64 encoding or similar? You could try [raw](http://stackoverflow.com/q/3804279/266304), but what does Informatica expect or understand? – Alex Poole Sep 15 '14 at 14:08
  • 2
    Not sure why this was down-voted and closed as off-topic. This sounds like a regular programming question to me, and may even have a relatively easy answer: [UTL_COMPRESS.LZ_UNCOMPRESS](http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_compr.htm#ARPLS381). – Jon Heller Sep 15 '14 at 16:18
  • 1
    Valid, though perhaps sketchy on detail question. I speak as a victim of this myself. Anyway, in PLSQL there is no real "string" type. A BLOB is a BLOB. So, write a procedure to RETURN the BLOB, and use the appropriate interface in Informatica to read the BLOB. Or, as suggested us UTL_COMPRESS within the stored procedure to uncompress the zipped BLOB data, and write it to a XMLTYpe, and return the XMLTYpe.getClob .. that should go into a Java String/CLOB equivalent (assuming Informatica understands Java). – TenG Sep 15 '14 at 21:07

0 Answers0