Convert a JSON object to an Insert statement to insert into a target table.

insertJsonObject.sql

-- Insert a JSON object to table 
-- Allow to specify the sequence for inserting data.
set SERVEROUTPUT ON
declare
   -- The test data comes from package function: demo_customers_rest_test.to_customer_record_test
   -- In parameter
    l_json_obj clob := '{ "customer_id": "1", "cust_first_name": "first name", ' || 
                    '"cust_last_name": "last name", "cust_street_address1": "address1",' || 
                    '"cust_street_address2": "address2", "cust_city": "city",' || 
                    '"cust_state": "AA", "cust_postal_code": "500",' || 
                    '"cust_email": "email", "phone_number1": "1234", "phone_number2": "5678",' || 
                    '"url": "url", "credit_limit": "200", "tags": "tag" }';
    -- In parameter
    l_src varchar2(100) := 'DEMO_CUSTOMERS';
    -- IN Parameter: the column to use the sequence
    l_primary_col varchar2(100) := 'CUSTOMER_ID';
    -- IN sequence name
    l_seq varchar2(100) := 'demo_cust_seq' || '.nextval';
    
    type type_col_values is table of varchar2(200) index by varchar2(200);
    l_col_values type_col_values;
    l_key varchar2(100);
    l_col_list clob;
    l_val_list clob;
    l_insert_stmt clob;    
    l_new_id number;

begin
  -- Get the columns from the table 
  apex_json.parse(l_json_obj);
  for col_info in (select column_name, data_type 
    from user_tab_columns 
        where table_name = upper('demo_customers'))
  loop
    l_col_values(col_info.column_name) := apex_json.get_varchar2(lower(col_info.column_name));
  end loop;
  
  -- print out the collection
  l_key := l_col_values.first;
  -- dbms_output.put_line('first key: ' || l_key);
  while l_key is not null loop
    dbms_output.put_line(l_key || ': ' || l_col_values(l_key));
    l_key := l_col_values.next(l_key);
  end loop;
  
  -- handle the primary key
  --l_col_values('CUSTOMER_ID') := DEMO_CUST_SEQ.nextval;
  l_col_values('CUSTOMER_ID') := l_seq;
  
  -- create the column and value lists
  l_col_list := '(';
  l_val_list := '(';
  for col_info in (select column_name, data_type 
    from user_tab_columns 
        where table_name = upper(l_src))
  loop
    l_col_list := l_col_list || col_info.column_name || ', ';
    case col_info.data_type 
        when 'VARCHAR2' THEN
            l_val_list := l_val_list || '''' || l_col_values(col_info.column_name) || '''' || ', ';
        else
            l_val_list := l_val_list || l_col_values(col_info.column_name) || ', ';
    end case;
    
  end loop;
  
  l_col_list := SUBSTR(l_col_list, 1, length(l_col_list)- 2);
  l_col_list := l_col_list || ' )';
   
  l_val_list := SUBSTR(l_val_list, 1, length(l_val_list)- 2);
  l_val_list := l_val_list || ' )';
  
  dbms_output.put_line(l_col_list);  
  dbms_output.put_line(l_val_list);
  -- create the insert statement
  l_insert_stmt := 'insert into ' || l_src || l_col_list || ' values ' || l_val_list;
  l_insert_stmt := l_insert_stmt || ' return ' || l_primary_col || ' into :1';
  EXECUTE IMMEDIATE l_insert_stmt returning into l_new_id;
  
  -- response to the caller
  if sql%rowcount > 0 then 
    dbms_output.put_line('Insert successfully ' || l_new_id );
  else 
    dbms_output.put_line('Insert failure');
  end if;
end;
/