In this tutorial, we will learn how to manipulate remote data using the same rest data sources, that we studied in our previous tutorials. For this tutorial, we will connect to the department table on the remote server to perform Insert, Update, and Delete DML operations.
And will also view the reflection of DML operations side by side on the local Apex instance in the object browser.
In this tutorial, we will be using two different Oracle Apex instances on different servers.
let's get started. This first instance also known as the local instance is running on the Oracle Apex server (in red color) and it will also be the source in this tutorial that will host the department table (in yellow color).
This is the second instance also called the remote instance is running on the max Apex server. We will create a form with an interactive report to view, insert, update, and delete data in the department's table using this instance.
let's start the proceedings. In the local instance access the restful services page from the SQL Workshop menu.
Click the button Register Schema with ORDS (in red color). Type your schema alias (in yellow color). Click the button Save Schema Attributes (in lime color).
A message Schema enabled for use with ORDS RESTful Services will appear indicating that now I can share my schema objects remotely. Click configure button (in yellow color) to check the Schema Alias (in lime color) which must be the same.
Now, create an auto-rest object. Select the option Enabled Objects (in red color). On this page, you have to Click a button named Create AutoREST Object, which should be (in green color). For the object type select the table option (in yellow color). and for the object select the Department table (in lime color). And click the Create button (in pink color).
Connect to the remote instance (in red color). Go to Shared Components. Click the option REST Data Sources (in yellow color). Create a new REST Data Source. Select the option from Scratch.
On the general screen set the following values. Put the endpoint URL (https://apex.oracle.com/pls/apex/schemaalias/dept/) by replacing the schema alias (in blue color) with your own.
The remote server screen comes up, in which the first part is the base URL of the chosen remote server object. The HTTP endpoint URL of the rest data source module is made up of the base URL. The second part is the service-specific URL path for the rest data source module. The HTTP endpoint URL is built by appending this to the base URL specified in the remote server object. Accepting the values of this wizard screen click next.
We do not want to implement authentication for this new rest data source module, so turn off the authentication and click the Discover button to see the remote data.
This is the remote data. Click the Create REST Data Source button to complete the process.
In the same remote instance, create an Interactive Report page. Set suitable page number and name. Include the form page. On the Data Source screen, select the REST Data Source option. Select the REST Data Source module from the respective list we have just created.
For the primary key column, select the department number column. And click the button Create Page.
After creating both report and form pages. Run the report page. This is the data from the department table. Click the Create button to create a new Department record.
On the form page set the following values. Click the Create button.
Our new Department record has been added to the list.
Connect to your local instance. Go to the object browser and select the department's table. You will see the new Department record added.
Edit new Department record. Change the location of this department. You will see an immediate change in the field.
Refresh your local instance screen. Change will also be reflected in the local instance.
Finally, let's delete this new Department.
As you can see our new department has vanished.
Let's check this in the local instance.
All the DML operations are performed successfully.
