Solved

Help in Understanding this oracle query

Posted on 2007-04-09
4
567 Views
Last Modified: 2013-12-18
i have a query like below:
select test_id,type,status,serial,item,operation,
     station,start_time,end_time,
     utl.blob2clob.compressor.blob_decompress(xml_data) as xmld
from test.test_data_v3
where status='CompletedNormally'
and start_time = to_date('02/12/2007','MM/DD/YYYY')
and operation = 'TxpCalAndTest';

what does this mean over here:
utl.blob2clob.compressor.blob_decompress(xml_data) as xmld

actualy the test data is stored as xml which in turn is stored as xml blob in the db

so i think this is one of the way to access the xml blob...but i cannot make head or tail out of it.
is there any document as to how to access data from oracle stored as xml blob and extract all the parameters?

thanks a lot
0
Comment
Question by:samir25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 10

Expert Comment

by:Jaax
ID: 18875246
It means it converts the XML data in a compressed format to an uncompressed/native contents so that it is intelligible. XML is stored in BLOB to conserve space, so we need to decompress it. Its like zipping and unzipping a file.
0
 
LVL 10

Expert Comment

by:Jaax
ID: 18875251
It means it converts the XML data in a compressed format to an uncompressed/native contents so that it is intelligible. XML is stored in BLOB to conserve space, so we need to decompress it. Its like zipping and unzipping a file.
0
 
LVL 1

Author Comment

by:samir25
ID: 18875324
ok little clear..
so here utl.blob2clob.compressor.blob_decompress(xml_data) as xmld
database name is utl? and blob to clob...?
then xml_data is the actual blob data in the db? can u help me understand more?
i was wondering how will i access the individual node in the xml file? do u know of any link which can help me onthis? thanks a lot
0
 
LVL 10

Accepted Solution

by:
Jaax earned 500 total points
ID: 18875534
>>utl . It is the name of the package for utilities. Doesn't seem like a oracle utility. Maybe it is used by your company
blob_decompress is the function in utl.blob2clob.compressor package.

>>then xml_data is the actual blob data in the db.
Yes

I guess when you store the entire XML document as a CLOB, you cannot access the individual node unless it is extracted/parsed.

Please refer these links for more details:
http://www.dbazine.com/olc/olc-articles/scardina1
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/scardina_xmldb.html
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96620/xdb01int.htm
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help on model clause 5 47
add more rows to hierarchy 3 34
update statement in oracle 9 40
Migration from sql server to oracle (IF then else condition ) 13 43
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question