8 Replies Latest reply on Apr 18, 2019 6:44 PM by Gianni Ceresa

    Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid

    mohanr.k

      I am getting below exception when I create Property Graph tables using OraclePropertyGraph.getInstance() API. This error is coming after applying PGX3.1.5 patch and it was working fine with PGX3.1.0.

       

      I am applying this patch on Oracle 18c and Windows environment.

       

      Patch has been downloaded from https://support.oracle.com/rs?type=patch&id=29396996

       

      11:45:14.733 [main] ERROR oracle.pg.rdbms.OraclePropertyGraph - createGraphTables:

      java.sql.SQLException: ORA-06550: line 1, column 7:

      PLS-00905: object MDSYS.OPG_APIS is invalid

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

       

       

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:223) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:907) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4230) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079) ~[ojdbc8-12.2.0.1.jar:12.2.0.1.0]

      at oracle.pg.rdbms.OraclePropertyGraph.createGraphTables(OraclePropertyGraph.java:1220) [sdopgdal.jar:?]

      at oracle.pg.rdbms.OraclePropertyGraph.init(OraclePropertyGraph.java:3051) [sdopgdal.jar:?]

      at oracle.pg.rdbms.OraclePropertyGraph.<init>(OraclePropertyGraph.java:3037) [sdopgdal.jar:?]

      at oracle.pg.rdbms.OraclePropertyGraph.<init>(OraclePropertyGraph.java:2954) [sdopgdal.jar:?]

      at oracle.pg.rdbms.OraclePropertyGraph.getInstance(OraclePropertyGraph.java:3143) [sdopgdal.jar:?]

        • 1. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
          Gianni Ceresa

          I had the same issue with 3.1.5 and reverted to 3.1.1.

          If not wrong the packages can't compile because of something related to "PRAGMA" (I don't have the full error in mind anymore).

           

          Not being a DB guy I have no idea of what "pragma" is in Oracle database, but the difference between 3.1.1 and 3.1.5 are quite obvious even just by looking at the content of the patch:

          Capture.PNG

          Left is 3.1.5, right is 3.1.1. There is a folder which isn't part of $ORACLE_HOME/md anymore, named "admin_with_pragma".

          It looks like the 3.1.1 PGX had 2 versions of the packages: one without "pragma" which was used, and one with "pragma" which wasn't used as not loaded/compiled.

          The 3.1.5 version seem to have moved to the "pragma" version and that's where my error was coming from !

          Sadly the readme file doesn't mention a single thing and there isn't any public document explaining how a patch fixing "memory leak in PGX in-memory analyst" ended up replacing a whole set of PL/SQL objects with the version with "pragma".

           

          More practically: as this is an official patch you can open an SR and have official support from Oracle if you want.

           

          EDIT: I mixed up versions, correcting my post

          • 2. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
            mohanr.k

            Thank you. Could you please share 3.1.1 patch link? I don't have access to support portal and I need to share it with someone for downloading it.

             

            Also want to check with you if 3.1.1 supports loading vertex labels?

            • 3. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
              Gianni Ceresa

              For any Oracle employee wanting to look into this:

              Capture.PNG

              All the OPG* packages in 3.1.5 can't compile and all with the same error.

               

               

              mohanr.k

              3.1.1 come with "Patch 28577866: MISSING LATEST PGX 3.1.X FUNCTIONS IN ORACLE 12.2 AND 18.1" (https://support.oracle.com/epmos/faces/PatchDetail?patchId=28577866 ).

              PGX 3.1.1 add a new column to the VT$ table, named "VL", which is supposed to store the vertices labels. But it seems to be supported only by the PGQL queries translated into SQL and run on the database itself. PGX doesn't seem to have a single way to load it (I asked PMs about it and ... nothing came back so far).

              For vertex labels in PGX I generally add this to my graph load config (the JSON): "use_vertex_property_value_as_label" : "<property_name>"

              So I store my label as a property and I ask PGX to turn it into labels when loading the graph from the DB.

              This worked for me so far.

              • 4. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
                mohanr.k

                Also it will be helpful if you can share the steps to revert the patch

                • 5. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
                  Gianni Ceresa

                  To revert your PGX 3.1.5 to the original one it requires you to have a backup of the $ORACLE_HOME/md/property_graph and $ORACLE_HOME/md/admin/*opg*.

                   

                  If you will install PGX 3.1.1 from the other patch just follow the steps included in the readme and it will be good.

                  If you have the backup of the above mentioned files/folders, you can revert by deleting $ORACLE_HOME/md/property_graph and $ORACLE_HOME/md/admin/*opg* and copy back the backups you have.

                  After you did that you need to load the packages and compile, you must be able to do that by doing this: (copying from the patch README file as that's what worked for me)

                  cd $ORACLE_HOME/md/admin/

                   

                  As sys, execute catopg.sql

                   

                  sqlplus /  as sysdba       

                  alter session set container=<YOUR_PDB_NAME>;

                  @catopg.sql

                   

                  In the end PGX is very detached from the database, which make things quite easy: fully replace the files in $ORACLE_HOME/md/property_graph which is really PGX itself with all the files and libs.

                  Load (replace) the packages in the database and compile them, which is what the files in $ORACLE_HOME/md/admin/*opg* do.

                  • 6. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
                    Gianni Ceresa

                    Rick Anderson-Oracle : are you "rjanders" ?

                    The packages of "Patch 29396996: MEMORY LEAK IN PGX IN-MEMORY ANALYST" not compiling anymore (I tested on a 18.3 EE and 18c XE on linux) have this comment on top:

                    Rem    rjanders    06/14/18 - #28103358: MDSYS packages require PRAGMA for

                    Rem                           DBMS_ROLLING upgrade support

                    So maybe you can help in fixing the issue (Error(4,10): PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here) ?

                    • 7. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
                      mohanr.k

                      I am getting below date parse exception with 3.1.1 patch

                       

                      13:50:53.004 [pgx-client-thread-0] ERROR oracle.pgx.client.PgxRemoteFuture - received serialized exception

                      java.lang.Exception: property creation_date: could not parse value 2014-08-13T11:06:54.000000-04:00 for property of type LocalDateTime using any of the given formats code: PGX-ERROR-FS8NKJQ79793

                      at oracle.pgx.common.marshalers.ExceptionMarshaler.toUnserializedException(ExceptionMarshaler.java:92) ~[pgx-api-3.1.1.jar:3.1.1]

                      at oracle.pgx.common.marshalers.ExceptionMarshaler.unmarshal(ExceptionMarshaler.java:105) ~[pgx-api-3.1.1.jar:3.1.1]

                      at oracle.pgx.client.RemoteUtils.parseExceptionalResponse(RemoteUtils.java:123) ~[pgx-client-3.1.1.jar:3.1.1]

                      at oracle.pgx.client.RemoteUtils.getResponseContent(RemoteUtils.java:91) ~[pgx-client-3.1.1.jar:3.1.1]

                      at oracle.pgx.client.RemoteUtils.parse(RemoteUtils.java:146) ~[pgx-client-3.1.1.jar:3.1.1]

                      at oracle.pgx.client.PgxRemoteFuture.executeRequest(PgxRemoteFuture.java:105) ~[pgx-client-3.1.1.jar:3.1.1]

                       

                      Below is the date property stored in DB

                       

                      • 8. Re: Property graph table creation error -> PLS-00905: object MDSYS.OPG_APIS is invalid
                        Gianni Ceresa

                        Are you reading my mailbox?

                        That was also part of my email to PMs when I asked about the "VL" column.

                         

                        I managed to find myself the answer to this one as I also struggled quite a bit in loading a date.

                        Try adding to your loading config JSON the following: "date_format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSSXXX" .

                         

                        That's a piece of my JSON to give you an example how I used it:

                        "vertex_props": [{

                          "type": "date",

                          "name": "dob"

                        }],

                        "date_format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSSXXX",

                        I had to try a bunch of various formats before to find one which was accepted. Using that one and the property of type DATE it worked. Trying to use TIMESTAMP type never worked (technically they use a different java class which parse dates differently, there are in theory ways to set their formats too but never worked for me).

                         

                        The annoying part is still that PGQL doesn't know much about dates, it often complain when you try to use them. If you plan to use them in PGQL also consider defining the dates as strings: it's dirty and not really correct, but sometimes it just works.