API Automation tutorial for Oracle Databases

Table of contents

In this tutorial we will show you how DreamFactory’s REST API Automation platform can dramatically speed time-to-value for your enterprise mobilization initiatives both with internal development resources and strategic development partners. We will demonstrate:

  • Instantly transform a production Oracle database into a REST API Backend
  • Rapidly assembling robust backends in our API Sandbox by orchestrating DreamFactory API Automation with commercial APIs
  • Virtualizing backend resources to securely stage partner development and deploy in your production environment with no changes to the app or your production database

Make sure you have DreamFactory installed, which can be installed anywhere and connect with any datasource.

Read more about the theory behind building APIs from your Oracle Database using DreamFactory



1. In the image above, we've already installed DreamFactory and are looking in the "Services" tab. Services are your data sources that you're connecting to. DreamFactory virtualizes the data source so that you can move things around and not loose anything. We already have data sources defined in the example above, and will begin by opening up our Oracle instance that we created.

2. Under the "Config" tab you are able to configure and move things around such as the Host, Port Number and Database. Once you have DreamFactory connected to Oracle we introspect the database to figure out what tables are there, what fields are a part of it, as well as what stored procedures and functions are there.



1. Move over to the "Schema" tab and select the Oracle Server under "Service". You will then see a list of tables associated with it. When you click on a table, you can see the portions that are a part of it, information about the table itself, and even the alias of the table if you don't want to expose the table name itself. We also include basic features like labels and plurals. You can then mark your primary key by selecting the key icon when you scroll down.


DreamFactory figures out foreign key relationships as they are defined by default and are then displayed for you when you scroll down under the "Schema" tab.

API Docs

1. We conveniently have a tab called "API Docs" that generate documentation around your APIs using Swagger. When you click into Oracle, you will see that you have all of the operations that we Automatically generate and expose to you. Every part of Oracle is now available through our REST API and is automatically generated for you. You can now do schema operations, manage data elements, and other store procedures and functions.

YouAs your table structure changes you don't have to write code to bring additional tables in. Just refresh your service cache and DreamFactory immediately pulls in the updated information in your database.


2. When you go into one of these structures, it is very SQL like. For example you can design what fields you want, and we automatically take care of foreign key relationships, execute complex filters, place limits and offsets, and much more! When you create a table and submit, we go into your database and bring information back. That information is then displayed in the image below.


3. In the "Request URL" you can see the following information: your server, version, service name, what you are calling for and the name of your table. When you request that, it brings back all of your information as JSON. You can easily change the amount of information by customizing limits, filters, orders and other features. It is extremely flexible and you can make it as complex as you want. For example, you can call for just the table, or also include related information. When related information is requested, DreamFactory goes in and fetches information from the child tables itself and then brings back all related records. You do not have to manually construct this request or API, or code anything to bring in related tables - it's automatically done by DreamFactory.

Establish a foreign key relationship

1. If you do not have an established foreign key relationship, DreamFactory's Data Mesh feature will perform this for you, even if it is in a completely different database. This feature can go across databases and bring back related information. To do this without any coding, go back to your "Schema" tab, select your Oracle Server under "Service", select your table and you will see the related fields. You can then overload your primary key by scrolling down and selecting "Foreign Key". It will then ask you to select your Reference Table and Field. DreamFactory will then connect with that Reference ID Field. This is why we have our virtual key references defined, so select both Virtual Foreign Key and Virtual Reference Service. You can select your Reference Service, Table and Field.


2. Once you go back to your "API Docs" tab, and you can do the same query but it's going to go query not just Oracle, but your other database as well and quickly bring back a consolidated record set of information. Ask for the same table information, and get Related *. So you can even fetch information from a completely different table, which exists in a different database, which exists in a different database server or server type, and have it be brought back.

Custom Scripting

1. This kind of data mesh capability can be done in just a few clicks with no code! But don't worry, you have the scripting capability to do all of this and can actually write post process scripts by writing just a few lines of code by going under the "Scripts" tab. You can do much more than just Databases under the "Scripts" tab including pre process and post process script and to extend the normal functionality DreamFactory provides. We support extremely flexible ways to connect different services without having to do much work.

To complete the picture, check out easy ways to define roles and manage accessibility for even more functionality.

We hope you enjoyed this tutorial on how you can use our platform for APIs to connect to any type of datasource, look at schema, document your API, customize or extend functionality through custom scripting and play with security facilities to lock down who gets access to what. To watch a video webinar of this process, please click here. 

Just connect your Oracle database to start building applications for the web, mobile or IoT using robust, documented, easy to use reusable APIs. DreamFactory REST API platform will help you transition and navigate the pain points from a client server world to a modern technology stack.