Home » SQL & PL/SQL » SQL & PL/SQL » Extracting data from BLOB data type (merged) (oracle 9i and 10g)
icon4.gif  Extracting data from BLOB data type (merged) [message #435257] Tue, 15 December 2009 06:37 Go to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi all,
I have a column defined as BLOB in DB. I use BI publisher as reporting tool. It sends a XML file in to this BLOB data type. I need to extract this XML file out of the BLOB to a location.
Kindly suggest me options or query to get the data out.

thanks in advance.
icon4.gif  Extracting data from BLOB data type [message #435260 is a reply to message #435257] Tue, 15 December 2009 06:38 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi all,
I have a column defined as BLOB in DB. I use BI publisher as reporting tool. It sends a XML file in to this BLOB data type. I need to extract this XML file out of the BLOB to a location.
Kindly suggest me options or query to get the data out.

thanks in advance.
Re: Extracting data from BLOB data type [message #435263 is a reply to message #435260] Tue, 15 December 2009 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It sends a XML file in to this BLOB data type.

So the field should be defined as XMLTYPE or CLOB but not BLOB.

Quote:
I need to extract this XML file out of the BLOB to a location.

"location" means variable, table, file?

Regards
Michel
Re: Extracting data from BLOB data type (merged) [message #435271 is a reply to message #435257] Tue, 15 December 2009 07:00 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi Michel,
The table and The BLOB column are created by BI Publisher itself. It is a column created automatically. When i schedule a report the report data are converted in to xml file and getting loaded in to the BLOB column. i need to extract this XML file out.

"Location" - i meant to extract the BLOB to a xml file to a location (any location).
Re: Extracting data from BLOB data type (merged) [message #435273 is a reply to message #435257] Tue, 15 December 2009 07:04 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
The reason behind the column defined as BLOB is .... some times the xml generated in BI publisher is so huge that BI publisher compress it to a .zip file and load this zip file to the BLOB column. so either way i have to extract the data from BLOB
Re: Extracting data from BLOB data type (merged) [message #435276 is a reply to message #435271] Tue, 15 December 2009 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is a file, read DBMS_LOB and UTL_FILE package specifications, they have many file related procedures.

Regards
Michel

[Updated on: Tue, 15 December 2009 07:16]

Report message to a moderator

Re: Extracting data from BLOB data type (merged) [message #435286 is a reply to message #435257] Tue, 15 December 2009 07:36 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Thanks Michel,

will be more happy if u can share any query. since its quite urgent.

Re: Extracting data from BLOB data type (merged) [message #435292 is a reply to message #435286] Tue, 15 December 2009 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are no query, it is a PL/SQL block and I think there are examples in the previous link I posted as well as in Application Developer's Guide - Large Objects.

Regards
Michel
Re: Extracting data from BLOB data type (merged) [message #435294 is a reply to message #435286] Tue, 15 December 2009 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A quick search on Google give me this:
http://www.google.com/search?hl=en&source=hp&q=writing+blob+to+file&aq=0&oq=writing+blob&aqi=g1

http://www.adp-gmbh.ch/blog/2005/november/20.html

Regards
Michel
Re: Extracting data from BLOB data type (merged) [message #435297 is a reply to message #435257] Tue, 15 December 2009 08:05 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Thanks Michel,

Will look in to the link and revert back.
Re: Extracting data from BLOB data type (merged) [message #435372 is a reply to message #435297] Tue, 15 December 2009 20:59 Go to previous messageGo to next message
eoracleapps
Messages: 17
Registered: November 2009
Location: California
Junior Member

see if this can help you

http://eoracleapps.blogspot.com/2009/10/oracle-xmldb.html


eoracleapps
Re: Extracting data from BLOB data type (merged) [message #435385 is a reply to message #435297] Tue, 15 December 2009 23:48 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A recent topic give code to write blob into file:
http://www.orafaq.com/forum/t/153162/102589/

Regards
Michel
Previous Topic: Oracle External Table
Next Topic: Distirbuted Update Operation
Goto Forum:
  


Current Time: Fri Sep 30 09:00:50 CDT 2016

Total time taken to generate the page: 0.11349 seconds