24.4 ADD_COLUMN Procedure

This procedure adds a column to the blueprint table.

Syntax

APEX_DG_DATA_GEN.ADD_COLUMN (
    p_blueprint             IN VARCHAR2,
    p_sequence              IN PLS_INTEGER,
    p_table_name            IN VARCHAR2,
    p_column_name           IN VARCHAR2,
    p_preserve_case         IN VARCHAR2 DEFAULT 'N',
    p_display_name          IN VARCHAR2 DEFAULT NULL,
    p_max_length            IN NUMBER   DEFAULT 4000,
    p_multi_value           IN VARCHAR2 DEFAULT 'N',
    p_mv_format             IN VARCHAR2 DEFAULT 'JSON',
    p_mv_unique             IN VARCHAR2 DEFAULT 'Y',
    p_mv_delimiter          IN VARCHAR2 DEFAULT ':',
    p_mv_min_entries        IN INTEGER  DEFAULT 1,
    p_mv_max_entries        IN INTEGER  DEFAULT 2,
    p_mv_partition_by       IN VARCHAR2 DEFAULT NULL,
    p_lang                  IN VARCHAR2 DEFAULT 'en',
    p_data_source_type      IN VARCHAR2,
    p_data_source           IN VARCHAR2 DEFAULT NULL,
    p_ds_preserve_case      IN VARCHAR2 DEFAULT 'N',
    p_min_numeric_value     IN NUMBER   DEFAULT 1,
    p_max_numeric_value     IN NUMBER   DEFAULT 10,
    p_numeric_precision     IN NUMBER   DEFAULT 0,
    p_min_date_value        IN DATE     DEFAULT NULL,
    p_max_date_value        IN DATE     DEFAULT NULL,
    p_format_mask           IN VARCHAR2 DEFAULT c_json_date_format,
    p_sequence_start_with   IN NUMBER   DEFAULT 1,
    p_sequence_increment    IN NUMBER   DEFAULT 1,
    p_formula               IN VARCHAR2 DEFAULT NULL,
    p_formula_lang          IN VARCHAR2 DEFAULT 'PLSQL',
    p_custom_attributes     IN VARCHAR2 DEFAULT NULL,
    p_percent_blank         IN NUMBER   DEFAULT 0,
    p_column_id             OUT NUMBER )

Parameters

Parameter Description
p_blueprint Identifier for the blueprint.
p_sequence 1 for first column, 2 for second, and so on.
p_table_name Table name as known to the blueprint. Checks exact case first, then checks upper case.
p_column_name Name of the column.
p_preserve_case Defaults to N which forces column name to uppercase. If Y, preserves casing of parameter.
p_display_name A friendly name for a given table.
p_max_length When generating data (such as Latin text) substring to this.
p_multi_value

Y or N (currently available for BUILTIN table data and INLINE data).

BUILTIN table data will be distinct.

INLINE data will be distinct if all values appear once (red,1;blue,1;green,1). Otherwise, permits duplicates (red,3;blue,4;green,8). The number indicates the approximated frequency of each value on the generate data.

p_mv_format DELIMITED (based upon p_mv_delimiter) or JSON (such as {"p_column_name" : ["sympton1","sympton2"]} ).
p_mv_unique If Y, values do not repeat within the multi-value column. If N, indicates values may repeat.
p_mv_delimiter Delimiter for a DELIMITED.
p_mv_min_entries Minimum values in a multi value list.
p_mv_max_entries Maximum values in a multi value list.
p_mv_partition_by This value must match a column in the same built-in data source. For example, if p_data_source is "car.model", this value may be "make" because "car.make" is valid.
p_lang Language code (for example en, de, es).
p_data_source_type
  • BLUEPRINT
  • BUILTIN
  • DATA_SOURCE
  • FORMULA (requires p_data_source to be NULL)
  • INLINE
  • SEQUENCE
p_data_source

Can be set to one of the following options:

  • DATA_SOURCE: DATA_SOURCE_NAME.COLUMN_NAME (column name's case follows p_ds_preserve_case and defaults to upper case).
  • BUILTIN: see built-in list, must match a built-in exactly.
  • BLUEPRINT: references table data already generated (table must have lower sequence). For example, Dept.Deptno where add_table with p_table_name = Dept and add_column with Deptno exist.

    Note:

    This is case-sensitive. Tables created with p_preserve_case = N are automatically uppercased. May require DEPT.DEPTNO instead of dept.deptno).
  • INLINE: PART_TIME,3;FULL_TIME,7

    Note:

    Inline format is VALUE,FREQUENCY, separated by a semi-colon. The frequency of the value is an approximation and Oracle best practice is to use the smallest numeric values that provide the desired distribution.
  • SEQUENCE: uses p_sequence_ parameters.
  • FORMULA: p_data_source must be NULL. Uses p_formula as a PL/SQL formula and {column_name} as substitutions from this table. For example, p_formula => {first_name}||'.'||{last_name}||'.insum.ca'
p_ds_preserve_case If p_data_source_type in ('DATA_SOURCE'. 'BLUEPRINT') and p_ds_preserve_case = N, then the data source is upper cased to match an upper case table_name.column_name
p_min_numeric_value A positive integer number used as the minimum value (inclusive) to be used in BUILTIN data sources that return NUMBER values.
p_max_numeric_value A positive integer number used as the maximum value (inclusive) to be used in BUILTIN data sources that return NUMBER values.
p_numeric_precision

0 = no decimal values

-1 = round to ten

positive integer = number of decimal places
p_min_date_value A DATE used as the minimum value (inclusive) to be used in BUILTIN data sources that return DATE type values.
p_max_date_value A DATE used as the maximum value (inclusive) to be used in BUILTIN data sources that return DATE type values.
p_format_mask Format mask when datatype is a date.
p_sequence_start_with Only used when p_data_source_type = SEQUENCE.
p_sequence_increment Only used when p_data_source_type = SEQUENCE.
p_formula

Enables referencing columns in this row, PL/SQL expressions that can reference columns defined in this blueprint row. For example:

{FIRST_NAME}||'.'||{LAST_NAME}||'.insum.ca'

Substitutions are case sensitive and must match {column_name} exactly. If p_preserve_case was set to N, {COLUMN_NAME} must be upper case.

Can be used on any DATA_SOURCE_TYPE.

Formulas are applied last, after p_percent_blank. If p_percent_blank = 100 but FORMULAR is sysdate, the column value will be sysdate.

p_formula_lang Formulas can be used as a combination of PL/SQL functions performed on this or other columns using {column_name} notation. String/Char, Date/Time, Numeric/Math functions are supported.
p_custom_attributes For future expansion.
p_percent_blank 0 to 100. This is applied prior to all formulas. If this column is referenced in a formula, the formula contains a blank when appropriate.

Note:

A formula on this column may cause the column to not be blank.
p_column_id ID of the added column (OUT).

Example

DECLARE
   l_column_id number;
BEGIN
   apex_dg_data_gen.add_column(
                p_blueprint               => 'Cars',
                p_sequence                => 1,
                p_table_name              => 'MY_CARS',
                p_column_name             => 'make',
                p_data_source_type        => 'BUILTIN',
                p_data_source             => 'car.make',
                p_column_id               => l_column_id);
END;

Parent topic: APEX_DG_DATA_GEN