Configuring Multiple Schemas within the same DB on ORDS

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

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

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

I see it Enabled. Please see screenshot below.

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

Thanks,

Vimal