How to pass JSON type data as an input parameter in a Stored procedure-CodePudding

I have one requirement wherein I need to pass JSON as an input parameter and process the data accordingly.

JSON data:

{
"Table name": "test_table",
"Column name":["column_1","column_2"],
"Column_value":["test_data","1"]
}

I need to write a procedure with this JSON as an input parameter.

Then based on the table name and column name it should insert the particular column value into the respective columns of a table.

Pseudo Code:

Store JSON in one table with the table structure as

   table_id |Table_name | Column_name | Column_value

Then pass table_name, column_name, and column_values JSON format as input parameters.

Then extract tables, columns, and column_value from the input parameter and load in into the respective table.

Will this work? As I am not aware of how to handle JSON in a stored procedure or if anyone has any clue of how to start with this it would help me.

Tool Used: SQL Developer (18c)

CodePudding user response：

You can use:

CREATE PROCEDURE load_data(
  i_json IN CLOB
)
IS
  v_obj      JSON_OBJECT_T := JSON_OBJECT_T(i_json);
  v_tbl      VARCHAR2(20)  := v_obj.get_string('Table name');
  v_cols     JSON_ARRAY_T  := v_obj.get_Array('Column name');
  v_vals     JSON_ARRAY_T  := v_obj.get_Array('Column_value');
  v_sql      CLOB := 'INSERT INTO ';
  v_sql_vals CLOB := ') VALUES (';
BEGIN
  v_sql := v_sql || DBMS_ASSERT.SQL_OBJECT_NAME(
                      DBMS_ASSERT.ENQUOTE_NAME(v_tbl, FALSE)
                    );
  v_sql := v_sql || ' (';
  FOR pos IN 0 .. v_cols.get_size() - 1
  LOOP
    IF pos > 0 THEN
      v_sql := v_sql || ',';
      v_sql_vals := v_sql_vals || ',';
    END IF;
    v_sql      := v_sql      || DBMS_ASSERT.ENQUOTE_NAME(v_cols.get_string(pos), FALSE);
    v_sql_vals := v_sql_vals || DBMS_ASSERT.ENQUOTE_LITERAL(v_vals.get_string(pos));
  END LOOP;
  v_sql := v_sql || v_sql_vals || ')';
  
  EXECUTE IMMEDIATE v_sql;
END;
/

If you have the table:

CREATE TABLE "test_table" (
  "column_1" VARCHAR2(20),
  "column_2" NUMBER
);

Then you can use:

BEGIN
  load_data('{
"Table name": "test_table",
"Column name":["column_1","column_2"],
"Column_value":["test_data","1"]
}');
END;
/

And the table will contain:

SELECT * FROM "test_table";

column_1 column_2

test_data 1

column_1	column_2
test_data	1

db<>fiddle here