Create an insert with dynamic number or columns in Oracle-CodePudding

Based on a given JSON, I need to create an insert statement in a stored procedure. For example I have this JSON:

{
    "tableName": "test_table",
    "columName":["col_1","col_2","col3"],
    "columnValue":["test1","test2","test3"]
}

The "columName" is an array with dynamic number of values and the "columnValue" is an array with the values for each position of the "columnName" array. What I need to do is to create an insert statement dynamically. For example, for the given JSON, the insert statement looks like this:

INSERT INTO test_table("col_1", "col_2", "col_3") VALUES("test1", "test2", "test3")

The test_table table has 27 columns. In JSON I receive data for one, two, three or all columns. How can I build the select statement based on dynamic number of columns.

I am using APEX_JSON.

Thank you!

CodePudding user response：

SET SERVEROUTPUT ON SIZE 999999
CLEAR SCREEN
DECLARE
  l_json_text      VARCHAR2(32767);
  l_json_values    apex_json.t_values;
  l_statement      VARCHAR2(32767);
  
  FUNCTION add_quotes (string_i VARCHAR2) RETURN VARCHAR2
  IS
  BEGIN
    RETURN '"' ||string_i||'"';
  END;
BEGIN
  
  l_json_text := '{
    "tableName": "test_table",
    "columName":["col_1","col_2","col3"],
    "columnValue":["test1","test2","test3"]
}
';  


  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );

   l_statement :=  'INSERT INTO ';
   l_statement :=  l_statement || apex_json.get_varchar2(p_path => 'tableName', p_values => l_json_values);
   l_statement :=  l_statement || '(';
   FOR j IN 1..apex_json.get_count(p_path => 'columName', p0 => 1, p_values => l_json_values) LOOP
     l_statement :=  l_statement || add_quotes(apex_json.get_varchar2(p_path => 'columName[%d]', p0 => j, p_values => l_json_values));
     l_statement :=  l_statement || ',';
   END LOOP;  
   l_statement :=  RTRIM(l_statement,',');
   l_statement :=  l_statement || ') values (';
   FOR j IN 1..apex_json.get_count(p_path => 'columnValue', p0 => 1, p_values => l_json_values) LOOP
     l_statement :=  l_statement || add_quotes(apex_json.get_varchar2(p_path => 'columnValue[%d]', p0 => j, p_values => l_json_values));
     l_statement :=  l_statement || ',';
   END LOOP;   
   l_statement :=  RTRIM(l_statement,',');
   l_statement :=  l_statement || ')';
   
   dbms_output.put_line(l_statement);
END;
/

INSERT INTO test_table("col_1","col_2","col3") values ("test1","test2","test3")


PL/SQL procedure successfully completed.

CodePudding user response：

declare
v_count number;
v_tableName varchar2(100 char);
v_columnName varchar2(100 char);
v_columnValue varchar2(100 char);
v_sqlStatement varchar2(2000 char);
v_json clob:= '{"tableName": "test_table","columnName":["col_1","col_2","col3"],"columnValue":["test1","test2","test3"]}';


begin
apex_json.parse(v_json);
v_count := apex_json.get_count(p_path=>'columnName');
v_tableName := apex_json.get_varchar2(p_path => 'tableName');
  FOR i IN 1..v_count loop
   
   v_columnName := apex_json.get_varchar2(p_path => 'columnName[%d]',p0 => i);
   v_columnValue := apex_json.get_varchar2(p_path => 'columnValue[%d]',p0 => i);
   v_sqlStatement := 'insert into' || v_tableName || '("' || v_columnName' || '" values("' || v_columnValue || '");' ;
   execute immediate v_sqlStatement;

  end loop;
end;