Solved

Help in Understanding this oracle query

Posted on 2007-04-09
4
558 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query returning everything 11 87
unable to get sorting resultset 15 68
report returning null 21 79
Schema creation in Oracle12c 6 39
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now