Currently I’m writing a blogpost about provisioning the Autonomous Transaction Processing (ATP) database and using it from Process Cloud Service (PCS) and Visual Builder Cloud Service (VBCS). Right now however I would like to share something that can benefit everyone working with ATP and Oracle Rest Data Services (ORDS), being: the ORDS CRUD Generator.
Link: you can find my JSFiddle here, if you want the tool in VBCS format, please ask me in the comments!
Remark: this tool is still under construction, so not all SQL inputs might work (yet). Please do add bugs in the comments and I’ll make sure the JSFiddle gets fixed. Update: it works now for both APEX SQL Workshop and SQLDeveloper copy paste
This little tool that I wrote will write SQL code that makes generating services easier then ever before!! But why do that? Let me explain:
We are using one schema for multiple domains, why? Because we want to have for example one address table, use and be able to query on it in a generic way. We do however want to have specific (address) services per domain. So let’s say we have two domains, marketing and orders, we are going to create two seperate address service CRUD templates, /marketing/address and /order/address. Why not auto-rest on the address table? Mainly for two reasons, the first being extensibility, if we ever get changes we can quickly & easily add PLSQL / SQL to our REST handlers. Secondly this is because we want to be able to have applications in each domain to be able to release without influence the others. In the GET handler, we don’t do GET * but select all the fields we currently have (like select street, city, etc). By doing this we decouple the fields between the ORDS service layer and the database itself. If one of the domain teams changes a table by adding a field, which will be 90% of the database changes, the other team is not even influenced AT ALL (say we add a IS_APARTMENT field to the address table in team marketing, we do not influence the orders team). If we rename or remove database fields, which will not happen often, we have two options:
- Decide on a common release window (always when removing a field)
- When renaming a team has to talk to the other teams to check if it impacts the services. The team then renames all the fields in the ORDS services. This way it will not impact any applications that use the services.
So what does the tool do for us? It makes the life easier by automatically create all ORDS CRUD services, including even a procedure so we can do a PATCH as well! For the patch we use optional fields in the PUT http method, which means we can optionally update a few fields of a complete table instead of always having to update the complete table. I use APEX on ATP myself to do the SQL, the tool has several fields (VBCS view, JSFiddle is slightly different):
- DB Schema: the schema of the DB you want the services in
- Systemfields: the fields which should NOT be exposed to the world, in our teams we have triggers for last updated by, last updated on, created by, etc.
- REST Module: the name of the REST module you want to add these templates to
- REST Path: the path you want in the URI, for example /marketing/
- ORDS Put procedure: the name of the procedure for the optional PUT (PATCH). The name is automatically filled, can be changed though
- Handler prefix: adds text to each URI of specific handlers, for example “jarvispizza-” makes the URI for the handler URI “jarvispizza-address” and the template URI “/marketing/jarvispizza-address”
- Tablename: automatically filled, database tablename in ATP
- ID Field: the ID field in the database, needed to be able for the single GET and DELETE operation (this field maps to the :id in the URI, so address/4, the 4 is the key in the ID Field)
- Select * in get: This switch means if we want specific fields or select * in the get (select * from address vs. select street, city, etc from address)
- Do camelCase: This switch means if we want the post and put to do camelCasing for underscore fields
The textarea is the input from the SQL statement of the creation of your table, found in the screenshot. An example:
CREATE TABLE "TESTORDSGEN"
( "ID" NUMBER(16,0),
"TESTCOL1" VARCHAR2(128) COLLATE "USING_NLS_COMP",
"TESTCOL2" VARCHAR2(128) COLLATE "USING_NLS_COMP",
"TESTCOL3" VARCHAR2(128) COLLATE "USING_NLS_COMP",
CONSTRAINT "TESTORDSGEN_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
) DEFAULT COLLATION "USING_NLS_COMP"
/
CREATE OR REPLACE EDITIONABLE TRIGGER "BI_TESTORDSGEN"
before insert on "TESTORDSGEN"
for each row
begin
if :NEW."ID" is null then
select "TESTORDSGEN_SEQ".nextval into :NEW."ID" from sys.dual;
end if;
end;
/
ALTER TRIGGER "BI_TESTORDSGEN" ENABLE
/
The first button Parse will make sure the data is correct and set the Tablename and procedure fields.
The second button Convert/Create will create the output SQL statement.
The two resulting SQL statements in the output should be runned seperately in the SQL worksheet or SQL commands tool seen in the screenshot. Running them in one statement is still one of the stretch goals 😉
Important! The first time a module is created one should run both the SQL Output in the “SQL Modules” section AND the “SQL Procedure” section. When the module already exists, run the normal “SQL Output” section AND the “SQL Procedure” section. If you do not do this, ORDS will first create an empty module and you will lose all your previous work.
The output of the tool that is in the screenshots:
create or replace procedure ords_put_testordsgen_prc
(l_id IN NUMBER,
l_testcol1 IN VARCHAR2,
l_testcol2 IN VARCHAR2,
l_testcol3 IN VARCHAR2)
is
begin
update TESTORDSGEN alias_testordsgen set
alias_testordsgen.testcol1 = nvl(l_testcol1, alias_testordsgen.testcol1),
alias_testordsgen.testcol2 = nvl(l_testcol2, alias_testordsgen.testcol2),
alias_testordsgen.testcol3 = nvl(l_testcol3, alias_testordsgen.testcol3)
where alias_testordsgen.ID = l_id;
end;
DECLARE
l_roles OWA.VC_ARR;
l_modules OWA.VC_ARR;
l_patterns OWA.VC_ARR;
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'ENEXIS',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'enexis',
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => 'standaard_aansluitingen',
p_base_path => '/sa/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => 'VBCS Generated Module');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => 'VBCS Generated resource');
ORDS.DEFINE_HANDLER(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen/:id',
p_method => 'DELETE',
p_source_type => 'plsql/block',
p_mimes_allowed => '',
p_comments => 'VBCS Generated delete handler',
p_source => 'delete from TESTORDSGEN where ID = :id');
ORDS.DEFINE_HANDLER(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen/:id',
p_method => 'GET',
p_source_type => 'json/item',
p_mimes_allowed => '',
p_comments => 'VBCS Generated get handler',
p_source => 'select ID, TESTCOL1, TESTCOL2, TESTCOL3 from TESTORDSGEN where ID = :id');
ORDS.DEFINE_HANDLER(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen/:id',
p_method => 'PUT',
p_source_type => 'plsql/block',
p_mimes_allowed => 'application/json',
p_comments => 'VBCS Generated put handler',
p_source => 'BEGIN
ords_put_testordsgen_prc(:id, :testcol1, :testcol2, :testcol3);
END;');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => 'VBCS Generated resource');
ORDS.DEFINE_HANDLER(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => 'application/json',
p_comments => 'VBCS Generated post handler',
p_source => 'BEGIN
insert into TESTORDSGEN (TESTCOL1, TESTCOL2, TESTCOL3) values (:testcol1, :testcol2, :testcol3);
END;');
ORDS.DEFINE_HANDLER(
p_module_name => 'standaard_aansluitingen',
p_pattern => 'testordsgen',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => '',
p_comments => 'VBCS Generated get handler',
p_source => 'select ID, TESTCOL1, TESTCOL2, TESTCOL3 from TESTORDSGEN');
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
COMMIT;
END;
When the statements have been run, both the procedure and all the CRUD rest services should have been created!
One can then test this with POSTMAN. Do note that in the current tool version I changed the POST and PUT optionally with the do camelCase feature is checked, so that it could get automagically converted into lowerCamelCase. For example a post should look like this (the field is TEST_COL_UNDERSCORE in the database which should be used in lowercase when the do Camelcase feature is unchecked):
{
"testcol1": "guns",
"testcol2": "and",
"testcol3": "rosez",
"testColUnderscore": "somethin"
}
Also notice that the filtering just keeps on working without a problem!
I hope you enjoyed this read and can use the tool in your own environment. Feel free to comment and ask me questions when needed. The tool is free for use, please do not copy & distribute it yourself but use my link.
Rick's blog 
Leave a comment