The first question you might ask is 'why on earth would I want to create tables in MCS, don't we have database cloud service and other sources for that?!?'

Let me start by saying I agree with that. However, in this project we are starting with a simple API and we want to make them available as quickly as possible. The data will move (eventually) to the proper back-end system and in MCS we will use a connector to access this data that will be exposed on the Oracle Service Bus. However, at the moment the system does not contain the data and the Oracle Service Bus is not exposing services for this particular system yet.

To save cost, minimize complexity and maximize time to market, we decided to use the database platform API.

You can create database tables on the fly, using the Database_CreateTablesPolicy environment setting. This will cause a table or a column to be added or resized when you insert a row using custom code if it does not exist yet.

implicitOnly: only implicit creation of database tables using custom code is allowed, the database management api can't be used;

none: curtails implicit calls from custom code.

This documentation is a bit unclear so let me elaborate on that:

Value

Use API in custom code

Use implicit calls in custom code

allow

yes

yes

implicitOnly

no

yes

explicitOnly

yes

no

none

no

no

These values are used to control the privileges for custom code, it does not control calling the database management API from outside of MCS (postman, curl etc).

There are several disadvantages to this approach:

You can accidentally end up with multiple columns because of spelling errors ('address' and 'adress' for example);

When unit testing custom code with 'faulty' data, instead of failing with the error you would get into your production environment (which is recommended by Oracle, to switch it off in production) you create new columns and the test fails with a different error (if it fails at all);

We use the environment that we are working as a production environment.

We decided to use "explicitOnly" and use the REST APIs to create, update and remove tables with Postman. However when I used one of the APIs I got the following response:

When creating a database table from outside the custom code (using the REST API and postman for example), you need to call it with a user that has the role Mobile_DbMgmt. Unfortunately, there is no easy way to check your role from inside MCS, as can be seen in this picture

No easy navigation to inspect or change your role from within MCS

So, I opened a new tab and navigated to cloud.oracle.com and signed in again. This brought me to my service dashboard, and offers the opportunity to manage users, using the "users" button in the upper right hand corner.
Click users and find yourself. Check your roles and add [environment name] Mobile Database management to the roles.

Now I was able to create the table 😊.NB: According to the documentation the default value is 'allow'. In our instance the value was set to 'explicitOnly', so make sure you check the value when you use the database platform API.