REST Enabled Views & Primary Keys

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;

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.

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.

3 Comments

Laurent Schneider 5 months ago 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
Laurent Schneider 5 months ago Reply

I had lot’s of hassle with view with CHECK CONSTRAINT and INSTEAD OF triggers.

I realized in https://www.thatjeffsmith.com/archive/2018/08/executing-pl-sql-package-procedure-via-rest/ that using procedures with in out procedures is so much simpler in my case

You rule Jeff, your SQL Developer is just fantastic… really zero-coding PL/SQL programming, awesome 🙂

Thanks
- thatjeffsmith Post Author 5 months ago
  
  Thanks Laurent, really appreciate that.
  
  I think anytime you can get rid of a TRIGGER, you’ve already won 🙂

Your VIEW needs a Primary Key

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

Related Posts

3 Comments

Write A Comment Cancel Reply