REST APIs for Oracle Database, everything you need to know

This is my Resource page for developers wanting to interact with an Oracle Database or it’s contents with HTTPS and/or REST APIs.

Oracle makes the following technology and interfaces available, for free, for its databases.

1. REST APIs – for your data
2. REST APIs – for database instance management & monitoring
3. REST SQL – REST API for ad hoc SQL against Oracle or MySQL
4. Oracle Database API for MongoDB
5. SODA for REST, REST APIs for your JSON Document Store
6. SQL Developer Web
7. Middle Tier for APEX
8. PL/SQL Gateway, a replacement for MOD_PLSQL

How does it work?

Our free mid-tier Java application provides access to your Oracle Database via HTTPS. Your GET and POST requests are received and responses are generated by harnessing the power of SQL and PL/SQL executed in the database. Responses are RESTful – they are rich with resource links, proper HTTPS status codes, and JSON bodies.

Developers can either request their resources be made available via REST automatically (no code required), or can write their own REST APIs by simply providing the SQL or PL/SQL behind the endpoints.

Oracle REST Data Services (ORDS)

The technology making this possible is commonly know as ‘ORDS.’

• Product Page
• Download
• Follow ORDS on Twitter

ORDS can accommodate one or more of your databases. HTTPS requests are routed to the appropriate database, the appropriate database code is executed, and results are returned, formatted as JSON and accompanied with HTTPS status codes, headers, and links.

REST APIs backed by SQL statements are automatically paginated.

HTTPS request and response

Resources

A resource page is useless without resources! I have over 160 posts on all things REST APIs for your database, so I put together these lists to make it easier for you to find what you’re looking for.

General Info

• ORDS 101: HTTP, JSON, & REST, & how ORDS ties it all for Oracle – slides & video 
• RESTful Services and AUTOREST Examples – slides and video
• AUTOREST – Low Code solution for REST access to your data and PL/SQL
• REST Enabling schemas
• Developing a RESTful Service with SQL Developer Web
• Hello World – quickly publish a REST API to test ORDS
• Frequently Asked Questions (FAQ)
• Architecture Overview
• Best Practices Guide for Database REST APIs
• Developing a RESTful Service with SQL Developer Desktop’s IDE (Video)

Troubleshooting

• Pool Errors – user is expired, locked, or password is bad
• Debugging REST APIs
• Enabling DB Error Messages at the schema level

Examples

• Query Parameters/Filtering (EMPLOYEES || BEER)
• PL/SQL (4 options for working with PL/SQL in your REST APIs)
• Including Links in your REST API responses
• Inserting a Row to a Table: the Definitive Example!
• Generate your own JSON
• Upload Files as Blobs (and download them again!)
• Parameters
• Paging your SQL Query Results
• Using a POST to run Queries
• Preventing 500 responses & using PL/SQL Exceptions

Python Examples

• Python program to call REST Enabled SQL…with OAuth2 Client/Secret
• Python program to batchload CSV to AutoREST Table API

SQL Developer Web (aka Database Actions)

• Feature Screenshots
• Autonomous Database in the Oracle Cloud
• On Premises
• Loading Data
• Performance Hub
• Relational Diagrams (data modeler)
• Security & OAuth2
• REST Development
• JSON (SODA) Document Management

DBAPI – Managing your Oracle Instance

• Introduction
• Slides
• Data Pump Exports
• Data Pump Imports
• PDB Lifecycle Management

Your Favorite Content

These are the most popular posts over the past 18 months…

1. THIS PAGE – you’re already here!
2. How to Update the ORDS_PUBLIC_USER Password
3. ORDS FAQ
4. ORDS Architecture Deployment Overview
5. Returning Raw JSON
6. Query Filtering Examples
7. How Paging Works
8. Forward Responses
9. Calling a PL/SQL Function
10. The Easy Way to Protect All Services in a Schema

Can’t find what you’re looking for?

ALL of my REST API posts (also good for RSS feeds)

We have 3 other great blog resources for you when it comes to REST APIs:

• GitHub Examples
• VP of Database Tools Development and co-creator of ORDS, Kris Rice
• ORDS lead developers, Colm Divilly & Peter O’Brien

Oracle Database REST APIs TV

A collection of great dance songs. Well, that’s not accurate, I can’t sing or dance, but I have taken time to record myself doing REST API talks for YouTube. It’s not the most active channel, but I do take requests!