22.24 UPDATE_COLUMN Procedure

This procedure updates an existing column in a blueprint table.

Syntax

APEX_DG_DATA_GEN.UPDATE_COLUMN (
    p_blueprint             IN VARCHAR2,
    p_table_name            IN VARCHAR2,
    p_column_name           IN VARCHAR2,
    p_new_column_name       IN VARCHAR2     DEFAULT NULL,
    p_sequence              IN PLS_INTEGER,
    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 )

Parameters

Table 22-24 UPDATE_COLUMN Parameters

Parameter	Description
p_blueprint	Identifier for the blueprint.
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_new_column_name	New name of column (rename).
p_sequence	1 for first column, 2 for second, and so on.
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	When p_data_source_type = DATA_SOURCE then DATA_SOURCE_NAME.COLUMN_NAME (column name'ss case follows p_ds_preserve_case and defaults to upper case). Can be set to one of the following options: 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.

Example

BEGIN
  apex_dg_data_gen.update_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');
END;