Page Comparison

CREATE OR REPLACE PROCEDURE usp_ConvertJSONToMixedTypes
(
  ippJsonData IN CLOB -- In parameter must be named this. CLOB to allow larger data sets
) AS

sample_vchar VARCHAR2(1000);
sample_number NUMBER;
sample_date VARCHAR2(1000);
converted_date DATE;

BEGIN
    -- Access first row of data, assuming we've included headers,
    -- process each column and extract the first of the raw and formatted value pair
    -- If we go outside bounds of array we will get an error,
    -- so this approach is ok if were happy with dimensions of data and table we're sending it to.
    -- See other example for methods to determine if we're at a valid point in array
    SELECT JSON_VALUE(ippJsonData, '$[1][0][0]') INTO sample_vchar FROM dual;
  
    -- Can explicitly tell JSON_VALUE to return a NUMBER (default VARCHAR2)
    SELECT JSON_VALUE(ippJsonData, '$[1][1][0]' RETURNING NUMBER) INTO sample_number FROM dual;
    SELECT JSON_VALUE(ippJsonData, '$[1][2][0]') INTO sample_date FROM dual;

    -- Will explicitly need to do type conversion here, based on prior knowledge of what to expect
    converted_date:= TO_DATE(sample_date, 'DD/MM/YYYY HH24:MI:SS');

    INSERT INTO example_table (varchar_col, number_col, date_col)
                      VALUES (sample_vchar, sample_number, converted_date);

END usp_ConvertJSONToMixedTypes;

CREATE PROCEDURE usp_ImportJSON(IN ippJsonData JSON)
BEGIN
  DECLARE chartest varchar(255) default null;
  DECLARE chartest2 varchar(255) default null;

  set chartest1 = JSON_UNQUOTE(JSON_EXTRACT(ippJsonData, '$[1][2][1]'));
  set chartest2 = JSON_UNQUOTE(JSON_EXTRACT(ippJsonData, '$[2][3][0]'));

  INSERT INTO example_table (col1, col2) VALUES (chartest1, chartest2);
END