?
Solved

Oracle Error  ora-19025

Posted on 2011-05-04
4
Medium Priority
?
803 Views
Last Modified: 2012-05-11
I recreated the ADAM_TEST table with the sap attribute and populated it successfully with
 extractvals.txt

I also need to extract /intervalresults/@startime and insert into each row, how would I modify my insert.   I’ve bolded what I am getting and underlined what I also need.  

I tried shortening

from table(xmlsequence(extract((select xml_data from test_table), '/kraken/profile/intervalresults/carrierevents', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"'))) s;

to

from table(xmlsequence(extract((select xml_data from test_table), '/kraken/profile/intervalresults', 'xmlns="http://www.kraken.prv.com/profiler/jaxb"'))) s;

and modifying the extractvalue to include /intervalresults/carrierevents but then I get the ora-19025

ERROR at line 40:
ORA-19025: EXTRACTVALUE returns value of only one node

Any ideas?  Thanks.

Here is the xml

SQL> select * from test_table;

<?xml version="1.0" encoding="US-ASCII" standalone='yes'?>
<kraken xmlns="http://www.kraken.prv.com/profiler/jaxb">
  <profile>
    <overview>
      <testname>Properties</testname>
      <comment>Properties Comment</comment>
      <starttime>2011-01-12 00:00:00</starttime>
      <totalruntime>24 hours</totalruntime>
      <interval>hour</interval>
    </overview>
    <totalresults>
      <HandheldRegistration>1504</HandheldRegistration>
      <DefaultRegistration>894</DefaultRegistration>

      <NonDefaultRegistration>80077</NonDefaultRegistration>
      <HandheldDeactivatedRegistration>14456</HandheldDeactivatedRegistration>
      <ActiveSuspend>104013</ActiveSuspend>
      <ActiveDeactivate>146201</ActiveDeactivate>
      <ActiveResume>77020</ActiveResume>
      <DefaultResume>6779</DefaultResume>
      <ActiveActivate>79806</ActiveActivate>
      <SimSwap>12336</SimSwap>
      <PinSwap>65723</PinSwap>
      <ActiveModify>43144</ActiveModify>
      <DeactivateToModify>16654</DeactivateToModify>
      <DefaultModify>301</DefaultModify>
      <NonDefaultModify>2536</NonDefaultModify>

      <WebSLActivate>0</WebSLActivate>
      <CaribouDevices>0</CaribouDevices>
      <CaribouLatest>0</CaribouLatest>
      <REGG>4651737</REGG>
      <REGC>5752516</REGC>
      <REGI>304504</REGI>
      <REGW>0</REGW>
     <CreateBBID>52486</CreateBBID>
      <LookUpBBID>951321</LookUpBBID>
      <DeleteBBID>13452</DeleteBBID>
      <ChangeBBID>59593</ChangeBBID>
      <BBIDSBP>227580</BBIDSBP>
      <BISNoteUpdate>0</BISNoteUpdate>

      <BISNoteMultiByte>0</BISNoteMultiByte>
      <ARI>1606158</ARI>
      <BBIDLogin>0</BBIDLogin>
      <WebLogin>0</WebLogin>
      <WebSBP>0</WebSBP>
      <WebDeviceLookUp>0</WebDeviceLookUp>
      <WebAccountLookUp>0</WebAccountLookUp>
      <WebDevDefServsAssignment>0</WebDevDefServsAssignment>
      <WebServiceLookUp>0</WebServiceLookUp>
    </totalresults>
    <intervalresults endtime="2011-01-11 00:59:59" starttime="2011-01-11 00:00:00">

      <totalevents>

        <HandheldRegistration>105</HandheldRegistration>
        <DefaultRegistration>26</DefaultRegistration>
        <NonDefaultRegistration>4998</NonDefaultRegistration>
        <HandheldDeactivatedRegistration>921</HandheldDeactivatedRegistration>
        <ActiveSuspend>3026</ActiveSuspend>
        <ActiveDeactivate>9314</ActiveDeactivate>
        <ActiveResume>3047</ActiveResume>
        <DefaultResume>490</DefaultResume>
        <ActiveActivate>5095</ActiveActivate>
        <SimSwap>864</SimSwap>
        <PinSwap>3892</PinSwap>
        <ActiveModify>2304</ActiveModify>
        <DeactivateToModify>960</DeactivateToModify>

        <DefaultModify>25</DefaultModify>
        <NonDefaultModify>137</NonDefaultModify>
        <WebSLActivate>0</WebSLActivate>
        <CaribouDevices>0</CaribouDevices>
        <CaribouLatest>0</CaribouLatest>
        <REGG>207067</REGG>
        <REGC>261837</REGC>
        <REGI>12254</REGI>
        <REGW>0</REGW>
        <CreateBBID>3117</CreateBBID>
        <LookUpBBID>46810</LookUpBBID>
        <DeleteBBID>609</DeleteBBID>
        <ChangeBBID>3152</ChangeBBID>

        <BBIDSBP>10505</BBIDSBP>
        <BISNoteUpdate>0</BISNoteUpdate>
        <BISNoteMultiByte>0</BISNoteMultiByte>
        <ARI>70177</ARI>
        <BBIDLogin>0</BBIDLogin>
       <WebLogin>0</WebLogin>
        <WebSBP>0</WebSBP>
        <WebDeviceLookUp>0</WebDeviceLookUp>
        <WebAccountLookUp>0</WebAccountLookUp>
        <WebDevDefServsAssignment>0</WebDevDefServsAssignment>
        <WebServiceLookUp>0</WebServiceLookUp>
      </totalevents>

     <carrierevents sap="0000319026">

        <HandheldRegistration>0</HandheldRegistration>
        <DefaultRegistration>0</DefaultRegistration>
        <NonDefaultRegistration>0</NonDefaultRegistration>
        <HandheldDeactivatedRegistration>0</HandheldDeactivatedRegistration>
        <ActiveSuspend>0</ActiveSuspend>
        <ActiveDeactivate>12</ActiveDeactivate>
        <ActiveResume>0</ActiveResume>
        <DefaultResume>0</DefaultResume>
        <ActiveActivate>17</ActiveActivate>
        <SimSwap>0</SimSwap>
        <PinSwap>0</PinSwap>
        <ActiveModify>0</ActiveModify>
        <DeactivateToModify>0</DeactivateToModify>

        <DefaultModify>0</DefaultModify>
        <NonDefaultModify>0</NonDefaultModify>
        <WebSLActivate>0</WebSLActivate>
        <CaribouDevices>0</CaribouDevices>
        <CaribouLatest>0</CaribouLatest>
        <REGG>85</REGG>
        <REGC>0</REGC>
        <REGI>0</REGI>
        <REGW>0</REGW>
        <CreateBBID>2</CreateBBID>
        <LookUpBBID>13</LookUpBBID>
        <DeleteBBID>0</DeleteBBID>
        <ChangeBBID>1</ChangeBBID>

        <BBIDSBP>9</BBIDSBP>
        <BISNoteUpdate>0</BISNoteUpdate>
        <BISNoteMultiByte>0</BISNoteMultiByte>
        <ARI>0</ARI>
        <BBIDLogin>0</BBIDLogin>
        <WebLogin>0</WebLogin>
        <WebSBP>0</WebSBP>
        <WebDeviceLookUp>0</WebDeviceLookUp>
        <WebAccountLookUp>0</WebAccountLookUp>
        <WebDevDefServsAssignment>0</WebDevDefServsAssignment>
        <WebServiceLookUp>0</WebServiceLookUp>
      </carrierevents>
0
Comment
Question by:farzanj
  • 2
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35691159
>>ORA-19025: EXTRACTVALUE returns value of only one node

It doesn't look like you xml is complete since I do not see the close for <kraken>

You must have repeating intervalresults nodes.
0
 
LVL 31

Author Comment

by:farzanj
ID: 35691374
When I try to get /intervalresults/@starttime by modifying the xmlsequence I get the oracle error on /intervalresult/carrierevents/*

Any thoughts how I could incorporate /intervalresults/@startime into the insert without causing the error?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35691387
Do you have repeating intervalresults nodes in the full XML?  If so, which 'starttime' do you want?
0
 
LVL 31

Author Closing Comment

by:farzanj
ID: 35704435
Appreciate your precious time.  I was helping a DBA, but he did not provide me with all the info.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

616 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