4 Replies Latest reply on Sep 28, 2017 7:17 PM by thatJeffSmith-Oracle

    Enable REST Access to Table In Another Schema

    ericwood73

      I'm trying to set up ORDS on the local JDE database, to get REST access to some of the interoperability tables.  I've been able to get through the ORDS setup and install with SQLDeveloper 17.2 and the embedded ORDS 3.0.11.  I created an ORDS user/schema and enabled rest services on it.  I was able to enable rest services for a table directly under that user, but when I try to enable for the interoperability table under user JDEDATA920, I get this error in the wizard:

      Invalid members: executeOracleQuery failed: SELECT * FROM TABLE(ORDS_METADATA.ORDS.check_object_support(p_schema=>'JDEDATA920', p_object=>'F0101Z2', p_object_type=>'TABLE'))

       

      How can I enable REST for a table under a different user?  I don't know the user password for the JDEDATA920 table, because it was set up by JDE and is most likely encrypted anyway.  Nor can I reset it without breaking JDE.  But I can grant access to that table to the ORDS user, so I was hoping I could set up rest accessing that table as the ords user.

        • 1. Re: Enable REST Access to Table In Another Schema
          Kiran Pawar

          Hi 3537534,

          3537534 wrote:

          Please change your user handle from "3537534" to something meaningful. Refer: Video tutorial how to change nickname available

          I'm trying to set up ORDS on the local JDE database, to get REST access to some of the interoperability tables. I've been able to get through the ORDS setup and install with SQLDeveloper 17.2 and the embedded ORDS 3.0.11. I created an ORDS user/schema and enabled rest services on it. I was able to enable rest services for a table directly under that user, but when I try to enable for the interoperability table under user JDEDATA920, I get this error in the wizard:

          Invalid members: executeOracleQuery failed: SELECT * FROM TABLE(ORDS_METADATA.ORDS.check_object_support(p_schema=>'JDEDATA920', p_object=>'F0101Z2', p_object_type=>'TABLE'))

          How can I enable REST for a table under a different user? I don't know the user password for the JDEDATA920 table, because it was set up by JDE and is most likely encrypted anyway. Nor can I reset it without breaking JDE. But I can grant access to that table to the ORDS user, so I was hoping I could set up rest accessing that table as the ords user.

          You can use ORDS PLSQL API to enable AutoREST in other schema and tables in that schema.

          IMPORTANT: See what the Usage Notes say for both of these APIs:

          Only database users with the DBA role can enable or disable a schema other than their own. (ORDS.ENABLE_SCHEMA)

          Only database users with the DBA role can enable/access to objects that they do not own. (ORDS.ENABLE_OBJECT)

          You have to be logged in as a privileged user with DBA role to execute these APIs and REST enabling of SCHEMA/OBJECT to succeed.

           

          Regards,

          Kiran

          • 2. Re: Enable REST Access to Table In Another Schema
            thatJeffSmith-Oracle

            You can't REST enable a table for a schema that's not REST enabled. And in this case, you probably don't want to.

             

            You can however create a RESTful Service no your current schema that will run a SELECT (sql block) on that table. So you could query from it, as the session will be ran as your 'ORDS user', just make sure you have granted SELECT on JDEDATA920.F0101Z2 to this user you created and REST enabled.

             

            You'll be writing your own handlers and code to work with this table vs using the AUTO feature.

            • 3. Re: Enable REST Access to Table In Another Schema
              ericwood73

              Thanks.  I am unable to configure rest services using the REST Development view.  I've watched the videos but there is something I  am missing for my situation.  I created a user called ORDS.  I created a connection in SQL Developer called ORDS_JDE that uses the ORDS user.  I right click on the ORDS_JDE connection and choose REST Services -> Enable Rest Services.  I choose a schema alias of jde.  Then I go into the REST Development view and create a connection called ORDS_JDE.  I set it to connect using user ords on localhost:8081 with server path ords and schema workspace blank.  When I try to connect I get an Invalid Resource Credentials error.  If I change the schema/workspace setting to / I get a 404 when I try to connect (in SQL Developer).  Same thing happens if I choose /ords and ords.  If I choose jde, the schema alias I used above, I get the Invalid Resource Credentials.  It will let me create a module so I did that using jde as a base URI and contact as the pattern.  I then tried to invoke the api by going to localhost:8081/ords/ords/jde/contact and get a 404 (Te request could not be mapped to any database).  Can you see where I am going wrong?