| Oracle Repository API and Model Reference Guide |
The registration process enables users with the appropriate repository privileges to create element types based on Oracle database schema objects. Instances of element types created in this way can be managed by the version-resolved facilities in the repository (with some restrictions). The JR_REGISTRATION package provides methods for performing the registration process using PL/SQL.
For details of the general process to use for registering an Oracle schema in the repository, see the Online Help system, which you can access from the Start menu, Oracle Repository 6i Doc.
Registration must be run by the repository owner.
To register a schema containing schema objects (typically table definitions) from which to create new repository element types, the caller must be logged on to the repository schema. The register method can be run multiple times to register multiple schemas before starting the generation phase. However, any schema object can be registered once only. For example, suppose that a schema is registered and it contains a table 'EMP'. If an attempt to register a table called EMP in another schema, the second table is not registered. (The remedy is to drop the first table and then register and generate the second table called 'EMP').
The generation phase uses the information stored in the repository in Phase 1 to create new element types. For each new element type, a PL/SQL package and appropriate triggers are optionally generated to enable instances of the element type to be created, updated, and managed as part of the version-resolved repository.
For registered element types, there is no CI_<element_name> view, as is the case with configured (that is, supplied) element types. The name of the view resulting from registration is the same as the name of the database schema object that was registered. For example, if a table MYTABLE is registered, the resulting view (which can be queried using SQL statements) is MYTABLE.
The set of properties generated for the the new element type is:
Repository pragmas are directives to the registration process. They are required in some cases to enable the new element types to take on some of the behaviors of configured (that is, supplied) element types in the repository. For example, to get a registered element type generated from a database table definition to behave as a secondary access element type (SAC), certain pragmas must be specified on the table definition and column definitions before the register phase.
The registered element type is created as a SAC or PAC. To create the element type as a secondary access element type, the original table must have a foreign key column, and the pragma OFK suitably defined.
The JR_REGISTRATION package provides the following methods for creating repository element types from Oracle schema objects:
drop_schema
drop_all_schemas
gen_plsql_api
generate
register
migrate_schema
PROCEDURE register (see chart);
Reads objects in an Oracle database schema specified by schema_name (called the template schema) and stores their definitions in repository metadata tables.
The caller must be logged on to the schema of the repository owner (known as the core schema).
Note: Extra information can be passed to the registration process; see Repository pragmas.
The following chart shows the parameters to the register procedure:
Parameter name and order |
Datatype |
Default value |
Notes |
|---|---|---|---|
| schema_name | VARCHAR2 | none | The name of the schema to be registered (for example, 'scott'). |
| gen_api | BOOLEAN | TRUE | Specifies whether to generate a PL/SQL package for the
resultant element types. Notes:
|
| gen_api_callout | BOOLEAN | FALSE | Specifies whether callouts are required from the PL/SQL
package to call user defined code. Set to TRUE if callouts are required. gen_api must be set to TRUE if gen_api_callout is set to TRUE. |
| gen_api_callout_stub | BOOLEAN | FALSE | Specifies whether callout stubs are required for user
defined code. Set to TRUE if callout stubs are required. gen_api must be set to TRUE if gen_api_callout_stub is set to TRUE. |
| log_file_location | VARCHAR2 | 'jr_reg_log' | Specifies the directory on the server in which to write a log of what gets processed. (The log file location must be specified in the init.ora file as value of utl_file_dir parameter). |
| log_file_name | VARCHAR2 | NULL | The name of the log file. |
| autocommit | BOOLEAN | FALSE | Specifies whether to commit data after registering each table. If set to FALSE (the default) no data will be committed, and it is the responsibility of the calling application to commit. If set to TRUE, each Table, View, Sequence, Synonym, Stored Object is to committed after its definition has been read into the RM model. This reduces the amount of rollback space required when registering large schemas. |
PROCEDURE generate (see chart);
Generates a versioned schema instance of the registered schema, with version resolved views, triggers and functions to maintain object version instances.
The following chart shows the parameters to the generate procedure:
Parameter Name |
Datatype |
Default Value |
Notes |
|---|---|---|---|
| schema_name | VARCHAR2 | n/a | Name of schema to be generated (for example, "SCOTT"). |
| log_file_location | VARCHAR2 | NULL | Directory on server to write a log of what gets processed (must be named in init.ora file as value of utl_file_dir parameter). |
| ddl_file_location | VARCHAR2 | NULL | Directory on server to write DDL files of the objects that are created (must be named in init.ora file as value of utl_file_dir parameter). |
| log_file_name | VARCHAR2 | 'jr_gen.log' | Name of log file (log_file_location must also be set). |
| tables_file | VARCHAR2 | 'jrtable.sql' | Name of DDL file for tables (generated only), (ddl_file_location must also be set). |
| indexes_file | VARCHAR2 | 'jrindex.sql' | Name of DDL file for indexes (generated and user defined). |
| constraints_file | VARCHAR2 | 'jrcon.sql' | Name of DDL file for constraints (generated and user defined). |
| triggers_file | VARCHAR2 | 'jrtrig.sql' | Name of DDL file for triggers (generated and user defined). |
| functions_file | VARCHAR2 | 'jrfunc.sql' | Name of DDL file for functions (generated only). |
| views_file | VARCHAR2 | 'jrview.sql' | Name of DDL file for views (generated and user defined). |
| stored_objs_file | VARCHAR2 | 'jrstor.sql' | Name of DDL file for packages, procedures and functions (generated and user defined). |
| synonyms_file | VARCHAR2 | 'jrsyn.sql' | Name of DDL file for synonyms (user defined only). |
| sequences_file | VARCHAR2 | 'jrseq.sql' | Name of DDL file for sequences (user defined only). |
PROCEDURE migrate_schema (see chart);
Migrates instance data from the registered schema into the repository.
Parameter Name |
Datatype | Default Value |
Notes |
|---|---|---|---|
| schema_name | VARCHAR2 | n/a | Name of registered schema whose data is to be migrated into the repository. |
| workarea_name | VARCHAR2 | n/a | Name of workarea in which to create instance data. |
| folder_name | VARCHAR2 | n/a | Name of folder in which to create instance data. |
| log_file_location | VARCHAR2 | null | Directory on server to write a log of what gets processed (must be named in init.ora file as value of utl_file_dir parameter). eg. for whisk8, utl_file_dir = /usr/whisk2/localdbs8 |
| migrate_file_location | VARCHAR2 | null | Directory on server to write DML file of the instance data insert/update statements (must be named in init.ora file as value of utl_file_dir parameter). |
| log_file_name | VARCHAR2 | 'jr_mig.log' | Name of log file (log_file_location must also be set). |
| migrate_file | VARCHAR2 | 'jrmig.sql' | Name of DML file for insert/update statements, (migrate_file_location must also be set). |
PROCEDURE gen_plsql_api (see chart);
Creates a PL/SQL package for manipulating registered types, containing methods as described like the CDAPI. The name of the package is the name of the schema object prefixed by 'JRO'. For example, a table called DEPARTMENT would have a package called JRODEPARTMENT.
This enables Repository tools,for example, the Repository Object Navigator, to manipulate all element types through a standard interface.
Parameter Name |
Datatype |
Default Value |
Notes |
|---|---|---|---|
| schema_name | VARCHAR2 | n/a | Name of the schema to generate an API for. |
| log_file_location | VARCHAR2 | NULL | Specifies the directory on the server to write a log of what gets processed (must be named in init.ora file as value of utl_file_dir parameter). |
| DDL_file_location | VARCHAR2 | NULL | Specifies the directory on the server to write DDL files of the packages that are created (this directory must be named in init.ora file as the value of the utl_file_dir parameter). |
| log_file_name | VARCHAR2 | 'jr_plgen.log' | Name of log file (log_file_location must also be set) |
| plsql_api_file | VARCHAR2 | 'jrplsapi.sql' | Name of DDL file for generated plsql, (DDL_file_location must also be set) |
| regenerate | BOOLEAN | FALSE | If set to TRUE, generates an API even if one has already been generated. |
PROCEDURE drop_schema (see chart);
Drops a schema that has been registered and/or generated. This method can be used to drop a schema after it has been registered (but not yet generated).
When a schema is dropped, the repository reverts to its state before the schema was registered. Any instances of the new element type are deleted as part of dropping the schema from which the element type was generated.
Parameter Name |
Datatype |
Default Value |
Notes |
|---|---|---|---|
| schema_name | VARCHAR2 | n/a | Name of registered/generated schema to drop. |
| log_file_location | VARCHAR2 | NULL | Directory on server to write a log of what gets processed (must be named in init.ora file as value of utl_file_dir parameter). |
| DDL_file_location | VARCHAR2 | NULL | Directory on server to write DDL files of the objects that are dropped (must be named in init.ora file as value of utl_file_dir parameter) |
| log_file_name | VARCHAR2 | 'jr_del.log' | Name of log file (log_file_location must also be set) |
| drop_DDL_file | VARCHAR2 | 'jr_del.sql' | Name of DDL file for drop commands, (DDL_file_location must also be set) |
| delete_meta_data | BOOLEAN | TRUE | Specifies whether to delete repository meta data relating to the schema being deleted
(ie. the result of 'register'). If set to TRUE, the schema must be re-registered before it can be generated. If set to FALSE, the schema can be regenerated without the need to re-register. |
PROCEDURE drop_all_schemas (see chart);
Drops all user-registered schemas that have ever been registered and/or generated.
Parameter Name |
Datatype |
Default Value |
Notes |
|---|---|---|---|
| log_file_location | VARCHAR2 | NULL | Directory on server to write a log of what gets processed (must be named in init.ora file as value of utl_file_dir parameter). |
| DDL_file_location | VARCHAR2 | NULL | Directory on server to write DDL files of the objects that are dropped (must be named in init.ora file as value of utl_file_dir parameter) |
| log_file_name | VARCHAR2 | 'jr_del.log' | Name of log file (log_file_location must also be set) |
| drop_DDL_file | VARCHAR2 | 'jr_del.sql' | Name of DDL file for drop commands, (DDL_file_location must also be set) |
| delete_meta_data | BOOLEAN | TRUE | Specifies whether to delete repository meta data relating to the schema being deleted
(ie. the result of 'register'). If set to TRUE, the schema must be re-registered before it can be generated. If set to FALSE, the schema can be regenerated without the need to re-register. |
Extra information may be required to describe a schema, information which cannot be expressed in a normal Oracle schema. For example: what the name column of a table is called, what (if any) audit columns there are. This extra information can be passed to the registration process in the form of repository pragmas. The syntax for expressing repository pragmas (embedded in a comment) is:
#repos_pragma{keyword=value, keyword=value, etc...}
Case is not significant, and no quotes are required around strings. In the case of
keywords which can take the value 'TRUE', just the existence of the keyword is sufficient;
for example:
#repos_pragma{is_system, v}
Table Keywords, which can be defined using their full names or their abbreviations, reserved by registration are:
Table Keywords |
Keyword Abbreviation |
Value |
Description |
|---|---|---|---|
| IS_SYSTEM | S | TRUE | Identify tables not to be generated. |
| EXTENSION | EXT | ||
| NLS | NLS | TRUE | Identify tables that can be translated to natural language |
| OWNING_OBJECT_VIA_FK | OFK | Name of the foreign key on the table referencing the
owning object - must be defined as cascade delete * or * NULL, if there is no Foreign Key For example, because there is a single column which is used to join to many tables, like rm_text_lines.txt_ref in which case there must be an OFK pragma defined against the column. |
Identify the foreign key which references the owning
object of this one * or * indicate that the Table is a secondary object, that has no owning foreign key |
| OPTIONAL_OWNING_OBJECT_VIA_FK | OPTOFK | Name of Foreign Key on the Table referencing the owning object - must be defined as cascade delete | For internal use only, should not be part of published api. Identify the foreign key which references the owning object of this one. There are a few special cases in the Designer model where an object can be either a primary object or a secondary object, in which case registration needs to generate behavior to support an optional parent_ivid column Not yet supported. |
| VERSION | V | no value required | Identify versionable objects Not yet supported. |
comment on table widget is 'some text #repos_pragma{is_system=TRUE}';
Column Keywords |
Keyword Abbreviation |
Value |
Description |
|---|---|---|---|
| NLS | NLS | FALSE | Identify non translatable columns in a translatable table |
| NAME_COLUMN | NC | TRUE | Identify column that identifies an instance in the table, used by the Repository Name Service, among others (stored in i$sdd_object_versions) |
| LOGICAL_TYPE_COLUMN | LTC | TRUE | Identifies a column in the table which stores the logical type identifier (that is, rm_element types.id), used to populate i$sdd_object_versions.logical_type_id at run time. |
| DATE_CREATED_COLUMN | DCC | TRUE | Identify an audit column |
| DATE_MODIFIED_COLUMN | DMC | TRUE | Identify audit column |
| CREATED_BY_COLUMN | CBC | TRUE | Identify audit column |
| MODIFIED_BY_COLUMN | MBC | TRUE | Identify audit column |
| NOTM_COLUMN | NOTMC | TRUE | Identify number of times modified column |
| OWNING_OBJECT_VIA_FK | OFK | no value required | Identify owning foreign key column |
comment on column WIDGET.NAME is '#repos_pragma{name_column=name}';
comment on column WIDGET.CREATED_BY is '#repos_pragma{created_by_column=TRUE}';
Additional user-defined keywords can be used; the keyword-value pairs are stored in:
Registration generates triggers, functions etc based on the table name and appends suffixes, up to 4 characters in length. There is an Oracle limit of 30 characters for schema objects, hence the 26 length limit for schemas being registered.
Workaround
In the schema being registered, rename any tables longer than 26 to 26 or less and
create a synonym with the original name for the new table name. Registration will preserve
the synonyms in the generated schema and thus any application code should continue to
work.
Workaround
If constraint condition is very long, insert linefeeds in the text so no line is longer
than 256 characters
Any constraint (Primary, Unique, Referential and Check) can be created without giving an explicit name. Oracle will then generate a name of the form 'SYS...'
Registration ignores these constraints
Workaround
Name all constraints
Workaround
Define surrogate primary/unique key columns and foreign keys that reference these
Workaround
As above