ORDS, SODA & JSON in the Database

You'll REST enable the SCHEMA you want to use for running the query behind your service.

So if you have a query that hits schemas A, B, C - you'll want to rest enable a schema whose USER will have the necessary privs to access those tables and views.

You could write a service to query SYS objects without enabling SYS - you'd simply enable a schema/USER that could query said SYS views. In fact, never ever ever REST enable SYS.

Hi,

It doesn't seem to work.

I have a synonym for Table 2 on Schema 1.

I logged into Schema 2 and executed the following

GRANT SELECT ON TABLE 2 to SCHEMA 1.

I then tried to enable the REST object on Schema 1 for Table 1 and i am getting the following exception.

Failed to process SQL command

- ORA-01031: insufficient privileges

ORA-06512: at "ORDS_METADATA.ORDS_SECURITY", line 51

ORA-06512: at "ORDS_METADATA.ORDS", line 289

ORA-06512: at line 5

Any pointers?

Thanks,

Vimal

I then tried to enable the REST object on Schema 1 for Table 1 and i am getting the following exception.

How exactly did you try this?

Through SQL Developer.

I am connected to both the schema's via SQL Developer.

So on Schema 1, I navigated to the Table and right clicked to Enable REST Services, selected the Enable Object and gave the Alias.

Here's the SQL for the same,

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,

                       p_schema => '<schema 2>',

                       p_object => '<table 1>',

                       p_object_type => 'TABLE',

                       p_object_alias => '<alias name>',

                       p_auto_rest_auth => FALSE);

    commit;

END;

Thanks,

Vimal

So if you're on Schema 1, then why is p_schema set to 'schema 2'  ?

That's what is confusing me as well.

I have registered both the schemas, even though i am connected to Schema 1.

Even though i picked Table 2 from the Table filter of Schema 1 (as i mentioned this a synonym), for some reason the SQL is always putting Schema 2 as the p_schema.

I also executed this query on schema 1, but it is not returning any rows.

SELECT parsing_schema,

       parsing_object,

       object_alias,

       type,

       status

FROM   user_ords_enabled_objects

ORDER BY 1, 2;

How can i check if Schema 1 is REST enabled?

Thanks,

Vimal

on your schema 1 connection, right click, enable rest - is the checkbox already checked? If so, it's enabled.

Or

select * from ORDS_METADATA.ORDS_SCHEMAS

I see it Enabled. Please see screenshot below.

APPS and NAPPCUST are the Schema 1 and Schema 2 in this scenario.

Thanks,

Vimal