Oracle - subst Instr

Hi,
I have a large CLOB datatype field I'm working with.
Within it I am trying to pull back some information



Each of the entries within this CLOB will be quite different but I do have and end and start point between the piece of data that I want to pull out of this field.

I have been trying to do a SUBST / INSTR combo but I can't get this to return the results I wanted, most times it returns just blanks. If someone can help I'd really appreciate this.

Example of the field would be:
 <Params><In/><Out><Param Name="Message" Value="This is the piece I need."/></Out></Params>
Or
<Params><In><Param Name="PlanID" Value="123546"/></In><Out><Param Name="ResultMessage" Value="This is the piece I need."/></Out></Params>

Lets call the Field FIELDNAME
You can see the piece I want to pull is the bit in quotes "This is the piece I need"
Each string has the exact section at the start and end between the string I need. The sting I need can vary in length.

I'm trying to understand how I can , with out adding the number so its more dynamic depending on the length of the string, could I add something else to calculate that value.
and without using replace to remove the end part of the field - could I use something else.

 SELECT
REPLACE(  substr(FIELDNAME,
  instr(FIELDNAME,'<In/><Out><Param Name="Message" Value="',1,1) +LENGTH('<In/><Out><Param Name="Message" Value="'),
  LENGTH(E1.HEVT_PARAMETERS) -
   instr(E1.HEVT_PARAMETERS,'<In/><Out><Param Name="ResultMessage" Value="',1,1),
   "/></Out></Params>','')  )

My method works for the first field example, but not the second and im not sure why.
any advice is much appreciated
Thank you,
PutochAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I recommend you extract the values from the XML using very powerful XMLType. All you need to do is to cast the CLOB to XMLType and use .extract('//Out/@Value').getStringVal():
SELECT XMLType(E1.HEVT_PARAMETERS).extract('//Out/Param/@Value').getStringVal() 
from E1 WHERE XMLType(E1.HEVT_PARAMETERS).extract('//Out/Param/@Name').getStringVal() = 'Message';
SELECT XMLType(E1.HEVT_PARAMETERS).extract('//Out/Param/@Value').getStringVal() 
from E1 WHERE XMLType(E1.HEVT_PARAMETERS).extract('//Out/Param/@Name').getStringVal() = 'ResultMessage';

Open in new window

SQLFiddle
slightwv (䄆 Netminder) Commented:
Is this Oracle or Sybase?

If Oracle:
EXTRACT is deprecated and shouldn't be used.

The new way is XMLTABLE.

See if the example below works for you:
drop table tab1 purge;
create table tab1(fieldname varchar2(200));

insert into tab1 values('<Params><In/><Out><Param Name="Message" Value="This is the piece I need."/></Out></Params>');
insert into tab1 values('<Params><In><Param Name="PlanID" Value="123546"/></In><Out><Param Name="ResultMessage" Value="This is the piece I need."/></Out></Params>');

commit;

select myvalue from
tab1,
xmltable(
		'//Out/Param'
		passing xmltype(fieldname)
		columns
			myvalue varchar2(100) path '@Value'
)
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
Thanks all for your help, on taking your advice Slightwv and not using the extract example I am looking at your answer.
Slightwv, on your example above, you created a table called tab1 with one field called fieldname.
in your select statement you return 'myvalue'
what is this?  
Am I meant to have created a variable for myvalue based on the fieldname (just trying to understand your query)

also when I run this I get the following results:
"You cannot access the application at this time.  Please try again later."

I wondered if it was because my field datatype is clob and not varchar2(100), but even after changing that I still get the same result as above; "You cannot access the application at this time.  Please try again later."

Thank you
Determine the Perfect Price for Your IT Services

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

PutochAuthor Commented:
Slightwv, I'm sorry your query does work, I missed something in it.

Thank you for your help.
I have a few questions just on the query.

select myvalue from
tab1,
xmltable(
            '//Out/Param'
            passing xmltype(fieldname)
            columns
                  myvalue varchar2(100) path '@Value'
)
/
 

do you always use the very last part of the xml beside the section of text you want to return?
in your example you used "'//Out/Param'"

When you declare the returned value as myvalue with the path as '@Value'
@Value is where the start of the message appears is this why you use this?

If i want to use this as an expression within informatica power center, would this be ok or cause issues?
thank you.
slightwv (䄆 Netminder) Commented:
>>what is this?  

It is nothing more than a working stand-alone example loosely based on what you posted.  I wanted to post a tested/working example.

>> wondered if it was because my field datatype is clob and not varchar2(100),

Shouldn't matter.

>>do you always use the very last part of the xml beside the section of text you want to return?
 in your example you used "'//Out/Param'"

Not always.  It depends on the level withing the XML that the data I'm after is.  In this case, I chose what I thought was the easiest PATH given the sample data.

>>"You cannot access the application at this time.  Please try again later."

That doesn't look like an Oracle message.  Can you test from sqlplus?

>>If i want to use this as an expression within informatica power center, would this be ok or cause issues?

I don't know informatica.  It might not understand the XMLTABLE syntax.  You will need to figure out what is causing that error.

If it doesn't, you might have to create a view in Oracle that extracts the data and use that view in informatica.
PutochAuthor Commented:
Thanks so much for all the advice on this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.