Mostly Making oracle easy, mostly:
Search for:
ThatJeffSmith
In ORDS

REST Enabled Views & Primary Keys

2 Mins Read

Got this question from a friend late last week…

I created a new schema (API) and within that schema a view based upon a table…like

create view my_data as select * from hr.data

/

I REST enabled my API schema.
I REST enabled my_data view

Calling /ords/api/my_data/1 returns a 500

And my reply was, it’s a bug.

But unfortunately, my reply was buggy.

The real answer is…

Your VIEW needs a Primary Key

Not all VIEWs are based on tables such that you can assume there is a primary key available. So what we do is if YOU provide us a PK on your view explicitly, we’ll support it. If there’s no PK available on the VIEW directly, we fall back to using ROWIDs, just like we do for TABLEs.

Let’s take a look at a concrete example.

A VIEW sans PK

I have a TABLE called STRAVA, it has my exercise activity data. And I’ve created a view on this table, that let’s me pull up anything in that table that’s NOT based on running activities.

After we create our VIEW, let’s REST Enable it – and the following single API call, gives us a ton of ORDS features for FREE (no custom handler coding):

BEGIN
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'NOT_RUNNING',
                       p_object_type => 'VIEW', -- A VIEW!
                       p_object_alias => 'not_running',
                       p_auto_rest_auth => FALSE);
    COMMIT;
END;
500’s are the WORST.

Same VIEW with a PK

CREATE OR REPLACE FORCE VIEW "NOT_RUNNING" (
       "ID",
       "ACTIVITY_DATE",
       "NAME",
       "TYPE",
       "DESCRIPTION",
       "ELAPSED_TIME",
       "DISTANCE",
       CONSTRAINT v_not_running_pk PRIMARY KEY ("ID") rely disable novalidate 
) DEFAULT COLLATION "USING_NLS_COMP" AS
       SELECT "ID",
              "ACTIVITY_DATE",
              "NAME",
              "TYPE",
              "DESCRIPTION",
              "ELAPSED_TIME",
              "DISTANCE"
         FROM strava
        WHERE TYPE NOT LIKE '%Run%';

And let’s pull up a row now.

Ta-da!

This has another net-effect on our collection, aka the View/

No extra code written other than you giving us the PK constraint on the View.

This capability, to add Integrity Constraints to VIEWs has been around awhile. Here’s a Docs link on the topic sourcing from version 10gR2.

Now that my VIEW has a PK, I can update it, too!

I can also now do a 

PUT /ords/schema/view/PK

And attach the on the PUT body a {JSON} with the values of the row, and have an UPDATE run.

So let’s add a comment to my Afternoon Ride, #686658414.

Sweet.
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

3 Comments

  1. Laurent Schneider Reply

    SQL> drop table t
    Table dropped.
    SQL> create table t(x number primary key, y number)
    Table created.
    SQL> create or replace view v as select x from t where y=1 with check option
    View created.
    SQL> create or replace trigger g instead of insert on v for each row
    begin
    insert into t values (:new.x,1);
    end;
    Trigger created.
    SQL> insert into v values (1)
    1 row created.
    SQL> select * from t

    X Y
    ———- ———-
    1 1
    1 row selected.

    I end up writing a procedure and rest-enabling that procedure instead of this view+trigger approach

    • thatjeffsmith
      thatjeffsmith Post Author

      Thanks Laurent, really appreciate that.

      I think anytime you can get rid of a TRIGGER, you’ve already won 🙂

Write A Comment