Skip to content

API

General notes

Warning

The current version of Data Generator have certain restrictions. We aim to eliminate some of them in future versions.

  • Only nonquoted identifiers are supported. Also if your database objects or parts of them have names, which must be quoted, some functions could not work, even if you do not use these names explicitely.
  • Only column data types provided in the GTYPE_COLUMN_VALUE type are supported.
  • Synonyms are not supported as table name. You can reference only a table or view directly.

Note

In the examples below we assume that Data Generator was installed in datagen schema and published under dg public synonym (s. Installation).

Identifiers

All identifiers used in subprograms input parameters are case insensitive, as if they were used in SQL. For example, the statements

dg.insert_record(iTABLE => 'employees');
dg.insert_record(iTABLE => 'EMPLOYEES');
dg.insert_record(iTABLE => 'Employees');

are equal and will insert a record into EMPLOYEES table.

However, if you reference columns within a return value of the insert_record function, you must use the upper case. For example:

DECLARE
    v_rec   dg.GTYPE_RECORD;
    v_id    hr.employees.employee_id%TYPE;
BEGIN
    v_rec := dg.insert_record(iTABLE => 'hr.employees');
    v_id := v_rec('EMPLOYEE_ID').number_val; -- will work
    v_id := v_rec('employee_id').number_val; -- ORA-01403: no data found
END;
/

You can use a schema prefix to specify a table in another schema. Otherwise the table in the CURRENT_SCHEMA will be referenced.

-- insert a record into EMPLOYEES table in the HR schema
dg.insert_record(iTABLE => 'hr.employees');

-- insert a record into MY_TABLE table in the current schema
dg.insert_record(iTABLE => 'my_table');

Column names must be specified without a table prefix:

v_values('first_name').varchar2_val := 'Steven';
v_rec := dg.insert_record(
    iTABLE  => 'hr.employees',
    iVALUES => v_values
);

Table descriptions

When acting with a table, Data Generator analyses the table definition, including constraints, gathering information from the data dictionary. The gathered information is saved within internal structures of Data Generator, so-called Table Description Cache.

The dg.describe% procedures allow you to add further constraints to a table. The dg.insert_record subprograms will handle them accordingly. For example, if you describes a column as not nullable using dg.describe_not_null procedure, dg.insert_record will fill the column with a value. Such additional descriptions are also saved in the Table Description Cache.

In contrast to real constrains, adding a constraint with a dg.describe% procedure will not check if existing data satisfy the new constraint. Only a dg.insert_record subprogram will pick the data accoridingly.

The procedures dg.delete_description and dg.delete_all_descriptions remove information about table definitions from the Table Description Cache, including the additional descriptions made by the dg.describe% procedures. Thus when the next time Data Generator acts with the table, it will analyse the table definition again, based on the data dictionary.

The Table Description Cache exists only within a database session. Therefore, if you applied any additional descriptions, they will be lost in a new session.

Logging

Data Generator logs some information in datagen.logger_logs table. The procedures dg.set_log_level and dg.reset_log_level let you control the current logging level.

Data types

GTYPE_IDENTIFIER

SUBTYPE gtype_identifier IS VARCHAR2(261 BYTE)

COLS

TYPE cols IS TABLE OF GTYPE_IDENTIFIER

GTYPE_COLUMN_VALUE

TYPE gtype_column_value IS RECORD (
    char_val        VARCHAR2(2000 BYTE),
    varchar2_val    VARCHAR2(32767 BYTE),
    nvarchar2_val   NVARCHAR2(32767),
    number_val      NUMBER,
    date_val        DATE,
    timestamp_val   TIMESTAMP,
    raw_val         RAW(32767),
    blob_val        BLOB,
    clob_val        CLOB
)

GTYPE_RECORD

TYPE gtype_record IS TABLE OF GTYPE_COLUMN_VALUE INDEX BY GTYPE_IDENTIFIER

Subprograms

delete_all_descriptions Procedure

PROCEDURE delete_all_descriptions;

Empties the Table Description Cache.

delete_description Procedure

PROCEDURE delete_description(
    iTABLE IN GTYPE_IDENTIFIER
);

Deletes the description of the table iTABLE from the Table Description Cache.

describe_fk Procedure

PROCEDURE describe_fk(
    iTABLE          IN GTYPE_IDENTIFIER,
    iCOLS           IN COLS,
    iREF_TABLE      IN GTYPE_IDENTIFIER,
    iREF_COLS       IN COLS,
    iRESTRICTION    IN VARCHAR2 DEFAULT NULL
);

Declares a new foreign key constraint or supplements an existing one with an iRESTRICTION.

Parameters:

  • iTABLE - child table name
  • iCOLS - collection of column names building the foreign key
  • iREF_TABLE - referenced table name
  • iREF_COLS - collection of column names building the referenced key
  • iRESTRICTION - string representing a sql condition applyable to the referenced table

If you specify an iRESTRICTION parameter, only a record in the referenced table satisfying the iRESTRICTION condition will be referenced. For example:

BEGIN
    dg.describe_fk(
        iTABLE          => 'employees',
        iCOLS           => dg.cols('department_id'),
        iREF_TABLE      => 'departments',
        iREF_COLS       => dg.cols('department_id'),
        iRESTRICTION    => q'[department_name like 'IT%']');

    -- inserts a record referencing a department whose name starts with 'IT'
    -- (assuming DEPARTMENT_ID is not nullable)
    dg.insert_record('employees');
END;
/

describe_not_null Procedure

PROCEDURE describe_not_null(
    iTABLE      IN GTYPE_IDENTIFIER,
    iCOL_NAME   IN GTYPE_IDENTIFIER
);

Declares the column iCOL_NAME of the table iTABLE as not nullable.

Example:

BEGIN
    -- inserts a record where FIRST_NAME is null
    dg.insert_record('employees');

    dg.describe_not_null(
        iTABLE      => 'employees',
        iCOL_NAME   => 'first_name');

    -- inserts a record where FIRST_NAME is not null
    dg.insert_record('employees');
END;
/

describe_unique Procedure

PROCEDURE describe_unique(
    iTABLE  IN GTYPE_IDENTIFIER,
    iCOLS   IN COLS
);

Declares a unique constraint consisting of columns of the table iTABLE specified in the iCOLS parameter.

Example:

BEGIN
    dg.describe_unique(
        iTABLE  => 'employees',
        iCOLS   => dg.cols('first_name', 'last_name'));

    -- inserts a record where combination of FIRST_NAME and LAST_NAME does not
    -- exist yet
    dg.insert_record('employees');
END;
/

insert_record Function

FUNCTION insert_record(
    iTABLE  IN GTYPE_IDENTIFIER,
    iVALUES IN GTYPE_RECORD DEFAULT GTYPE_RECORD()
) RETURN GTYPE_RECORD;

Inserts a new record into the table iTABLE.

In the optional iVALUES parameter you can specify desired values for particular columns. The column name is used as key of the associative array and the value is assigned to the field corresponding to the column data type (.<datatype>_val).

Example:

DECLARE
    v_rec       dg.GTYPE_RECORD;
    v_values    dg.GTYPE_RECORD;
BEGIN
    -- EMPLOYEES.FIRST_NAME column is of VARCHAR2 type
    v_values('first_name').varchar2_val := 'Steven';
    v_rec := dg.insert_record(
        iTABLE  => 'employees',
        iVALUES => v_values
    );
END;
/

Important

The record field, which a value is assigned to or read from must exactly correspond the column data type.

For example, if the column SALARY was declared as NUMBER and you assign a value to the varchar2_val field, it won`t work:

v_values('salary').varchar2_val := '32000'

Data Generator would assume NULL was specified for the SALARY column, since it will reference the number_val field due to the column data type.

The same is true for the return value:

DECLARE
    v_rec       dg.GTYPE_RECORD;
    v_values    dg.GTYPE_RECORD;
BEGIN
    v_values('salary').number_val := '32000';
    v_rec := dg.insert_record(
        iTABLE  => 'employees',
        iVALUES => v_values);
    dbms_output.put_line(v_rec('SALARY').number_val);   -- 32000
    dbms_output.put_line(v_rec('SALARY').varchar2_val); -- NULL
END;
/

See also notes to identifiers when specifing table and column names.

If you specify a value for a column, Data Generator garantees, that this value will be written into the table, so far constraints allow. In the worst case, an error will be thrown, but the specified value will never be exchanged or removed.

If you don't specify any value for a column, the column will get a value choosed by Data Generator. The rules, how Data Generator will choose a value are following:

  • if the column is nullable and not a part of a primary, unique or foreign key constraint, it will get NULL value
  • if the column is not nullable and not a part of a primary, unique or foreign key constraint, it will get some default value
  • if the column is a part of a primary, unique or foreign key constraint, Data Generator will do its best to find a value satisfying the constraint(s)

Note

If you specify a NULL value for a column, Data Generator will try to fill the column with NULL. Of course, this will fail for a not nullable column.

If a column is a part of a foreign key constraint, the function will attempt to find an appropriate record in the referenced table. If no such record can be found there, it will insert a new one. The same procedure is applied for the insert into the referenced table, and so forth.

Note

Data Generator will not handle check constraints automatically. To satisfy a check constraint you will need to specify an appropriate value explicitely in the iVALUES parameter.

The function return value represents the whole new inserted record. The keys of the associative array are (case-sensitive!) column names.

DECLARE
    v_rec dg.GTYPE_RECORD;
BEGIN
    v_rec := dg.insert_record(
        iTABLE  => 'employees');
    dbms_output.put_line(v_rec('EMPLOYEE_ID').number_val);  -- new PK value
    dbms_output.put_line(v_rec('FIRST_NAME').varchar2_val); -- NULL, since the column is nullable
    dbms_output.put_line(v_rec('LAST_NAME').varchar2_val);  -- '-'
END;
/

insert_record Procedure

PROCEDURE insert_record(
    iTABLE  IN GTYPE_IDENTIFIER,
    iVALUES IN GTYPE_RECORD DEFAULT GTYPE_RECORD()
);

The procedure does exactly the same as the insert_record function, but has no return value.

set_log_level Procedure

PROCEDURE set_log_level(
    iLEVEL IN VARCHAR2
);

Sets logging level to iLEVEL.

Possible values of the iLEVEL parameter rely on those ones of Logger, but are case-insensitive.

The set logging level takes effect only within the current database session. In a new session the logging level will be reset to a default one.

Information

Currently, 'DEBUG' is the only useful level you can set to get a verbose logging. 'INFORMATION' is the default level.

reset_log_level Procedure

PROCEDURE reset_log_level;

Resets logging level to a default one.

unique_varchar2_prefix Function

FUNCTION unique_varchar2_prefix RETURN VARCHAR2;

Returns the prefix string used to insert unique values of VARCHAR2 type.

If the table has a unique key with a VARCHAR2 column, Data Generator will insert values unique_varchar2_prefix||<counter> where <counter> is a next integer, such that unique_varchar2_prefix||<counter> value does not yet exist in the target column.

version Function

FUNCTION version RETURN VARCHAR2;

Returns a version number of the installed Data Generator.