Creating a Transformation

Figure 9-10 Use Case Diagram: Creating a Transformation

Text description of the illustration adq81434.gif

Purpose

Creates a message format transformation. The transformation must be a SQL function with input type from_type, returning an object of type to_type. It can also be a SQL expression of type to_type, referring to from_type. All references to from_type must be of the form source.user_data.

Usage Notes

To use this feature, you must be granted execute privileges on dbms_transform. You must also have execute privileges on the user-defined types that are the source and destination types of the transformation, and have execute privileges on any PL/SQL function being used in the transformation function. The transformation cannot write the database state (that is, perform DML) or commit or rollback the current transaction.

Syntax

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_AQADM Package): Oracle9i Supplied PL/SQL Packages and Types Reference DBMS_AQADM, DROP_QUEUE_TABLE procedure

• Visual Basic (OO4O): There is no applicable syntax reference for this use case

• Java (JDBC): Oracle9i Supplied Java Packages Reference

Examples

See Chapter 3, "AQ Programmatic Environments" for a list of available functions in each programmatic environment. Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_AQADM): Creating a Transformation

• VB (OO4O): Example not provided.

• Java (JDBC): Dropping a Queue

PL/SQL (DBMS_AQADM): Creating a Transformation

dbms_transform.create_transformation(schema => 'scott',
    name           => 'test_transf', from_schema => 'scott',
    from_type      => 'type1', to_schema => 'scott',
    to_type        => 'type2',
    transformation => 'scott.trans_func(source.user_data)');

Or you can do the following:

dbms_transform.create_transformation(schema => 'scott',
    name           => 'test_transf',
    from_schema    => 'scott', 
    from_type      => 'type1,
    to_schema      => 'scott',
    to_type        => 'type2',
    transformation => 'scott.type2(source.user_data.attr2, 
        source.user_data.attr1)');

Java (JDBC)

No example is provided with this release.