Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Query for reading XML script: Conversion from SQL Server to Oracle

860250Aug 28 2012 — edited Aug 30 2012
Hi,

I am trying to convert below SQL server query to Oracle & getting error. Please help me on this issue.

Thanks in advance.

SELECT DerivedItems.Timings.value('(ServiceName)[1]','varchar(100)'),
ROW_NUMBER() OVER(ORDER BY Timings.value('(/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData)[1]', 'varchar(100)')),
FROM @MQResponse2.nodes('/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData') as DerivedItems(Timings)

Oracle Query:

SELECT XMLTYPE.EXTRACT(VALUE(tt),'/TimingData/ServiceName/text()').getstringval(),
ROW_NUMBER() OVER(ORDER BY XMLTYPE.EXTRACT(VALUE(tt),'/AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData/text()')).getstringval()
FROM TABLE(XMLSEQUENCE(v_MQResponse2.EXTRACT('//AD/InsuranceSvcRs/PersAutoPolicyQuoteInqRs/com.tt_DerivedItems/TimingData'))) tt;

Below is the XML script

<WIP xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Status><PolicyTypeCd /><DecisioningActivity>POSTFILTER</DecisioningActivity><FilterIndicator>N</FilterIndicator><Channel /><RemovedXMLDeclarations /><LastServiceAttempted>isDeferredTransaction</LastServiceAttempted><ConverseUID>009d291c-cec0-4051-87f9-8672e38bb2cc</ConverseUID><StatusCode>0</StatusCode><Severity>INFO</Severity><StatusDescription>Logging Completed</StatusDescription><MachineName>PVDXESS029-096</MachineName><ConnectionString>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</ConnectionString><ResponseDateTime>2011-12-16T16:35:55.082</ResponseDateTime><HttpRequestTime>12/16/2011 4:03:55 PM</HttpRequestTime><timeInFLow>1908540</timeInFLow><TimeOutOfFLow>9666</TimeOutOfFLow><TotalTime>1918206</TotalTime><TotalProcessingTime>1918292</TotalProcessingTime><LookupDate>5/23/2010</LookupDate><TransactionType>RFQ1</TransactionType><State>AZ</State><AgentCD>0RV664</AgentCD><PostalCode>85250</PostalCode><County>Maricopa</County><City>Scottsdale</City><Addr1>7319 E KEIM DRIVE</Addr1><LOB>AUTOP</LOB><TransactionEffectiveDt>2012-01-28T00:00:00.00000-05:00</TransactionEffectiveDt></Status><enabledTransactions><FlowTypes><ABSBilling>0</ABSBilling><AutomaticImport>0</AutomaticImport><AutomaticRegistration>0</AutomaticRegistration><BridgeCleaner>0</BridgeCleaner><ContactView>0</ContactView><CoveragePackages>0</CoveragePackages><DoubleImport>0</DoubleImport><EFullCatalog>0</EFullCatalog><EFullfillment>0</EFullfillment><EnabledTransaction>1</EnabledTransaction><GarageTransform>1</GarageTransform><Javacaps>0</Javacaps><MSB>0</MSB><RTCC>0</RTCC><SQIW>1</SQIW><TrimEndorsements>0</TrimEndorsements><VPALS>0</VPALS><GatewayModernization>1</GatewayModernization><GatewayMILogging>1</GatewayMILogging><OpenMedia>0</OpenMedia><BoatService>0</BoatService><ScreenRouting>0</ScreenRouting><Linkage>1</Linkage><EFulfillmentCaching>1</EFulfillmentCaching><GTWLookupCaching>1</GTWLookupCaching><ErrorService>0</ErrorService><SLOLFA>0</SLOLFA><AppendLimitsAndDeductibles>0</AppendLimitsAndDeductibles><PolicyTermChk>1</PolicyTermChk><GIRO>0</GIRO><BillingServiceForDownPayHardEnforce>1</BillingServiceForDownPayHardEnforce><DownPaymentHardEnforcement>0</DownPaymentHardEnforcement><PaymentValidation>0</PaymentValidation><MonthlyPaymentForResponse>0</MonthlyPaymentForResponse><PreFiltersEnabled>0</PreFiltersEnabled><PostFiltersEnabled>1</PostFiltersEnabled><ContactViewImportOnly>0</ContactViewImportOnly><CloseQuote>0</CloseQuote><TargetApplicationRouter>1</TargetApplicationRouter></FlowTypes></enabledTransactions><gatewayServiceCalls><mqdrop><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>5755</TotalTime><Status /><Endpoint>Qmgr=TQ21;SendQ=A4218QA.PLCDE.CICTPLH2.REQUEST.GCM0;RcvQ=A4218QA.GATEWAY.GENERIC.RESPONSE;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred /><Response><![CDATA[<AD><InsuranceSvcRs><RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID><SPName>QMATIO</SPName><PersAutoPolicyQuoteInqRs><RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID><TransactionResponseDt>2011-12-16</TransactionResponseDt><com.tt_DerivedItems><AgentCd>0RV664</AgentCd><WritingCompanyCd>THM</WritingCompanyCd><MarketCd>A</MarketCd><NumericStateCd>02</NumericStateCd><EffectiveDt>2012-01-28</EffectiveDt><ExpirationDt>2013-01-28</ExpirationDt><FormCd>101</FormCd><PolicySequenceNumber>1</PolicySequenceNumber><PolicyTerm>12</PolicyTerm><TotalPremiumAmt>1329.00</TotalPremiumAmt><RatingPlan>9</RatingPlan><ReportingOffice>183</ReportingOffice><AlphaStateCd>AZ</AlphaStateCd><ProcessDt>2012-01-28 12:00:00.000</ProcessDt><PricingTier>028</PricingTier><TotalPremiumAndTaxAmt>1329.00</TotalPremiumAndTaxAmt><ProgramCd>QU</ProgramCd><OriginalQuoteDt>2011-12-15</OriginalQuoteDt><CustomerNumber>0000039176213</CustomerNumber><PricingTrack>TR028</PricingTrack><PrrInsLpseCd>N</PrrInsLpseCd><ServiceOffice>476</ServiceOffice><PremiumInfo><PremiumCd>PIF</PremiumCd><PremiumAmt>1329.00</PremiumAmt></PremiumInfo><PremiumInfo><PremiumCd>INS</PremiumCd><PremiumAmt>1400.00</PremiumAmt></PremiumInfo><TimingData><ServiceName>XDX10021-BLD-FDS:SFGA0001</ServiceName><StartTime>2011121616045588-0500</StartTime><EndTime>2011121616045600-0500</EndTime></TimingData><TimingData><ServiceName>XDX10042-TRN-FDS:SFGA0001</ServiceName><StartTime>2011121616045600-0500</StartTime><EndTime>2011121616045678-0500</EndTime></TimingData><TimingData><ServiceName>IGA1071V-ADD-GW-CUST:</ServiceName><StartTime>2011121616045638-0500</StartTime><EndTime>2011121616045644-0500</EndTime></TimingData><TimingData><ServiceName>IOE1023V-SUMMARY-REQUEST</ServiceName><StartTime>2011121616045658-0500</StartTime><EndTime>2011121616045659-0500</EndTime></TimingData><TimingData><ServiceName>IOE1022V-CRDT-REQ-CK</ServiceName><StartTime>2011121616045659-0500</StartTime><EndTime>2011121616045662-0500</EndTime></TimingData><TimingData><ServiceName>IOE1002V-REPORT-VIEW-S</ServiceName><StartTime>2011121616045662-0500</StartTime><EndTime>2011121616045662-0500</EndTime></TimingData><TimingData><ServiceName>XDX10092-MAP-FDS:SFGA0001</ServiceName><StartTime>2011121616045678-0500</StartTime><EndTime>2011121616045682-0500</EndTime></TimingData><TimingData><ServiceName>M3172F00-CALLCARS:PRICE</ServiceName><StartTime>2011121616045685-0500</StartTime><EndTime>2011121616045812-0500</EndTime></TimingData><TimingData><ServiceName>M3172F00-CALLCARS:QUOTE</ServiceName><StartTime>2011121616045814-0500</StartTime><EndTime>2011121616045892-0500</EndTime></TimingData><TimingData><ServiceName>XDX10021-BLD-FDS:SFGA0002</ServiceName><StartTime>2011121616045893-0500</StartTime><EndTime>2011121616045914-0500</EndTime></TimingData><TimingData><ServiceName>XDX10042-TRN-FDS:SFGA0002</ServiceName><StartTime>2011121616045914-0500</StartTime></TimingData><PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp><PolicyProcessingEvent>POST RATING</PolicyProcessingEvent><ThirdPartyReports><CategoryCode>CREDIT</CategoryCode><InstanceId>324876227541053</InstanceId></ThirdPartyReports><AlphaInsuranceScore>AANH</AlphaInsuranceScore><InsuranceScoreSourceCd>T</InsuranceScoreSourceCd></com.tt_DerivedItems><PersPolicy><LOBCd>AUTOP</LOBCd><ContractTerm><EffectiveDt>2012-01-28</EffectiveDt><DurationPeriod><NumUnits>12</NumUnits></DurationPeriod></ContractTerm><CreditScoreInfo><CSSourceCd>T</CSSourceCd></CreditScoreInfo><QuoteInfo><CompanysQuoteNumber>1387853026</CompanysQuoteNumber><com.tt_CustomerNumber>0000039176213</com.tt_CustomerNumber><com.tt_CICSRegion>CICTPLH2</com.tt_CICSRegion><com.tt_IFSTokenId>223207027541053</com.tt_IFSTokenId><com.tt_IFSVendorCd>DMS</com.tt_IFSVendorCd></QuoteInfo></PersPolicy><PersAutoLineBusiness><LOBCd>AUTOP</LOBCd><Coverage><CoverageDesc>Safe Driver Advantage</CoverageDesc><CreditOrSurcharge id="CS291762915061053"><CreditSurchargeCd>com.tt_SafeDriverAdvantage</CreditSurchargeCd></CreditOrSurcharge></Coverage><Coverage><CoverageDesc>Early Quote Advantage</CoverageDesc><CreditOrSurcharge id="CS514762915061053"><CreditSurchargeCd>com.tt_EarlyQuoteAdvantage</CreditSurchargeCd></CreditOrSurcharge></Coverage><PersVeh id="VEH1"><Manufacturer>NISSA</Manufacturer><Model>SENTRA E/X</Model><ModelYear>1994</ModelYear><VehIdentificationNumber>1N4EB31F6RC817270</VehIdentificationNumber><Coverage><CoverageCd>PD</CoverageCd><CurrentTermAmt><Amt>283.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>BI</CoverageCd><CurrentTermAmt><Amt>469.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>MEDPM</CoverageCd><CurrentTermAmt><Amt>126.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>UM</CoverageCd><CurrentTermAmt><Amt>91.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>UNDUM</CoverageCd><CurrentTermAmt><Amt>40.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>COMP</CoverageCd><CurrentTermAmt><Amt>185.00</Amt></CurrentTermAmt></Coverage><Coverage><CoverageCd>COLL</CoverageCd><CurrentTermAmt><Amt>135.00</Amt></CurrentTermAmt></Coverage></PersVeh></PersAutoLineBusiness><PolicySummaryInfo><PolicyStatusCd>QUOTEDNOTBOUND</PolicyStatusCd><FullTermAmt><Amt>1329.00</Amt></FullTermAmt><com.tt_AlternatePremium>1400.00</com.tt_AlternatePremium><com.tt_PrimaryPremiumCd>PIF</com.tt_PrimaryPremiumCd><com.tt_AlternatePremiumCd>FULL</com.tt_AlternatePremiumCd></PolicySummaryInfo><MsgStatus><MsgStatusCd>SUCCESS</MsgStatusCd></MsgStatus><RemarkText id="MSG001" IdRef="MSG001"><ProbLog>NO TEXT FOUND FOR MESSAGE CODE</ProbLog><ProbCode>000000306</ProbCode></RemarkText></PersAutoPolicyQuoteInqRs></InsuranceSvcRs></AD>]]></Response><TotalMQSendTime>0</TotalMQSendTime><TotalMQReceTime>0</TotalMQReceTime></mqdrop><autoRegistration><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1:21071/authorizedconnectionservices.asmx</Endpoint><Primary_deferred /><Response>false</Response></autoRegistration><absEmail><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Primary_deferred /></absEmail><msb><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:20206/RequestMsbWebService.asmx</Endpoint><Primary_deferred /><ReplacedCoverageA /><ReplacementCost /></msb><wsRouter><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1jas-strat.dvllb.travp.net/RouterWS/services/RouterWS</Endpoint><Primary_deferred /><RouterCallType>CACHED</RouterCallType></wsRouter><javacapsCall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1piservices.prodlb.travp.net/plGatewayAuto/ExecutePortType</Endpoint><Primary_deferred /></javacapsCall><rtccCall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://int1-strat-bs.dvllb.travp.net/BillingWS/services/BillingWS</Endpoint><Primary_deferred /><Response /><BrokerTransactionID /><BrokerName /><InternalTransactionID /></rtccCall><tranManLogRequest><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354261449</startTime><endTime>354261450</endTime><TotalTime>1</TotalTime><Status>SUCCESS</Status><Endpoint>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</Endpoint><Primary_deferred>Deferred</Primary_deferred></tranManLogRequest><tranManIsEnabled><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354261460</startTime><endTime>354261745</endTime><TotalTime>285</TotalTime><Status>SUCCESS</Status><Endpoint>Server=PLEXTRN-DV.prodlb.travp.net,40005;Database=PLGatewayA;</Endpoint><Primary_deferred>Primary</Primary_deferred><FromCache>0</FromCache></tranManIsEnabled><vpals><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration1piservices.prodlb.travp.net/VPALSService/services/VPALSService</Endpoint><Primary_deferred /></vpals><bridgeCleaning><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></bridgeCleaning><XMLValidation><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262334</startTime><endTime>354262899</endTime><TotalTime>565</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred></XMLValidation><coveragePackages><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></coveragePackages><sqiwcall><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356179577</startTime><endTime>356179590</endTime><TotalTime>13</TotalTime><Status>SUCCESS</Status><Endpoint>Qmgr=TQ21;SendQ=A4218QA.GATEWAY.QUOTE.TO.QIW;RcvQ=A4218QA.GATEWAY.QUOTE.TO.QIW;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred>Deferred</Primary_deferred><SqiwXML><![CDATA[<c6770gwy-gwy-mq-rcd><gwy-h-timestamp><gwy-h-date>2011-12-16</gwy-h-date><gwy-h-time>16:35:55.8328750</gwy-h-time></gwy-h-timestamp><gwy-h-lob>AUTO</gwy-h-lob><gwy-h-origination-code>GA</gwy-h-origination-code><hdr-filler></hdr-filler><bus-cntr-cust-num-nme>0000039176213</bus-cntr-cust-num-nme><account-pol></account-pol><form-pol>101</form-pol><seq-pol>1</seq-pol><vehst-alpha-pol>AZ</vehst-alpha-pol><control-agent-pol>0RV664</control-agent-pol><source-partner-nm>COM.QUOMATION</source-partner-nm><quote-origin>CallCenter</quote-origin><package-id></package-id><package-name></package-name><signon-role-cd>RFQ1</signon-role-cd><PLWQ_QueWriteTmstp>2011-12-16-16.04.58.934241</PLWQ_QueWriteTmstp><user-id></user-id><session-id>1387853026</session-id><request-id>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</request-id><iscr-token-id>223207027541053</iscr-token-id><mvr-token-id></mvr-token-id><converse-user-id>009d291c-cec0-4051-87f9-8672e38bb2cc</converse-user-id><consent-to-ordr-ind>1</consent-to-ordr-ind><incident-usage-cd></incident-usage-cd><prr-pol-exp-dt>2012-01-28</prr-pol-exp-dt><call-flow-type></call-flow-type><email-addr>ABI@ABC.COM</email-addr><machine-id></machine-id><channel-id></channel-id><monthly-premium></monthly-premium><fltr-ind>N</fltr-ind><decn-act-cd>POSTFILTER</decn-act-cd><trm-eff-dt>2012-01-28</trm-eff-dt><data-filler></data-filler></c6770gwy-gwy-mq-rcd>]]></SqiwXML><DoubleRateSqiwXML><![CDATA[]]></DoubleRateSqiwXML></sqiwcall><efullpackagecatalog><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration2piw.prodlb.travp.net/Efulfillment/EfulfillmentServices</Endpoint><Primary_deferred /><autoIDCardInd /><eSignInd /><Code /><Desc /><CallingApp /><SPName /><FromCache /></efullpackagecatalog><efullprocess><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integration2piw.prodlb.travp.net/Efulfillment/EfulfillmentServices</Endpoint><Primary_deferred /><Code /><Desc /><AgentCd /><FormCode /></efullprocess><garageTransform><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262327</startTime><endTime>354262331</endTime><TotalTime>4</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred></garageTransform><CRDBaddProspect><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:21071/crdbservices.asmx</Endpoint><Primary_deferred /></CRDBaddProspect><CVDBimportQuote><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theadaw1.dev.prod.travp.net:20204/ContactViewServices.asmx</Endpoint><Primary_deferred /><AccountCode /><UserId /></CVDBimportQuote><Milsubmitter><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://integrationsiw.prodlb.travp.net/MILSubmitterWeb/services/InteractionSubmitter</Endpoint><Primary_deferred /></Milsubmitter><VRUCommonUtil><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://theacaw1.dev.prod.travp.net:20208/GenesysConfig.asmx</Endpoint><Primary_deferred /></VRUCommonUtil><EmailService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></EmailService><boatService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint>http://THEASAW1.dev.prod.travp.net:21090/GRateSrv.asmx</Endpoint><Primary_deferred /></boatService><linkageService><ErrorInfo>2058:Queue manager name not valid or not known.</ErrorInfo><startTime>356179593</startTime><endTime>356179598</endTime><TotalTime>5</TotalTime><Status>ERROR</Status><Endpoint>Qmgr=*PIMIT;SendQ=A3105QA.PIMI.RT2MI.DEV;RcvQ=A3105QA.PIMI.RT2MI.DEV;WaitTime=60;ExpiryTime=300</Endpoint><Primary_deferred>Deferred</Primary_deferred><Request><![CDATA[<PIPolicy><messageHeader><messageId>POST RATING2011-12-16T16.04.58.934241-05:00</messageId><messageWriteDate>2011-12-16T16:35:55.848500-05:00</messageWriteDate></messageHeader><applicationInfo><sourceApplicationCd>GTW</sourceApplicationCd><transactionType>NBQT</transactionType><transactionDate>2011-12-16T16.04.58.934241-05:00</transactionDate><policyProcessingEvent>POST RATING</policyProcessingEvent></applicationInfo><producerInfo><reportingOfficeCd>183</reportingOfficeCd></producerInfo><personalPolicy><policyAccountInfo><customer><customerNumber>0000039176213</customerNumber></customer><policyContract><policyEffectiveDate>2012-01-28</policyEffectiveDate><policyExpirationDate>2013-01-28</policyExpirationDate></policyContract></policyAccountInfo><productInfo><ratingStateCd>AZ</ratingStateCd><lineOfBusiness>AUTO</lineOfBusiness><policyFormCd>101</policyFormCd><ratingPlan>9</ratingPlan></productInfo><policyInfo></policyInfo></personalPolicy><thirdPartyData><thirdPartyReports><report><categoryCd>CREDIT</categoryCd><requestTokenId>223207027541053</requestTokenId><reportInstanceId>324876227541053</reportInstanceId></report></thirdPartyReports></thirdPartyData></PIPolicy>]]></Request><DoubleRateXml><![CDATA[]]></DoubleRateXml><LinkageCall>1</LinkageCall></linkageService><errorService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status></Status><Endpoint>http://unitjas.prodlb.travp.net/UserRegistration/services/trav.ws</Endpoint><Primary_deferred></Primary_deferred></errorService><downPayHardEnforcement><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356172264</startTime><endTime>356178325</endTime><TotalTime>6061</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred><DatabaseReadWriteTime>0</DatabaseReadWriteTime></downPayHardEnforcement><billingService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>356173783</startTime><endTime>356177694</endTime><TotalTime>3911</TotalTime><Status>SUCCESS</Status><Endpoint>http://unit3jas.prodlb.travp.net/BillingService/services/trav.ws</Endpoint><Primary_deferred>Primary</Primary_deferred></billingService><commandOrchestrator><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></commandOrchestrator><coveragePackageService><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></coveragePackageService><creditCardValidationsvc><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></creditCardValidationsvc><bankRouteNumValidationsvc><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></bankRouteNumValidationsvc><filtersOrchestration><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>354262921</startTime><endTime>356172262</endTime><TotalTime>1909341</TotalTime><Status>SUCCESS</Status><Endpoint /><Primary_deferred>Primary</Primary_deferred><CustomerLookupCustomer /><HostCustomer>0000039176213</HostCustomer><FilterIndicator>N</FilterIndicator><DecisioningActivity>POSTFILTER</DecisioningActivity></filtersOrchestration><importByPass><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></importByPass><importSystem><ErrorInfo><![CDATA[]]></ErrorInfo><startTime>0</startTime><endTime>0</endTime><TotalTime>0</TotalTime><Status /><Endpoint /><Primary_deferred /></importSystem></gatewayServiceCalls><FinalAD><![CDATA[<AD><SignonRq><SignonTransport><SignonRoleCd>RFQ1</SignonRoleCd><CustId><SPName>COM.QUOMATION</SPName><CustPermId>0a0165</CustPermId><CustLoginId>CallCenter</CustLoginId><com.tt_ToolUserId>PROD\gmyers</com.tt_ToolUserId></CustId></SignonTransport><ClientDt>2011-12-15</ClientDt><CustLangPref>English</CustLangPref><ClientApp><Org>CALLCENTER</Org><Name>QUOMATION.COM</Name><Version>11.11.1</Version></ClientApp></SignonRq><InsuranceSvcRq><com.quomation_TransactionId>1980624D-2015-4D2D-9694-31A9DDD0D1D3</com.quomation_TransactionId><RqUID>847C9B80-953A-4867-8304-5A2E0F05E10E</RqUID><PersAutoPolicyQuoteInqRq><RqUID>EC086D43-756B-4F45-A7F1-D9EF99B9CAC8</RqUID><TransactionRequestDt>2012-01-28T00:00:00.00000-05:00</TransactionRequestDt><TransactionEffectiveDt>2012-01-28T00:00:00.00000-05:00</TransactionEffectiveDt><com.tt_OriginalQuoteDt>2011-12-15</com.tt_OriginalQuoteDt><CurCd>USAD</CurCd><Producer><ProducerInfo><ContractNumber>0RV664</ContractNumber></ProducerInfo></Producer><InsuredOrPrincipal id="FNI"><GeneralPartyInfo><NameInfo><PersonName><Surname>TESTING</Surname><GivenName>TESTAZ</GivenName></PersonName></NameInfo><Communications><PhoneInfo><CommunicationUseCd>Home</CommunicationUseCd><PhoneNumber>+1-520-9475471</PhoneNumber></PhoneInfo><EmailInfo><EmailAddr>ABI@ABC.COM</EmailAddr></EmailInfo></Communications></GeneralPartyInfo></InsuredOrPrincipal><PersPolicy><LOBCd>AUTOP</LOBCd><CreditScoreInfo><com.tt_CreditConsentToOrderInd>1</com.tt_CreditConsentToOrderInd></CreditScoreInfo><ContractTerm><EffectiveDt>2011-12-25</EffectiveDt><DurationPeriod><NumUnits>12</NumUnits><UnitMeasurementCd>Months</UnitMeasurementCd></DurationPeriod></ContractTerm><OtherOrPriorPolicy><PolicyCd>Prior</PolicyCd><LOBCd>AUTOP</LOBCd><NAICCd>12963</NAICCd><ContractTerm><ExpirationDt>2012-01-28</ExpirationDt></ContractTerm><LengthTimeWithPreviousInsurer><NumUnits>48</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeWithPreviousInsurer><Coverage><CoverageCd>BI</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><com.tt_6_Mons_Continuous_Cov>Y</com.tt_6_Mons_Continuous_Cov></OtherOrPriorPolicy><QuoteInfo><CompanysQuoteNumber>1387853026</CompanysQuoteNumber><com.tt_MachineName>127.0.0.1</com.tt_MachineName><com.tt_RemoteMachineName>127.0.0.1</com.tt_RemoteMachineName><com.tt_RequestTime>2011-12-16T16:03:55.567</com.tt_RequestTime><com.tt_Channel></com.tt_Channel><com.tt_FormCode>101</com.tt_FormCode><com.tt_PolicyState>AZ</com.tt_PolicyState><com.tt_BookTransferIndicator></com.tt_BookTransferIndicator><com.tt_AutomatedImportIndicator></com.tt_AutomatedImportIndicator><com.tt_RegistrationWorkgroupCode></com.tt_RegistrationWorkgroupCode><com.tt_ConverseUid>009d291c-cec0-4051-87f9-8672e38bb2cc</com.tt_ConverseUid><com.tt_TestTransactionCycleDate>2012-01-28T00:00:00.00000-05:00</com.tt_TestTransactionCycleDate><com.tt_TransactionTypeCd>RFQ1</com.tt_TransactionTypeCd></QuoteInfo><QuestionAnswer><QuestionCd>ContinuouslyInsured</QuestionCd><YesNoCd>Y</YesNoCd><Explanation></Explanation></QuestionAnswer><QuestionAnswer><QuestionCd>VehiclePriorDamage</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><QuestionAnswer><QuestionCd>PE01</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><PersApplicationInfo><InsuredOrPrincipal><GeneralPartyInfo><Addr><AddrTypeCd>StreetAddress</AddrTypeCd><Addr1>7319 E KEIM DRIVE</Addr1><City>Scottsdale</City><StateProvCd>AZ</StateProvCd><PostalCode>85250</PostalCode><County>Maricopa</County></Addr><Addr><AddrTypeCd>MailingAddress</AddrTypeCd><Addr1>4321 Monsoon Trail</Addr1><City>Tucson</City><StateProvCd>AZ</StateProvCd><PostalCode>85749</PostalCode><County>Pima</County></Addr><Addr><AddrTypeCd>PreviousAddress</AddrTypeCd><Addr1>1615 N DELAWARE DR 88</Addr1><Addr2></Addr2><City>APACHE JUNCTION</City><StateProvCd>AZ</StateProvCd><PostalCode>85220</PostalCode></Addr></GeneralPartyInfo></InsuredOrPrincipal><ResidenceTypeCd>DW</ResidenceTypeCd><ResidenceOwnedRentedCd>Y</ResidenceOwnedRentedCd></PersApplicationInfo><DriverVeh DriverRef="Drv1" VehRef="Veh1"><UsePct>100</UsePct></DriverVeh></PersPolicy><PersAutoLineBusiness><LOBCd>AUTOP</LOBCd><PersDriver id="Drv1"><GeneralPartyInfo><NameInfo><PersonName><Surname>TESTING</Surname><GivenName>TESTAZ</GivenName><NameSuffix></NameSuffix></PersonName><TaxIdentity><TaxIdTypeCd>SSN</TaxIdTypeCd><TaxId>#####1111</TaxId></TaxIdentity></NameInfo></GeneralPartyInfo><DriverInfo><PersonInfo><GenderCd>M</GenderCd><BirthDt>1987-07-07</BirthDt><MaritalStatusCd>M</MaritalStatusCd><LengthTimeEmployed><NumUnits>52</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeEmployed><LengthTimeCurrentOccupation><NumUnits>52</NumUnits><UnitMeasurementCd>M</UnitMeasurementCd></LengthTimeCurrentOccupation><OccupationClassCd>EXE</OccupationClassCd></PersonInfo><License><StateProvCd>AZ</StateProvCd><LicensedDt>2004-04-07</LicensedDt><LicensePermitNumber>A11111111</LicensePermitNumber></License><QuestionAnswer><QuestionCd>ForeignLicense</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer><QuestionAnswer><QuestionCd>PE40</QuestionCd><YesNoCd>N</YesNoCd><Explanation>Valid</Explanation></QuestionAnswer><QuestionAnswer><QuestionCd>PE24</QuestionCd><YesNoCd>N</YesNoCd></QuestionAnswer></DriverInfo><PersDriverInfo><DriverRelationshipToApplicantCd>IN</DriverRelationshipToApplicantCd><DistantStudentInd>N</DistantStudentInd><FinancialResponsibilityFiling><FilingCd>N</FilingCd></FinancialResponsibilityFiling><GoodStudentCd>N</GoodStudentCd></PersDriverInfo></PersDriver><PersVeh id="Veh1"><Manufacturer>NISSAN</Manufacturer><Model>SENTRA E/XE/SE/GXE 1.6L</Model><ModelYear>1994</ModelYear><VehBodyTypeCd>PP</VehBodyTypeCd><CostNewAmt><Amt>12299</Amt></CostNewAmt><Registration><StateProvCd>AZ</StateProvCd></Registration><EstimatedAnnualDistance><NumUnits>17000</NumUnits><UnitMeasurementCd>Miles</UnitMeasurementCd></EstimatedAnnualDistance><VehIdentificationNumber>1N4EB31F6RC817270</VehIdentificationNumber><VehSymbolCd>11</VehSymbolCd><AntiLockBrakeCd>N</AntiLockBrakeCd><GaragingCd>G</GaragingCd><OdometerReading><NumUnits>289000</NumUnits><UnitMeasurementCd>Miles</UnitMeasurementCd></OdometerReading><VehUseCd>PL</VehUseCd><SeatBeltTypeCd>Active</SeatBeltTypeCd><AirBagTypeCd>Driver</AirBagTypeCd><AdditionalInterest><AdditionalInterestInfo><NatureInterestCd>LOSSP</NatureInterestCd></AdditionalInterestInfo><GeneralPartyInfo><NameInfo><CommlName><CommercialName>ALBERT</CommercialName></CommlName></NameInfo><Addr><Addr1>5465 HEW WOOD</Addr1><City>SALT LAKE CITY</City><StateProvCd>UT</StateProvCd><PostalCode>84118</PostalCode></Addr></GeneralPartyInfo></AdditionalInterest><Coverage><CoverageCd>BI</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>PD</CoverageCd><Limit><FormatCurrencyAmt><Amt>15000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PropDam</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>MEDPM</CoverageCd><Limit><FormatCurrencyAmt><Amt>2000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>UM</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>UNDUM</CoverageCd><Limit><FormatCurrencyAmt><Amt>25000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerPerson</LimitAppliesToCd></Limit><Limit><FormatCurrencyAmt><Amt>50000</Amt></FormatCurrencyAmt><LimitAppliesToCd>PerAcc</LimitAppliesToCd></Limit></Coverage><Coverage><CoverageCd>COMP</CoverageCd><Deductible><FormatCurrencyAmt><Amt>ACV</Amt></FormatCurrencyAmt><DeductibleTypeCd>USAD</DeductibleTypeCd><DeductibleAppliesToCd>AllPeril</DeductibleAppliesToCd></Deductible></Coverage><Coverage><CoverageCd>COLL</CoverageCd><Deductible><FormatCurrencyAmt><Amt>500</Amt></FormatCurrencyAmt><DeductibleTypeCd>USAD</DeductibleTypeCd><DeductibleAppliesToCd>AllPeril</DeductibleAppliesToCd></Deductible></Coverage><VehicleLeasedCd>F</VehicleLeasedCd></PersVeh><NumLicensed>1</NumLicensed></PersAutoLineBusiness></PersAutoPolicyQuoteInqRq></InsuranceSvcRq></AD>]]></FinalAD></WIP>

Edited by: 857247 on Aug 28, 2012 7:00 PM
This post has been answered by AlexAnd on Aug 29 2012
Jump to Answer

Comments

EdStevens 
FC60 wrote:
Some months ago, I search informations about the old tool (Designer vs. SQL Developer ).
Now the company wants to change Oracle Designer to modeling database with a new tool. 
It's sufficient to use SQL Developer Data Modeler or is better to buy Enterprise Architect or Power Architect or others software?
Thanks for all suggestions

"Better", like beauty, is in the eye of the beholder.

What criteria does YOUR organization use to define "Better"?  Exactly how do you expect to  use the selected tool?  Who will be using it (end users, DBAs, application developers?).

FC60

Thanks Ed,

  1. I'm developer and data architect - I create a logical model for a table, I modify a table, I rename, I delete (table or columns) and I use only Designer (Entity Relationship Diagrammer)
  2. I call the DBA - He creates the script(s) from Designer

I know (they said to me) that with Designer they can to create some reports to have the also documentation on the attributes if we describes all our columns in the write way.

unknown-7404
Answer 
It's sufficient to use SQL Developer Data Modeler or is better to buy Enterprise Architect or Power Architect or others software?

Your org should do what many other companies do:

1. write a requirements doc - it should include a list of all functionality you want in a product and a rating for each item of how desirable/necessary that item is

2. acquire copies of both products - review the products and the documentation

3. have people from each company, if possible, make a presentation for their product where you can ask questions.

4. actually evaluate each product and have the evaluators mark each item in the requirements doc with a rating as to how each product actually performed as related to that requirement item.

5. meet and discuss the results of the above process

In other words - your org needs to make an INFORMED decision AFTER actually using each product.

Marked as Answer by FC60 · Sep 27 2020
1 person found this helpful
GregV

Hi,

It depends how much you want to be able to do. I decided to use SQL Developer Data Modeler for my DB modeling because it's fairly easy to use and is free. It can hold both logical and physical models, so I can store (almost) all my application objects in a Design. I say almost because there are a few objet types missing: Scheduler objects (programs, jobs, etc.), materialized view logs, system triggers. The workaround is to embed them in a fake pl/sql unit but I wish there were a real support. Apart form that it's quite functional, with a SVN built-in plugin.

You can create reports showing the tables and their columns, along with their descriptions.

Since it's free there's no official support. You can use this community for help. You can submit bugs and ER through SRs but that seems to be a facade. Nothing really moves. I don't know how it works with the Feature Exchange place. I submitted a couple of things there, but there's no follow up. For example:

https://apex.oracle.com/pls/apex/f?p=43135:7:::NO:RP,7:P7_ID:44141

You'll notice there's a score of 10 for this one.

1 person found this helpful
FC60

GregV,

For me SQL Developer Data Modeler it's sufficient, the DBAs have also Toad to manage the DBs.

As you say, it's free, but not supported? Sometime the supported sw are less supported than free tools. We pay Hitachi to support Pentaho, but the last two questions don't have answers...No comment...

Finally, I have found an old DBA that knows Designer very well and he has said to me that one of things that we need to verify is if SQL Developer Data Modeler can create an "Alter table..." script when I add a column in my Data Modeler file.

Another thing is if I can share an entity between two schemas (always in Data Modeler).

Thanks.

This community is great, sometime we find arrogant people , but my grandmother said: Do not look gift horses in the mouth!

Thanks

thatJeffSmith-Oracle

>>Since it's free there's no official support. You can use this community for help
NOT TRUE.

If you have a database under contract with My Oracle Support, then SQL Developer SQL Developer Data Modeler, SQLcl, and ORDS are all covered. Same deal with APEX.

1 person found this helpful
thatJeffSmith-Oracle

We have no plans to build a CLI for the Data Modeler. I've left the request open vs rejecting it for the moment.

1 person found this helpful
thatJeffSmith-Oracle

Finally, I have found an old DBA that knows Designer very well and he has said to me that one of things that we need to verify is if SQL Developer Data Modeler can create an "Alter table..." script when I add a column in my Data Modeler file.

Another thing is if I can share an entity between two schemas (always in Data Modeler).

Thanks.

Yes - alter scripts https://www.thatjeffsmith.com/archive/2012/03/diffs-and-alter-scripts-via-oracle-sql-developer-data-modeler/

and

No - shared entitys, one table in two schemas.

A table belongs to a single user in a model.

You can have a linked objects between two models.

1 person found this helpful
unknown-7404 
No - shared entitys, one table in two schemas.
A table belongs to a single user in a model.
You can have a linked objects between two models.

Presumably that will change somewhat if you plan to support the Application Container functionality of 12.2?

thatJeffSmith-Oracle

I don't know of any multitenant support plans for the modeler - probably better a question for the modeler forum though

1 person found this helpful
FC60

Thank you all for your answers, I resume and I forward to my project manager.

GregV 
thatJeffSmith-Oracle wrote:
If you have a database under contract with My Oracle Support, then SQL Developer SQL Developer Data Modeler, SQLcl, and ORDS are all covered. Same deal with APEX.

Yes, theorically. I submitted an SR about a bug regarding the Unit Test feature of SQL Developer. I even started a thread about it:

You said that time the fix was not likely to be implemented. Unfortunately I can't access the SR anymore since I moved to another company. I guess the SR died out. So yes there's support, but I think only critical issues are dealt with, and those issues are reported in this community first most probably.

Regarding the ER about having the CLI, that time a person from Oracle support called me and told me it would be implemented in the next version (that was about 2 years ago). Well, I know now he was just lying and wanted to close the SR.

1 person found this helpful
thatJeffSmith-Oracle

I'm happy to look at any SR you want. We have a dedicated support team for these products and they take many cases a day.

If someone told you a feature was coming in a particular time frame, that was not good. We don't promise features, we don't give release dates as a corporate policy.

But I will tell you if i can whether it's likely a feature will happen or not. There are no plans for a CLI on the modeler today.

1 person found this helpful
FC60

Hello Jeff,

Probably SDDM will be our futur tool to data modeling. I'm searching info about "...You can have a linked objects between two models..." can you say to me if I can find something in you blog ThatJeffSmith? To have more information.

Thanks

thatJeffSmith-Oracle

sure thing

https://www.thatjeffsmith.com/archive/2012/11/sql-developer-data-modeler-v3-3-early-adopter-link-model-objects-across-designs/

1 person found this helpful
jflack

One advantage of going from Oracle Designer to Oracle SQL Developer Data Modeler (SDDM) is that SDDM can import a logical design from a Designer Repository.  It can't import everything from the repository, but some is better than none.

1 person found this helpful
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 27 2012
Added on Aug 28 2012
#pl-sql-xml-programming, #xml
8 comments
335 views