Skip to Main Content
Forums

SQL & PL/SQL

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

QUERY XML Data from Oracle Database

Oracle ManiacNov 8 2017 — edited Nov 13 2017

Hi Team

I have a requirement wherein i need to pull out few information from an xml file loacated on a  remote Machine. I preferred to load the XML file

into Oracle Table (xmltype) column and then query the XML file from that table. Though the select from my table is too slow only for 5 rows.

Format of Output Data Display: A typical hierarchy can have upto 5 substances, so we can have

medicinalproduct           Activesubstance1        Activesubstance2         Activesubstance3       Activesubstance4    Activesubstance5

Drug1                                 Substance1             Substance2                 NUll                                NUll                              NUll

Sample Data:

       <drug>

            <drugcharacterization>1</drugcharacterization> ----------------------------------**

            <medicinalproduct>Drug1</medicinalproduct> ---------------------------------**

            <obtaindrugcountry></obtaindrugcountry>

            <drugbatchnumb>123</drugbatchnumb>

            <drugauthorizationnumb>123</drugauthorizationnumb>

            <drugauthorizationcountry></drugauthorizationcountry>

            <drugauthorizationholder>Spare MAH</drugauthorizationholder>

            <drugstructuredosagenumb></drugstructuredosagenumb>

            <drugstructuredosageunit></drugstructuredosageunit>

            <drugseparatedosagenumb></drugseparatedosagenumb>

            <drugintervaldosageunitnumb></drugintervaldosageunitnumb>

            <drugintervaldosagedefinition></drugintervaldosagedefinition>

            <drugdosagetext></drugdosagetext>

            <drugdosageform></drugdosageform>

            <actiondrug></actiondrug>

            <drugrecurreadministration></drugrecurreadministration>

            <drugadditional></drugadditional>

            <activesubstance>

               <activesubstancename>Substance1</activesubstancename>  ---------------------------------**

            </activesubstance>

           <activesubstance>

               <activesubstancename>Substance2</activesubstancename>  ---------------------------------**

            </activesubstance>

            <drugreactionrelatedness>

               <drugreactionassesmeddraversion>18.1</drugreactionassesmeddraversion>

               <drugreactionasses>10007515</drugreactionasses>

               <drugassessmentsource>2</drugassessmentsource>

               <drugassessmentmethod>EVCTM</drugassessmentmethod>

               <drugresult>1</drugresult>

            </drugreactionrelatedness>

         </drug>

       <drug>

            <drugcharacterization>2</drugcharacterization>

            <medicinalproduct>Drug2</medicinalproduct>

            <obtaindrugcountry></obtaindrugcountry>

            <drugbatchnumb>123</drugbatchnumb>

            <drugauthorizationnumb>123</drugauthorizationnumb>

            <drugauthorizationcountry></drugauthorizationcountry>

            <drugauthorizationholder>Spare MAH</drugauthorizationholder>

            <drugstructuredosagenumb></drugstructuredosagenumb>

            <drugstructuredosageunit></drugstructuredosageunit>

            <drugseparatedosagenumb></drugseparatedosagenumb>

            <drugintervaldosageunitnumb></drugintervaldosageunitnumb>

            <drugintervaldosagedefinition></drugintervaldosagedefinition>

            <drugdosagetext></drugdosagetext>

            <drugdosageform></drugdosageform>

            <actiondrug></actiondrug>

            <drugrecurreadministration></drugrecurreadministration>

            <drugadditional></drugadditional>

            <activesubstance>

               <activesubstancename>Substance3</activesubstancename>

            </activesubstance>

<activesubstance>

               <activesubstancename>Substance4/activesubstancename>

            </activesubstance>

            <drugreactionrelatedness>

               <drugreactionassesmeddraversion>18.1</drugreactionassesmeddraversion>

               <drugreactionasses>10007515</drugreactionasses>

               <drugassessmentsource>2</drugassessmentsource>

               <drugassessmentmethod>EVCTM</drugassessmentmethod>

               <drugresult>1</drugresult>

            </drugreactionrelatedness>

         </drug>
This post has been answered by Paulzip on Nov 8 2017
Jump to Answer

Comments

Amith Y
In the configuration page where you provide SMTP server and port number details, you will need to use the mail server of your organization instead of using gmail and yahoo.com. You will need to setup your own mail server if you dont have one.
user8007837
Hi Amit,

Thanks for your response. I have tried my organization SMTP server as well and getting the same error. But in theory it should work with any valid SAMP sever.

See the example below,which worked with gmail.

http://krishnasalapati.blogspot.com/2011/02/ibot-mail-configuration-for-obiee-11g.html

Cheers,
Jorge

Edited by: user8007837 on 26-Apr-2011 10:13
Amith Y
Did you try to use the port # 25 with gmail server?

Regards,
-Amith.
user8007837
Yes I did.

Thanks

Updated:

I remember I was getting different error, so I tested with port # 25 again. this is the error I am getting 
Eventually succeeded, but encountered and resolved errors...
    Number of skipped recipients: 2 of 3

    AgentID: /users/jorge/UsageTrackingAgent
[nQSError: 75005] Failed to send AUTH command. SMTP server does not support any authentication mechanisms.  Remove email credentials, or use a server which supports authentication.    AgentID: /users/jorge/UsageTrackingAgent
    ...Trying SMTP Delivery loop again... Sleeping for 3 seconds.    AgentID: /users/jorge/UsageTrackingAgent
[nQSError: 75005] Failed to send AUTH command. SMTP server does not support any authentication mechanisms.  Remove email credentials, or use a server which supports authentication.    AgentID: /users/jorge/UsageTrackingAgent
    ...Trying SMTP Delivery loop again... Sleeping for 5 seconds.    AgentID: /users/jorge/UsageTrackingAgent
[nQSError: 75005] Failed to send AUTH command. SMTP server does not support any authentication mechanisms.  Remove email credentials, or use a server which supports authentication.    AgentID: /users/jorge/UsageTrackingAgent
    ...Trying SMTP Delivery loop again... Sleeping for 8 seconds.    AgentID: /users/jorge/UsageTrackingAgent
[nQSError: 75005] Failed to send AUTH command. SMTP server does not support any authentication mechanisms.  Remove email credentials, or use a server which supports authentication.    AgentID: /users/jorge/UsageTrackingAgent
    Exceeded number of SMTP delivery retries.
Edited by: user8007837 on 26-Apr-2011 10:33
Amith Y
Jorge,

Please refer to this link below and perform all the steps in it.

http://gerardnico.com/wiki/smtp_telnet

Thanks,
-Amith.
1 person found this helpful
user8007837
Hi Amilt,

Thanks again. I executed Google STARTTLS from the above link, and then run the agent. I am still getting the same error.
C:\Users\Administrator>telnet smtp.gmail.com 587


220 mx.google.com ESMTP d59sm3142380wed.45
EHLO OBIEE
250-mx.google.com at your service, [62.97.99.232]
250-SIZE 35882577
250-8BITMIME
250-STARTTLS
250 ENHANCEDSTATUSCODES
AUTH LOGIN
530 5.7.0 Must issue a STARTTLS command first. d59sm3142380wed.45
STARTTLS
220 2.0.0 Ready to start TLS
EHLO OBIEE


Connection to host lost.

C:\Users\Administrator>
Thanks.
user8007837
Hi,

I have at least found where the error is. I think error is in SMTP server, need to find out what. I have created a mail at Gmx.com (as Nico suggested) and used it in the mail config. Its working fine.

Amit, I have awarded you points as Helpful answer.

Thanks.
Amith Y
Jorge,

I was about to suggest you to do the same. Thanks for the points.

Cheers,
Amith.
874079
This happens because the SMTP server does not accept authentication.

You need to remove the user and password from credentials.

This link shows how to remove the credentials, because there is a bug that does not permit you erase user and password:
http://shivabizint.wordpress.com/2011/04/17/to-delete-smtp-settings-in-obiee11g-enterprise-manager/
1 person found this helpful
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 11 2017
Added on Nov 8 2017
10 comments
515 views