Stored Procedure help with XML Clob.

j2911 used Ask the Experts™
Can someone please help me create an Oracle Stored Procedure for a Clob that contains XML?

create global temporary table SHIP_INFO_TEMP
on commit preserve rows as
with myXML as (
select test_id||'-'||ship_seq_nbr as scnro_id, TRKNG_NBR,  
xmlcol from IAT.ship_info
where isvalid(baseline) = 1)
select scnro_id, TRKNG_NBR, TRKNG_NBR,
extractvalue(xmlcol,'//MNBR') "MN",
extractvalue(xmlcol,'//P1') "P1",
extractvalue(xmlcol,'//RECPCNTRYCD') "Dest",
extractvalue(xmlcol,'//RECPNM') "Recipient_Name",
extractvalue(xmlcol,'//SNDRCNTRYCD') "Origin",
extractvalue(xmlcol,'//SHIPPING_DEVICE') "Software",
extractvalue(xmlcol,'//SVCTYPCD') "Service"
from myXML;

I need it to parse this XML faster, so I can use it in a query.  It is taking me 30 minutes to run a query when it has to use this parsed data.  I'm not sure what is the best aproach to handle this.  I would be open to suggestions, if I am barking up the wrong tree.  However it was suggested to me the Stored procedures would work better and faster.  

What is needed is I need to be able to lookup the data that is stored in an XML in this clob.  

If a stored procedure would work --- I need to know how to do that.

Thank you.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Or maybe I should have asked this question:  "how to use the xmltype and call an Oracle stored procedure?"


Is so how would I do that?  I'm searching for examples and cannot find any.
Most Valuable Expert 2011
Top Expert 2012
what is it you're trying to do with the data you pull out of the clob?

using a temp table might not be right way to go,  using a stored procedure might not be either.

if you do use the temp table,  combining it with create table is definitely wrong (unless you do that once ever and never again)

how long does the select above (without the table create)  take?

also,  what is the "isvalid" function doing?  if you make the assumption that your xml is valid, how long does the select take if you remove that condition?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


I have xml that is stored in a clob and it contains lots of information.  So what I did was create view with the above original statement that was only pulling what I needed to pull out of the entire xml.

Now that I have that view, I have a query that pulls from this data from the view.  It only take a couple of minutes to create the view but when I try to pull that data that I need into my other query it takes nearly 30 minutes or more for it to run.  In my query, I am only pulling about 4000 rows of data.  Using the same query and not pulling that data from the clob, it would only take me around 4-5 minutes.  

So it slows down tremedously when I use the data that I retrieved from the xml in the clob.  I am trying to figure the best way to use that data that is stored in xml in a clob.  I need to use it all of the time.  I wanted it to be stored in a table but every day it must be updated with new data.  It was suggested to me that I should use stored procedures but I am not sure how that would work in this situation.


I've requested that this question be closed as follows:

Accepted answer: 0 points for j2911's comment #a38016669

for the following reason:



sorry but much has happened since I posted this question.  Please close this.
Most Valuable Expert 2011
Top Expert 2012

rather than accepting your own post, please accept an answer (or answers) that helped you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial