codemonth.dk

One project every month - making stuff better ...

Create your Oracle test data using JSON and PLSQL

For a long time the only way to create a generator using the testdata_ninja package, was using a pure text based syntax. It was using special characters and was maybe a bit obscure and not user friendly. The reason for this was that the databases I needed this package on, was running Oracle 10 and 11. So I needed the package to be compatible with those versions, and JSON is not supported natively. Looking at the requirements, it would be a good fit for JSON though. So recently when I needed to run this on Oracle version 12, I had the chance to finally add JSON support. It makes it a lot more easy to write the generator definitions, as you can see in the below examples.

Let us start out with an example o how the generator definition would look like in the classic text based format. We will create a table, with a name, creditcard and a creditcard number.


my_name#varchar2(50)#person_random.r_name
@my_creditcard#varchar2(20)#finance_random.r_creditcard
@my_creditcardnum#number#finance_random.r_creditcardnum
That would be the format for a table like that, and we would create it with the following command:

declare
  p_format varchar2(4000) := 'my_name#varchar2(50)#person_random.r_name@my_creditcard#varchar2(20)#finance_random.r_creditcard@my_creditcardnum#number#finance_random.r_creditcardnum';
begin
  testdata_ninja.generator_create('ccards', p_format);
end;
/

This format is good, in the way that it is compact and only uses us7ascii characters, so it can easily be parsed by any version of Oracle. It is however, not very user friendly, so here is the same format but now in JSON format:


declare
p_format varchar2(32000) := '{
  "columns": [{
      "column_name": "my_name"
      , "column_datatype": "varchar2(50)"
      , "column_type": "generated"
      , "generator": "person_random.r_name"
    }, {
        "column_name": "my_creditcard"
        , "column_datatype": "varchar2(50)"
        , "column_type": "generated"
        , "generator": "finance_random.r_creditcard"
      }, {
          "column_name": "my_creditcardnum"
          , "column_datatype": "number"
          , "column_type": "generated"
          , "generator": "finance_random.r_creditcardnum"
        }]}';
begin
  testdata_ninja.generator_create('ccards', p_format);
end;
/

In JSON format it is a lot more easy to both create and read the format. However because of the json usage this feature is only supported from Oracle version 12c. The source code uses pl/sql conditional compilation so the same source will compile on most recent Oracle versions. Currently updating the documentation for the new JSON format, so keep an eye out for that on my github site. I am also in the process of creating a video series showing all of the features of this package. For a quick description of the json format, here is a list of the possible fields and demo values:


{
  "columns": [
    {
      "column_name": "mycolname"
      , "column_datatype": "number|date"
      , "column_type": "builtin"
      , "builtin_type": "[numiterate]|datiterate"
      , "builtin_function": "[util_random.ru_number_increment]|util_random.ru_date_increment"
      , "builtin_startfrom": "[1]|to_char(sysdate, 'DDMMYYYY-HH24:MI:SS')"
      , "builtin_increment_min": "[1]"
      , "builtin_increment_max": "[5]"
      , "builtin_increment_component": "[seconds]|minutes|hours|days|weeks|months|years"
    }, {
      "column_name": "mycolname"
      , "column_datatype": "number|varchar2|date|clob"
      , "column_type": "fixed"
      , "fixed_value": "myvaluehere"
    }, {
      "column_name": "mycolname"
      , "column_datatype": "number|varchar2|date|clob"
      , "column_type": "reference field"
      , "reference_table": "table_name"
      , "reference_column": "column_name"
      , "reference_distribution_type": "[simple]|range|weighted"
      , "distribution_simple_val": "[1]"
      , "distribution_range_start": "[1]"
      , "distribution_range_end": "[5]"
      , "distribution_weighted": [
        {
          "distribution_value": "2"
          , "distribution_weight": "0.5"
        }, {
          "distribution_value": "4"
          , "distribution_weight": "0.5"
        }
      ]
    }, {
      "column_name": "mycolname"
      , "column_datatype": "varchar2"
      , "column_type": "referencelist"
      , "reference_static_list": "a,list,of,comma,separated,values"
    }, {
      "column_name": "mycolname"
      , "column_datatype": "number|varchar2|date|clob"
      , "column_type": "generated"
      , "generator": "core_random.r_natural"
      , "nullable": "[10]"
      , "arguments": [
        {
          "arg_name": "maybe_a_name"
          , "arg_value": "a_value"
        }, {
          "arg_name": "another_name"
          , "arg_value": "%%mycolname%%"
        }
      ]
    }
  ]
}

Tagged in : RANDOM_NINJA