Using Oracle JSON_Table to Insert Suppliers and Invoices

The JSON_TABLE function in Oracle can be used to insert JSON data into Oracle E-Business Suite. This article will show you how to insert suppliers, supplier sites, and invoices from a JSON dataset.

The Initial Request

Let’s pretend have a list of people that we owe a refund. We need to import these people into Oracle E-Business Suite, create an invoice for them, and cut each one a check. But, we have several hundred, so we don’t want to enter these by hand.

So, how do you do this? Here is one option:

Let’s use JSON!

Why? A couple reasons:

  1. Oracle’s JSON_TABLE function makes JSON easy to use in Oracle 12c
  2. An Excel spreadsheet is easily converted to JSON
  3. It’s always fun to learn something new

The JSON Data

If you’ve never encountered JSON in the wild, it’s still pretty to figure out just by looking at it. JSON is split up into key/value pairs, separated by a colon (:).  JSON is much more compact and flexible than XML, and it becoming very popular on the web. Here is a sample of what JSON data looks like:

While the above is valid JSON data, I have a much bigger file, full of 100 fake customers available here: SMJ_JSONData_file.

JSON is pretty easily converted from any other data source. Oracle database (as of 12c) can export JSON. And, if your accountant just gives you an Excel spreadsheet it can be easily converted with online JSON conversion tools too.

The PL/SQL Code using JSON_TABLE

Now we need an outline of what is going to happen in this code. Here is a quick summary of what we need to do:

  • Declare variables
  • Load external file of JSON data
  • Build cursor using Oracle’s JSON_TABLE function
  • Start a loop for each JSON record
    • Add a supplier record using create_vendor API
    • Add a supplier site record using create_vendor_site API
    • Insert an invoice into AP_INVOICES_INTERFACE table
    • Insert an invoice line into AP_INVOICE_LINES_INTERFACE
    • Output some messages so we know if everything is working

Here is some PL/SQL which will do the trick:

The additional points to consider

JSON_TABLE made it really easy to build a cursor from raw JSON data. After that, it was pretty simple PL/SQL. If you’re copying and pasting the above code, here are a few things to consider:

  1. Verify the location of the JSON file on the server (see line 13) and that it has the correct permissions
  2. Many of the DBMS_OUTPUT.put_line lines are there simply for instruction and debugging
  3. Make sure you have valid values for the following:
    • vendor_type_lookup_code
    • pay_group_lookup_code
    • org_id
    • accts_pay_code_combination_id

But, this works and will add 100 new suppliers and 100 new invoices from the JSON source data. Since many of the modern APIs return data in JSON format, JSON_TABLE will be an important and useful function in your repertoire.