How to Add an API using a PLSQL Definition

Modified on Mon, 15 Feb, 2021 at 11:16 AM

Illuminate has several many ways to create APIs for use at your institution. In this case, we will walk through creating an API using a SQL definition, which can be started by clicking on the SQL option on the API Builder screen. This selection will move you to the start of the API construction process as shown below.

This screen allows you to create the API itself by writing in the SQL query you want it to run. For ease of use, this screen is similar to the screen used in other types of API creation, in this case, the API method can be GET, POST, or PUT due to the nature of this API type. The completed query will then retrieve the specified data from your chosen source. While these choices are drawn from those specified in the manage connections screen, you can always come back and edit the API after construction if your create it before the source is configured.

In addition, you can also create tags for you new API here that you can use in the search bar of the builder's main screen. Simply type the desire tag in the field before taping the 'Tags/label' button on the right to create a tag with that text.

For the query itself, Illuminate is configured to make the construction as easy as possible. In this example, we will be putting in the following code:

procedure scott.PKG_EMP.INSERT_EMP(
  P_ENAME varchar2,
  P_JOB VARCHAR2,
  P_MGR VARCHAR2,
  P_HIREDATE VARCHAR2,
  P_SAL VARCHAR2,
  P_EMPNO VARCHAR2,
  P_COMM VARCHAR2,
  P_HIREDATE VARCHAR2,
  P_DEPTNO VARCHAR2,
  P_STATUS OUT varchar2,
  P_STATUS_MSG OUT varchar2
);

After which, the data is automatically formatted and set to be configured by selecting the 'Generate Model' button below the text input field.

Once you are done, click 'Save & Next' in the bottom right. This will open up an optional screen to use XSLT transformation on your data. If you do not intend to do so, simply continue on to the next screen.

At this point, all you need to do is confirm how you want the API to output your data and then choose a consumer to send the data to. For the ease of the user, Illuminate will also display an empty example of the output for you to verify before you finalize your choice. Once you are satisfied, click 'Apply To Consumer' to go to the last step.

In this final step of the API builder, the consumers you have created in the Clients and Consumers menu will be displayed. Simply select to which consumers you wish to send the output data and select next to complete the construction of the API and move on to the publishing step. This specific step will allow you to publish your API to the Illuminate community at large and is entirely optional. Click 'Skip & Continue' to view the automatically generated documentation for your API before clicking 'Done' to leave the API builder.

Now that your API is complete, you can edit any details you want to change, delete it, try it out with real data, or authorize it for a consumer. Authorizing the API allows the consumer to use the API to draw data from your system and can be reversed by simply selecting the authorization again.