When you AUTO-REST enable table in ORDS, we publish a full API for you:

the /{id} part is important.

We auto find your Primary Key Column and use that to address particular rows (items) in your table (collection.)

But..what happens if you don’t have a primary key?

CREATE TABLE no_keys (letter CHAR(1), letters VARCHAR2(25), numbers INTEGER);
 
INSERT INTO no_keys VALUES ('a', 'abc', 1);
INSERT INTO no_keys VALUES ('a', 'abc', 1);
INSERT INTO no_keys VALUES ('b', 'abc', 2);
INSERT INTO no_keys VALUES ('c', 'abc', 3);

REST enable that…

BEGIN
 
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'NO_KEYS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'no_keys',
                       p_auto_rest_auth => FALSE);
 
    COMMIT;
END;
/

And now let’s ‘GET’ the table.

Those look like…ROWIDs?

So let’s try that…

Wait, what?

Hmmm, that ‘%2’ part doesn’t look right to me.

That’s actually ‘%2B’ which is a way to escape a ‘+’ in a URL.

So if we replace that appropriately, we can confirm that’s indeed the ROWID.

Getting the record via ROWID.

By the way, ROWIDs can change. You should probably try to at least have some UNIQUEs on your table, if you can.

And if we go the OTHER way, More than One Column in my Primary Key?

CREATE TABLE THREE_KEYS 
(
  COL1 CHAR(1) NOT NULL 
, COL2 INT NOT NULL 
, COL3 INT NOT NULL 
, COL4 DATE 
, CONSTRAINT THREE_KEYS_PK PRIMARY KEY 
  (
    COL1 
  , COL2 
  , COL3 
  )
  ENABLE 
);
INSERT INTO three_keys VALUES ('A', 1, 2, sysdate);
INSERT INTO three_keys VALUES ('A', 1, 3, sysdate);
INSERT INTO three_keys VALUES ('A', 1, 4, sysdate);
COMMIT:

Now let’s REST enable it and pull up a record.

It’s col1,col2,col3, or ords/hr/three_keys/A,1,2
thatjeffsmith
Author

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

12 Comments

  1. *******************************************
    BUG-BUG-BUG-BUG-BUG-BUG-BUG-BUG-BUG-
    *******************************************
    Hi Jeff, my leader enginner found the solution and found a bug.

    Autorest insertion fails when:
    – The PK has more than one column and
    – The first column of the table begins with CDRA, CDRB, CDRC, CDRD, CDRE, or CDRF (although not part of the PK)

    oracle db 12c 12.2.0.1
    PL/SQL 12.2.0.1
    CORE 12.2.0.1
    APEX 18.2.0.00.12
    ORDS 18.1.1.95.1251
    ORDS

  2. Hello, I had a problem, we have many tables with primary key combinated, by example (field1, field2) like primary key
    and when I make a POST, this fail and respond 500 but the row was inserted, I looking for but no find any answer, in my tests was build the table step by step and just when put the primary key return 500, Do you know why happen this?, or Do you know one way for disable the warnnings that return the database and that ORDS converts in 500.

    Too is import that you know I had enabled the debbug fuction but no return any error type ORA or PLS is only java log

    • thatjeffsmith

      please share your table DDL and the row you’re trying to insert, and I’ll try to reproduce the issue

    • Hi,I Share An example of the issue, I appreciate your help

      this is my table structure:

      — Create table
      create table ICE.TATRITPL
      (
      cdramo NUMBER(3) not null,
      id_layout NUMBER not null,
      cdatribu NUMBER(2) not null,
      idatribu VARCHAR2(30) not null,
      dsatribu VARCHAR2(100) not null,
      swformat VARCHAR2(1),
      swobliga VARCHAR2(1),
      minlength NUMBER(12),
      maxlength NUMBER(12),
      minvalue NUMBER(12),
      maxvalue NUMBER(12),
      ottabval VARCHAR2(8),
      swvisible VARCHAR2(1),
      msg_id NUMBER(6)
      )
      tablespace DATA
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
      );
      — Create/Recreate indexes
      create unique index ICE.TATRITPL_PK on ICE.TATRITPL (CDRAMO, ID_LAYOUT, CDATRIBU)
      tablespace INDEXES
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
      );
      create unique index ICE.TATRITPL_U01 on ICE.TATRITPL (CDRAMO, ID_LAYOUT, IDATRIBU)
      tablespace INDEXES
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
      );
      — Create/Recreate primary, unique and foreign key constraints
      alter table ICE.TATRITPL
      add constraint TATRITPL_UK1 primary key (CDRAMO, ID_LAYOUT, CDATRIBU);

    • =================================================================================
      this is my url:

      http://x.x.x.x:8080/ords/gnpdb001/ice/tatritpl/

      this is my payload:

      {
      “cdramo” : 240,
      “id_layout” : 58,
      “cdatribu” : 5,
      “idatribu” : “ww”,
      “dsatribu” : “ww”,
      “swformat” : “N”,
      “swobliga” : “S”,
      “minlength” : 0,
      “maxlength” : 0,
      “minvalue” : 0,
      “maxvalue” : 0,
      “ottabval” : “”,
      “swvisible” : “S”,
      “msg_id” : “4”
      }

    • =================================================================================
      this is the respose when the table has primary key:

      404 Not Found

      Debug Trace
      [TE] url-mapping start: 2019-08-29T16:55:45.463Z duration: 0ms
      Mapped request to mapping: +ICE|gnpdb001|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
      did not authenticate request
      mapped request using: /gnpdb001/ice/* to: +ICE|gnpdb001|pu|, cryptoKeys=*****, identityDomain=__NO_IDENTITY_DOMAIN__
      Choosing: oracle.dbtools.rest.resource.jdbc.JDBCDispatchMetaData as current candidate with score: JDBCResourceHandlerScore [score=MetaDataScore [matchedMethod= POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      , matchedPattern= /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
      POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      , metadata=
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=ALLOW, documentation=AUTHORIZED, frameOptions=SAME_ORIGIN, pageSize=null, pagination=null, requiresPrivilege=, transport=ANY]
      pathTemplates:
      /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
      POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      /tatritpl/batchload
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      POST: POST /tatritpl/batchload
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      PUT: PUT /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]

      ]]
      Choosing: oracle.dbtools.rest.resource.cache.CachedResourceDispatcher as current candidate with score: JDBCResourceHandlerScore [score=MetaDataScore [matchedMethod= POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      , matchedPattern= /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
      POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      , metadata=
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=ALLOW, documentation=AUTHORIZED, frameOptions=SAME_ORIGIN, pageSize=null, pagination=null, requiresPrivilege=, transport=ANY]
      pathTemplates:
      /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=25, pagination=ITEM_OFFSET, requiresPrivilege=null, transport=null]
      POST: POST /tatritpl/
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      /tatritpl/batchload
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      POST: POST /tatritpl/batchload
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=null, pagination=null, requiresPrivilege=null, transport=null]
      methods:
      DELETE: DELETE /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      GET: GET /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]
      PUT: PUT /tatritpl/:id
      common: CommonMetaData [accepts=[], allowedOrigins=[], cors=null, documentation=null, frameOptions=null, pageSize=0, pagination=NONE, requiresPrivilege=null, transport=null]

      ]]

    • =================================================================================
      The row was inserted, but the response is that error, in all my tables if has primary fails, but when not has primary key works fine, too works fine when I put unique constraint, I see is only with the primary but I don’t know why

  3. Very nice tutorial on REST. It will be very handy to share data among different systems. Thank you Jeff!

Write A Comment