In ORDS

What Happens if I REST Enable a table with no Primary Key in ORDS?

March 5, 2018 1 Min Read

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

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.

So let’s try that…

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.

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

Author thatjeffsmith

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

12 Comments

Mario 5 years ago Reply

*******************************************
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
Mario 5 years ago Reply

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 Post Author 5 years ago
  
  Autorest table, or your own service?
- Mario 5 years ago
  
  Hello, yes my service is Autorest this only happen with Autorest
- thatjeffsmith Post Author 5 years ago
  
  please share your table DDL and the row you’re trying to insert, and I’ll try to reproduce the issue
- Mario 5 years ago
  
  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);
- Mario 5 years ago
  
  =================================================================================
  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”
  }
- thatjeffsmith Post Author 5 years ago
  
  I can POST a new record to your table/ no problem – I just changed the table name…
- Mario 5 years ago
  
  =================================================================================
  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]
  
  ]]
- Mario 5 years ago
  
  =================================================================================
  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
Yogi 7 years ago Reply

Very nice tutorial on REST. It will be very handy to share data among different systems. Thank you Jeff!
- Mario 5 years ago
  
  Hello, yes my service is Autorest this only happen with Autorest

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

Filtering out the Noise from your Relational Diagrams

Building An Object Search for SQLcl

Related Posts

Python Flask w/JavaScript to demo Oracle REST APIs

Python Developers: Learn how to work with JSON in your Oracle Database via REST APIs at Cloud World

Querying JSON Relational Duality Views via REST APIs

12 Comments

Write A Comment Cancel Reply