7 Replies Latest reply: Jun 10, 2014 7:29 AM by odie_63 RSS

    Trying to read specific RSS fails with ORA-31011 and LPX-00007

    Nikolas Saridakis

      Hello everybody,

       

      My DB version is Personal Oracle Database 12c Release 12.1.0.1.0

       

      This have puzzled me a lot. I'm trying to read RSS data from ΟΠΑΠ Πάμε Στοίχημα RSS</title> <link rel="alternate" href="http://www.opap.gr/el/web/guest/stoi…

       

      1st try was with rss_util_pkg. No chance since it throws ORA-31011 and LPX-00007

      2nd try is the following, since maybe i have large amount of data or misformed XML (my assumption) I tried to insert the document in a CLOB and maybe print it, so I can see what's wrong:

       

      declare

        url varchar2(500):= 'http://www.opap.gr/el/web/guest/stoixima-rss/-/journal/rss/2308077?doAsGroupId=10157&refererPlid=14540&_15_groupId=10157';

        rssFeedDoc    dbms_xmldom.domdocument;

        myParser     dbms_xmlparser.Parser;

       

        myClob    CLOB := ' ';

      begin

          myParser := dbms_xmlparser.newParser;

          rssFeedDoc   := dbms_xmlparser.parse(url);

          myClob := dbms_xmldom.getxmltype(doc => rssFeedDoc).getClobVal();

      end;

       

      Guess what, same mistake, ORA-31011, and LPX-00007.

       

      Actually, whatever I put after dbms_xmlparser.parse(url); (getElementsByTagname or whatever), throws same error...

       

      Any help would be appreciated!!

      Thank you in advance,

      Nik

        • 1. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
          odie_63

          Hi Nik,

           

          Your code works fine for me.

          This one too :

          SQL> set scan off

          SQL>

          SQL>

          SQL> select *

            2  from xmltable(

            3         xmlnamespaces(default 'http://www.w3.org/2005/Atom')

            4       , '/feed/entry'

            5         passing httpuritype(

            6                 'http://www.opap.gr/el/web/guest/stoixima-rss/-/journal/rss/2308077?doAsGroupId=10157&refererPlid=14540&_15_groupId=10157'

            7                 ).getxml()

            8         columns title    varchar2(256)            path 'title'

            9               , pub_date timestamp with time zone path 'published'

          10       )

          11  ;

           

          TITLE                                                                            PUB_DATE

          -------------------------------------------------------------------------------- -----------------------------------

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 18 ΠΑΓΚΟΣΜΙΟΚΥΠΕΛΛΟΒΡΑΖΙΛΙΑΣ #2014593333618#                     09/06/14 12:33:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 158 ΜΟΛΝΤΕ-ΜΠΡΑΝ #201459328033#                                  09/06/14 12:28:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 157 ΣΤΡΟΜΣΓΚΟΝΤΣΕΤ-ΧΑΟΥΓΚΕΣΟΥΝΤ #20145932755272#                 09/06/14 12:27:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 167 ΧΟΝΕΦΟΣ-ΤΡΟΜΣΝΤΑΛΕΝ #20145931237698#                         09/06/14 12:12:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 164 ΜΠΑΕΡΟΥΜ-ΦΡΕΝΤΡΙΚΣΤΑΝΤ #20145913332809#                      09/06/14 10:33:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 168 ΡΟΖΕΝΜΠΟΡΓΚ-ΛΙΛΕΣΤΡΟΜ #20145912750439#                       09/06/14 10:27:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 168 ΡΟΖΕΝΜΠΟΡΓΚ-ΛΙΛΕΣΤΡΟΜ #20145912746641#                       09/06/14 10:27:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 168 ΡΟΖΕΝΜΠΟΡΓΚ-ΛΙΛΕΣΤΡΟΜ #20145912742235#                       09/06/14 10:27:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 168 ΡΟΖΕΝΜΠΟΡΓΚ-ΛΙΛΕΣΤΡΟΜ #20145912736630#                       09/06/14 10:27:00,000000 +00:00

          ΑΛΛΑΓΗ ΑΠΟΔΟΣΗΣ 12 ΠΑΓΚΟΣΜΙΟΚΥΠΕΛΛΟΒΡΑΖΙΛΙΑΣ #201459104840490#                   09/06/14 07:48:00,000000 +00:00

           

          10 rows selected

           

          The RSS feed comes with UTF-8 encoding. Does your db support this charset?

          I'm also assuming you've enabled network access via ACLs?

          • 2. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
            Nikolas Saridakis

            Thank you for your help! Your code doesn't work also...

             

            This is current condition

            SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

             

            VALUE

            --------------------------------------------------------------------------------

            AL32UTF8

             

            I run this for the acl:

             

            SQL> begin

              2   dbms_network_acl_admin.create_acl (

              3    acl => 'rss.xml'

              4  , description => 'RSS Access'

              5  , principal => 'C##BETBOX'

              6  , is_grant => TRUE

              7  , privilege => 'connect'

              8  , start_date => null

              9  , end_date => null );

            10 

            11   dbms_network_acl_admin.add_privilege (

            12    acl => 'rss.xml'

            13  , principal => 'C##BETBOX'

            14  , is_grant => TRUE

            15  , privilege => 'resolve'

            16  , start_date => null

            17  , end_date => null );

            18 

            19   dbms_network_acl_admin.assign_acl (

            20    acl => 'rss.xml'

            21  , host => 'applications.opap.gr'

            22  , lower_port => 80

            23  , upper_port => 80);

            24 

            25 

            26   dbms_network_acl_admin.assign_acl (

            27    acl => 'rss.xml'

            28  , host => 'www.opap.gr'

            29  , lower_port => 80

            30  , upper_port => 80);

            31 

            32  commit;

            33  end;

            34  /

             

            still no luck... What am I missing here?

            • 3. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
              odie_63

              Could you check the output of :

              select httpuritype(

                       'http://www.opap.gr/el/web/guest/stoixima-rss/-/journal/rss/2308077?doAsGroupId=10157&refererPlid=14540&_15_groupId=10157'

                     ).getclob()

              from dual;

              ?

              It should give you the content without any parsing attempt.

              • 4. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
                Nikolas Saridakis

                It runs ok, but there's no output, only

                 

                SQL> select httpuritype(

                  2               'http://www.opap.gr/el/web/guest/stoixima-rss/-/journal/rss/2308077?doAsGroupId=10157&refererPlid=14540&_15_groupId=10157'

                  3               ).getclob() as t

                  4  from dual;

                 

                T

                --------------------------------------------------------------------------------

                 

                SQL>

                 

                Thank you for your time and help! If I manage to solve this, I owe you one...

                • 5. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
                  odie_63

                  Then there's a problem in retrieving the online resource itself.

                  I can't help you much in this regard.

                   

                  Are you behind a proxy?

                  • 6. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
                    Nikolas Saridakis

                    I' m not behind a proxy BUT...

                     

                    I found where the problem lies!!!!

                     

                    When I execute the script it asks me for parameters refererPlid and _15_groupId...

                    I thought that I should put there the numbers that I see on the url, but no! I saw in the substitution of the parameters that it was replacing the parameter names on the url with the numbers i input, making the url as ...doAsGroupId=1015714540=14540!!

                     

                    So next question is how to disable this question of parameters since everything is on the url... Any ideas?

                     

                    Anyway just for the "honours", I am marking this question as answered, but if you can direct me to where to find solution about the parameters being asked I would appreciate it!!

                    Thank you so much

                    • 7. Re: Trying to read specific RSS fails with ORA-31011 and LPX-00007
                      odie_63

                      I should have thought about that one

                      So next question is how to disable this question of parameters since everything is on the url... Any ideas?

                      Well, depends on the client tool you're using.

                      If you see again my first reply, I used SET SCAN OFF command in SQL*Plus to disable the picking up of substitution parameters.

                       

                      In PL/SQL Developer, you can either doubling the & character (not ideal), or recent versions provide a command to turn it off as well.

                      SQL Developer, TOAD or other similar GUI tools surely have this kind of features too.