ODI, Oracle

Using a RESTful API to load JSON data with Oracle Data Integrator

by jb

The goal of this post is to show how to consume data from a public REST API and write the JSON data to disk.

To demonstrate the setup we need a running and patched installation of Oracle Data Integrator 14c. The process on how to install and patch ODI 14c are described here and here.

Adding the RESTful Service Data Source

We will use https to connect to the public REST server. To add the Data Source we start ODI Studio and go to Topology => expand Technologies => right click on RESTful Service => New Data Server. We can fill the fields as shown (https://openlibrary.org):

Testing the Data Source

We can test the new Data Source by clicking “Test Connection”. If we select “OracleDIAgent” as the source of the outgoing https request the connection should be fine and we should see:

If we choose to connect without the Java EE Agent (OracleDIAgent), meaning the https request is done from the ODI Studio integrated agent (by selecting “Local (No Agent)”) we probably get the following error:

ODI-26039: Connection failed.
oracle.odi.runtime.agent.ExecutionException: oracle.odi.core.exception.OdiRuntimeException: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

This is because the SSL certificate of https://openlibrary.org is not in the truststore ODI Studio uses. To add the certificate we can use the script from here:

# as root:
cacerts.sh openlibrary.org

After a restart of ODI Studio also this connection will be successful.

Adding the Physical Schema

In the Physical Schema we will specify the rest of the URL to get the (book) data from the REST server. We add the Physical Schema by a right click on the new OpenLibraryDS and selecting New Physical Schema. We can confirm the “Unexpected error” by clicking OK. In the Resource URI field we can enter /api/books. Next we go to the Operations tab and click the green plus sign to add the operation to get the book data. In the fields we enter the following data:

  • Name: getBookData
  • Method: GET
  • Query Parameters / Single Line format: bibkeys=ISBN:0201558025,LCCN:93005405&format=json

We can now save the Physical Schema data and test the complete REST call by selecting “Test Restful Service”. If that succeeds we are lucky and probably some bugs have been fixed, but we rather see the following error on the ODI Studio console window:

java.lang.NoClassDefFoundError: com/jayway/jsonpath/Configuration
Caused by: java.lang.ClassNotFoundException: com.jayway.jsonpath.Configuration cannot be found by oracle.odi.navigator_14.1.2

As we can see, the class is missing from ODI studio. To add the missing jar files to ODI studio we can use the procedure described here. To avoid going through all the errors we can simply add the follwoing 3 jar files and restart ODI Studio.

cat >>/home/oracle/Oracle/Middleware/Oracle_Home/odi/studio/bin/odi.conf <<EOF
AddJavaLibFile ../../../oracle_common/modules/thirdparty/json-path-2.9.0.jar
AddJavaLibFile ../../../oracle_common/modules/jersey-media-multipart-2.45.jar
AddJavaLibFile ../../../oracle_common/modules/mimepull-1.9.14.jar
EOF

If we repeat the test of the Restful Service Operation now we will see that the call to the REST API was successful:

Adding the Logical Architecture Configuration

Adding the Logical Agent

If the Logical Agent does not already exist we can add one as follows. We go to Topology => Logical Architecture => right click on Agents => New Logical Agent. We set the name to LogAgent1 and select OracleDIAgent in the Global field. Click Save.

Adding the Logical Schema for the RESTful Service Physical Schema

We go to Topology => Logical Architecture => right click on Technologies => right click on RESTful Service => New Logical Schema. We set the Name to LS_REST and select OpenLibraryDS./api/books in the Glogal field. Click save.

Creating the Oracle Data Integrator Project

To create the Project that can be run manually or scheduled to run at certain times we go to the Designer Tab and click New Project. We give the Project a name (Project_REST) and click save to create the Project.

The next step is to create a package that will contain the steps to retrieve data from the REST service and to write that data to disk. We expand ProjectREST and First Folder => right click on Packages => New Package. We give it a name (PackageREST2Ora) and click save.

Adding OdiInvokeRESTfulService to the project

In the Toolbox we select OdiInvokeRESTfulService and place it in the white window with a left click. In the Properties field (General tab) we make the following adjustments:

  • Context: Global
  • Logical Schema: LS_REST
  • Main Operation: getBookData (this is the name of the Operation defined in the REST Data Source)
  • Response File: /home/oracle/rest2ora/rest.txt
  • Append to Response File: No
  • Trace File: /home/oracle/rest2ora/rest.trc
  • Append to Trace File: No

Click save to save the changes.

Running the project manually

We can now run the project by clicking the green play button on the toolbar. We select the Agent and click on OK. We can check in the Operation Tab => Session List => Date to see if the run was successful or if errors occured.

We can also check the contents of the written JSON file and the trace file (which might contain useful information in case something goes wrong):

cat ~oracle/rest2ora/rest.txt


cat ~oracle/rest2ora/rest.trc
Sample Output (click to expand): 
[root@lin7 ~]# cat ~oracle/rest2ora/rest.txt
{
   "ISBN:0201558025" : {
      "bib_key" : "ISBN:0201558025",
      "info_url" : "https://openlibrary.org/books/OL1429049M/Concrete_mathematics",
      "preview" : "full",
      "preview_url" : "https://archive.org/details/concretemathemat00grah_444",
      "thumbnail_url" : "https://covers.openlibrary.org/b/id/135182-S.jpg"
   },
   "LCCN:93005405" : {
      "bib_key" : "LCCN:93005405",
      "info_url" : "https://openlibrary.org/books/OL1397864M/Zen_speaks",
      "preview" : "borrow",
      "preview_url" : "https://archive.org/details/zenspeaksshoutso0000caiz",
      "thumbnail_url" : "https://covers.openlibrary.org/b/id/240726-S.jpg"
   }
}[root@lin7 ~]#
[root@lin7 ~]#
[root@lin7 ~]# cat ~oracle/rest2ora/rest.trc
Sep 05, 2025 1:43:42 PM org.glassfish.jersey.filter.LoggingFilter log
INFO: 1 * Sending client request on thread SimpleAsyncTaskExecutor-1
1 > GET https://openlibrary.org/api/books?format=json&bibkeys=ISBN%3A0201558025%2CLCCN%3A93005405

Sep 05, 2025 1:43:45 PM org.glassfish.jersey.filter.LoggingFilter log
INFO: 1 * Client response received on thread SimpleAsyncTaskExecutor-1
1 < 200
1 < access-control-allow-method: GET, OPTIONS
1 < access-control-allow-origin: *
1 < access-control-max-age: 86400
1 < Connection: keep-alive
1 < Content-Type: application/json
1 < Date: Fri, 05 Sep 2025 11:43:45 GMT
1 < Referrer-Policy: no-referrer-when-downgrade
1 < Server: nginx/1.28.0
1 < Transfer-Encoding: chunked
1 < x-ol-stats: "IB 2 0.082 MC 3 0.010 TT 0 0.096"
{"ISBN:0201558025": {"bib_key": "ISBN:0201558025", "info_url": "https://openlibrary.org/books/OL1429049M/Concrete_mathematics", "preview": "full", "preview_url": "https://archive.org/details/concretemathemat00grah_444", "thumbnail_url": "https://covers.openlibrary.org/b/id/135182-S.jpg"}, "LCCN:93005405": {"bib_key": "LCCN:93005405", "info_url": "https://openlibrary.org/books/OL1397864M/Zen_speaks", "preview": "borrow", "preview_url": "https://archive.org/details/zenspeaksshoutso0000caiz", "thumbnail_url": "https://covers.openlibrary.org/b/id/240726-S.jpg"}}

[root@lin7 ~]#