The Autonomous Database is offered via two services in our Oracle Cloud, Autonomous Data Warehouse and Autonomous Transaction Processing.
As of today, you can now develop and deploy RESTful Services for your Autonomous Database with native Oracle REST Data Services (ORDS) support.
What is ORDS?
This video is a comprehensive overview of REST and how ORDS provides what you need to deliver RESTful Services for your Oracle Database.
You can find more information about ORDS on our product page:
Developing RESTful Services in Autonomous Database
You have several development interfaces available, including:
- SQL Developer (desktop)
- PL/SQL API
From SQL Developer on your desktop, you can connect to your Autonomous Database and REST enable tables and views and/or develop custom RESTful Services based on your SQL and PL/SQL code.
In this exercise, I’m going to create a new user, enable it for REST (SQL Developer Web + RESTFul Services) access, create a table, and publish a RESTFul Service for it.
Let’s create our user first – I do NOT recommend you use ADMIN account for application development work, just like you wouldn’t use SYS or SYSTEM to create your application tables.
Executed directly from my SQL Developer Web worksheet:
BEGIN CREATE USER jeff IDENTIFIED BY ReallyGoodPasswordNot; GRANT CONNECT, resource TO jeff; ALTER USER jeff QUOTA UNLIMITED ON DATA; -- this part is important if you want to do INSERTs BEGIN -- this part is so I can login as HR via SQL Developer Web ords_admin.enable_schema ( p_enabled => TRUE, p_schema => 'JEFF', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'tjs', -- this flag says, use 'tjs' in the URIs for JEFF p_auto_rest_auth => TRUE -- this flag says, don't expose my REST APIs ); COMMIT; END; /
With this executed, I can now open a SQL Developer Web session, as my new user JEFF. My URL would look something like this:
I’ll be prompted for the username and password (use the JEFF account), and then I can create my table, data, and RESTful Service.
So let’s do that. Here’s a table, some data, and a very simple GET Handler (to select * from our table).
CREATE TABLE hello_world ( id INTEGER, message VARCHAR2 (25), CONSTRAINT hello_world_pk PRIMARY KEY (id) ); INSERT INTO hello_world (id, message) VALUES (1, 'Hello'); INSERT INTO hello_world (id, message) VALUES (2, 'world,'); INSERT INTO hello_world (id, message) VALUES (3, 'it''s'); INSERT INTO hello_world (id, message) VALUES (4, 'me,'); INSERT INTO hello_world (id, message) VALUES (5, 'Jeff.'); BEGIN ords.enable_schema ( p_enabled => TRUE, p_schema => 'JEFF', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'tjs', p_auto_rest_auth => TRUE ); ords.define_module ( p_module_name => 'test', p_base_path => '/test/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL ); ords.define_template ( p_module_name => 'test', p_pattern => 'itaot/', --is there anyone out there? p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL ); ords.define_handler ( p_module_name => 'test', p_pattern => 'itaot/', p_method => 'GET', p_source_type => 'json/collection', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'select * from hello_world' ); COMMIT; END;
Note: The ORDS PL/SQL API can be accessed via the ORDS (when you are logged in as your APP user (Jeff) and ORDS_ADMIN (when you are logged in as ADMIN) packages. I’m running this as JEFF, so all the calls are using ORDS().
Now to access my service, I can take the same URL I have for my SQLDev Web session, and replace everything after the /tjs/ with ‘test/itaot/’, like so:
But wait. I didn’t supply an OAUTH token or even any user credentials? What’s up with that?
Protecting our module with a privilege.
I’m going to create a new privilege (an ORDS priv, not a database priv), assign it to a ROLE (‘SQL Developer’), and use it to protect the TEST module we just created.
DECLARE l_roles OWA.VC_ARR; l_modules OWA.VC_ARR; l_patterns OWA.VC_ARR; BEGIN l_roles(1) := 'SQL Developer'; l_modules(1) := 'test'; ORDS.DEFINE_PRIVILEGE( p_privilege_name => 'thewall', p_roles => l_roles, p_patterns => l_patterns, p_modules => l_modules, p_label => '', p_description => 'priv required to hit itaot', p_comments => NULL); COMMIT; END;
With this executed, if we try to hit our service again…
Authenticating the request (our GET)
If you read the prior code block, you’ll have seen that we used the role ‘SQL Developer’ – this is a special ORDS role that is assigned to any Database User Authenticated session. So, if I use the JEFF credentials to make a request on a ‘tjs’ resource, I’ll be given the ‘SQL Developer’ role.
Relying on database credentials to access your RESTful Services isn’t ideal, so if you want to go a different route for securing your services, check out the OAUTH2 docs for ORDS.
Nice PL/SQL api, but…not fun?
You have a full RESTful Services development environment built into SQL Developer (on your desktop).
Or, if you want to stay in your browser while we work on the REST IDE screens in SQL Developer Web, you can use the APEX SQL Workshop.
Additionally, now that the Autonomous Database also includes native APEX support, you may also use the RESTful Services development pages in APEX to build and maintain your services and REST enabled objects.
Use the SQL Workshop in APEX to access your Oracle RESTful Services and REST Enabled objects.
Want to learn more about ORDS and RESTFul Services for Oracle Database?
In addition to the resources on oracle.com/rest and youtube.com I previously shared, I’ve also put together this Resource Page to help you get started with frequently asked questions and tasks you might be curious about.
Why this is not part of Oracle Documentation?
It is, what’s not here is covered here.
Of course please let me know if you find something missing or insufficient.
Request your help here. I am trying to enable REST Services for a test table created in ORDSDEMO schema on Oracle Autonomous Transaction Processing system on Oracle Cloud and the Database version is 18c.
I have created a test Schema, Module, template and a handler for my DEPT table in ORDSDEMO schema and used ORDS PL\SQL API for defining them.
SELECT uom.comments module_desc,
” || uos.pattern || uom.uri_prefix || uot.uri_template url,
WHERE handler_id = uoh.id) parameter_count
FROM user_ords_schemas uos,
WHERE uot.module_id = uom.id
AND uom.schema_id = uos.id
AND uoh.template_id = uot.id
AND uos.parsing_schema = ‘ORDSDEMO’
ORDER BY uom.comments, uot.uri_template
Sample HR Module,Departments Resource,List departments,GET,json/query,api/hr/v1/departments,0
My question here is : If the database is hosted on my machine, the hostname would be something like localhost:port. As this is on Cloud, what will be my hostname ? Below are the contents of my tnsname.ora file from Oracle Cloud Wallet.
Pull up your console, there will be a link for SQL Developer Web and APEX – those will give you the base URL. Where you /ords, make sure that is followed by your REST enabled schema alias, module, and then service template.
So something like this