Скачать или смотреть How to Create an INSERT Statement with a Dynamic Number of Columns in Oracle

Learn how to build dynamic `INSERT` statements in Oracle based on JSON input, especially when dealing with varying numbers of columns and values.
---
This video is based on the question https://stackoverflow.com/q/73287834/ asked by the user 'XLD_a' ( https://stackoverflow.com/u/5559174/ ) and on the answer https://stackoverflow.com/a/73288357/ provided by the user 'Koen Lostrie' ( https://stackoverflow.com/u/4189814/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Create an insert with dynamic number or columns in Oracle

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Creating a Dynamic INSERT Statement in Oracle from JSON

When working with databases, one common task is inserting data into tables. However, things can get tricky when the number of columns and corresponding values varies. In this guide, we'll address how to generate a dynamic INSERT statement in Oracle based on JSON input. Instead of manually specifying every column, we will automate the process, making our database operations more flexible and powerful.

The Problem at Hand

Suppose you receive data in the form of JSON and you need to use this data to create an insert statement in Oracle. A typical JSON data structure looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Here, the tableName specifies which table to insert into, while columName is an array of the column names where data will be inserted. The columnValue array contains the actual values for these columns.

Your goal is to dynamically create an INSERT statement that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

How can we automate this process, especially when the number of columns and values can change?

Creating the Solution

We'll employ a PL/SQL block to parse the JSON input and construct the desired INSERT statement dynamically. The code example below outlines how to accomplish this. Before running the code, ensure you have access to the APEX_JSON package, which should be available in your Oracle environment.

Step-by-Step Breakdown

Set up environment:
We start by configuring the server output and clearing the screen.

[[See Video to Reveal this Text or Code Snippet]]

Declaring necessary variables:
We will declare the variables required to hold the JSON text, parsed values, and the final SQL statement.

[[See Video to Reveal this Text or Code Snippet]]

Function to add quotes:
A small helper function named add_quotes to encapsulate values in quotes.

[[See Video to Reveal this Text or Code Snippet]]

Parsing the JSON:
Here, we specify the JSON string and parse it using apex_json.parse.

[[See Video to Reveal this Text or Code Snippet]]

Building the INSERT statement:
The next section constructs the INSERT statement by fetching the dynamic columns and values.

[[See Video to Reveal this Text or Code Snippet]]

Output the result:
Finally, we print out the dynamic INSERT statement.

[[See Video to Reveal this Text or Code Snippet]]

Complete Code Block

Here is the complete PL/SQL block:

[[See Video to Reveal this Text or Code Snippet]]

Running this PL/SQL block will yield the following output:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By utilizing the APEX_JSON package and PL/SQL, we can effectively generate INSERT statements for a dynamic number of columns based on JSON input. This method not only saves time but also adds flexibility to how we handle database operations in Oracle. You can now handle varying data structures seamlessly, enhancing your database management capabilities.

Feel free to experiment with this approach in your own projects and adapt it as necessary to fit your requirements!