What PL / SQL libraries for JSON auto-generation do you recommend?

Question

Are there any good PL / SQL libraries for JSON that you worked with and found useful?

In PL / SQL, I have to tediously pass code returning JSON values ​​to JavaScript functions. I found one PL / SQL library for automatic JSON generation, but it doesn’t do exactly what I need. For example, I could not extend the basic functions in the library so that it returns the complex tree-like JSON data structure necessary for the JavaScript tree component that I use.

Remarks:
The system, which has been in operation for 8+ years, has been designed to use PL / SQL for CRUD and most of the business logic. PL / SQL also generates 90% of the presentation level (HTML / JavaScript) using the PL / SQL mod. The remaining 10% is data submitted through Oracle Reports Builder.

Answer 1

@ Geoff-

The system, which has been running for 8 years, was created to use PL / SQL for CRUD and most of the business logic. PL / SQL also generates 90% of the presentation level (HTML / JavaScript) using the PL / SQL mod . The remaining 10% is report data made through Oracle Reports Builder.

Thus, there is no application code, as you would see in more modern, more advanced systems. I want to do everything right, I just do not have such luxurious organizational restrictions.

Answer 2

I wonder why you do not want to output data from Oracle to any application code and do JSON there?

Answer 3

Ouch - creating your interface in PL / SQL. You have my sympathy.

I have never done anything like this, but Googling found this page (which is also linked to the json.org page).

Answer 4

A relatively new library called PLJSON (without a slash) is on GitHub . We use it in a rather large project in production and do not experience any problems with it. Parsing is slow, but this can be expected.

Disclaimer: I wrote this. If you find errors or suggestions, let me know.

Answer 5

If someone is still interested in serving JSON using PL / SQL, I just completed the PL / SQL data service structure called BackLogic . This is the complete structure of the REST web service. It includes an SQL utility to create a complex JSON structure from REF CURSOR, including the "complex tree structure of JSON data required by the JavaScript tree component" mentioned in the original question, which the early PLJSON structure is not able to execute.

I see a bright future for PL / SQL when creating a REST API. Until recently, impedance attitudes towards objects were mainly dealt with by mid-level ORM structures. BackLogic solves this problem in the database and thus can create the complex JSON structures needed for the user interface infrastructure. Here is a link to the BackLogic User Guide . In section 5.3, you can find some examples of unverification.