4 Replies Latest reply on Jul 23, 2019 3:18 PM by Ajax_ords

    Enable Table from a different Schema

    Ajax_ords

      Hi Team,

       

      I have two database schema under the database. Schema User A is the user under which all the objects are created and the Schema User B has all the grants to access these objects and can perform CRUD operations.

       

      When I am opening the SQL Work Sheet to run the below API to expose my object which is created under Schema A  under the schema under B it does not work.

       

       

      Having said that i able to make a select * from table. I can query the record easily in the same window

       

       

      Is it possible to expose table from a a different schema under  a different  one ?

       

      I know we can create ords for multiple database but what about multiple schema  can we mapp it ?

        • 1. Re: Enable Table from a different Schema
          Kiran Pawar

          Hi Ajax_ords,

          Ajax_ords wrote:

           

          Hi Team,

           

          I have two database schema under the database. Schema User A is the user under which all the objects are created and the Schema User B has all the grants to access these objects and can perform CRUD operations.

          When I am opening the SQL Work Sheet to run the below API to expose my object which is created under Schema A under the schema under B it does not work.

          Having said that i able to make a select * from table. I can query the record easily in the same window

          Is it possible to expose table from a a different schema under a different one ?

          I know we can create ords for multiple database but what about multiple schema can we mapp it ?

          AutoREST does not work that way. If you enable Schema User A (with an alias). Then the objects (which are AutoREST-enabled) are available under that alias. The objects that are AutoREST-enabled in Schema User A will not be available in Schema User B.

           

          The best way is to define your own RESTful Web Service under Schema User B to expose the objects in Schema User A as RESTful web-service (given that you have already mentioned all the grants for select, insert, update, delete on the objects in Schema User A have already given to Schema User B).

           

          So for example you can create following RESTful Web Service in Schema User B:

          BEGIN
            ORDS.define_module(
              p_module_name    => 'testmodule2',
              p_base_path      => 'testmodule2/',
              p_items_per_page => 0);
          
            ORDS.define_template(
            p_module_name    => 'testmodule2',
            p_pattern        => 'emp/');
          
          
            ORDS.define_handler(
              p_module_name    => 'testmodule2',
              p_pattern        => 'emp/',
              p_method        => 'GET',
              p_source_type    => ORDS.source_type_collection_feed,
              p_source        => 'SELECT * FROM SCHEMAUSERA.emp',
              p_items_per_page => 0);
             
            COMMIT;
          END;
          /
          

           

          Assumptions:

          • Schema User A is SCHEMAUSERA in database
          • Schema User B is SCHEMAUSERB in database
          • SCHEMAUSERB schema is REST enabled in the database (with schema alias userb)
          • EMP is a table under SCHEMAUSERA in database
          • The above REST Service will be available at URL http://server:port/ords/userb/testmodule2/emp

           

          Hope this helps!

           

          Regards,

          Kiran

          • 2. Re: Enable Table from a different Schema
            Ajax_ords

            Yes Kiran, I am aware that I can write my own restful web service under a schema and then expose objects from a different schema (assuming all database rights exist). This does work but then I have to manually create  N module,template and a handler to expose N tables which looks like a work at least a bit.

             

            But I was wondering if there is a custom mechanism to expose objects from a different schema then when why can't we just make use of auto-rest or at least permit the API's (ORDS.ENABLE_OBJECT) to expose objects to do so.

             

            Primarily because that's how most applications built on top of database work at least in our case. We create a separate user to perform CRUD and then not use the main user as this can be used to drop objects but then with ORDS we might have to expose our main schema itself.

             

            BTW that's how it work as of now then.

             

            Thank you for your reply.

             

            Cheers.

            • 3. Re: Enable Table from a different Schema
              thatJeffSmith-Oracle

              Use ords_admin() to rest enable other schemas and their objects

              • 4. Re: Enable Table from a different Schema
                Ajax_ords

                thatJeffSmith-Oracle

                 

                Thank you for your reply.

                 

                I believe i was not able to communicate properly. I dont't want to enable two or more schemas but rather enable only one schema A and expose objects of other schema B from Schema A only.

                 

                Something  which is available by wring my own restful web service to access other schema object(s) from one schema.

                 

                If i make use of ords_admin then i would have to give each scehma it's own mapping url isn't ? or is there a way we can route them to one.

                 

                Ajax