We help IT Professionals succeed at work.

Oracle - subst Instr

Putoch
Putoch asked
on
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,
Comment
Watch Question

Top Expert 2013

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2019
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

Author

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

Author

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.
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Author

Commented:
Thanks so much for all the advice on this.