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 nameiCOLS
- collection of column names building the foreign keyiREF_TABLE
- referenced table nameiREF_COLS
- collection of column names building the referenced keyiRESTRICTION
- 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.