Oracle 12c DBMS_TRANSFORM Demos

Oracle DBMS_TRANSFORM Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,

Purpose

Provides an interface to the message format transformation features of Advanced Queuing.

AUTHID

DEFINER

Dependencies

DBMS_TRANSFORM_INTERNAL

DBMS_TRANS_LIB

DBMS_UTILITY

Documented

Yes

First Available

9.0.1

Security Model

Owned by SYS with EXECUTE granted to the AQ_ADMINISTRATOR_ROLE and AQ_USER_ROLE roles

Source

{ORACLE_HOME}/rdbms/admin/dbmstxfm.sql

Subprograms

COMPUTE_TRANSFORMATION	DROP_TRANSFORMATION	Demo
CREATE_TRANSFORMATION	MODIFY_TRANSFORMATION

COMPUTE_TRANSFORMATION

Undocumented

dbms_transform.compute_transformation (
message               IN "<ADT_1>",
transformation_schema IN VARCHAR2,
transformation_name   IN VARCHAR2,
transformed_message   OUT "<ADT_1>");

TBD

CREATE_TRANSFORMATION

Creates a transformation that maps an object of the source type to an object of the destination type

dbms_transform.create_transformation (
schema         IN VARCHAR2(30),
name           IN VARCHAR2(30),
from_schema    IN VARCHAR2(30),
from_type      IN VARCHAR2(30),
to_schema      IN VARCHAR2(30),
to_type        IN VARCHAR2(30),
transformation IN VARCHAR2(4000) DEFAULT NULL);

exec dbms_transform.create_transformation('SCOTT', 'MYMAP', 'SCOTT', 'CART_POINT', 'SCOTT', 'POLAR_POINT', 'function_name(parameter)');

DROP_TRANSFORMATION

Drop a transformation

dbms_transform.drop_transformation(schema IN VARCHAR2(30), name IN VARCHAR2(30));

exec dbms_transform.drop_transformation('SCOTT', 'MYMAP');

MODIFY_TRANSFORMATION

Modify a transformation

dbms_transform.modify_transformation (
schema           IN VARCHAR2(30),
name             IN VARCHAR2(30),
attribute_number IN NUMBER,
transformation   IN VARCHAR2(4000));

exec dbms_transform.modify_transformation('aqadmin', 'MYMAP', 2, 'function_name(parameter)');

Demo

DBMS_TRANSFORM (Demo from $ORACLE_HOME/rdbms/admin/exechae.sql)

BEGIN
sys.dbms_transform.create_transformation(schema => 'SYS', name => 'haen_txfm_obj',
from_schema => 'SYS', from_type => 'ALERT_TYPE', to_schema => 'SYS', to_type => 'VARCHAR2', transformation => 'sys.haen_txfm_text(source.user_data)');
EXCEPTION
WHEN OTHERS THEN
    IF sqlcode = -24184 THEN
      NULL;
    ELSE
      RAISE;
END IF;
END;
/

-- which references
CREATE OR REPLACE FUNCTION haen_txfm_text(message in sys.alert_type) RETURN VARCHAR2 IS
EXTERNAL
NAME "kpkhetp"
WITH CONTEXT
PARAMETERS(context, message, message indicator struct, RETURN OCISTRING)
LIBRARY DBMS_HAEVENTNOT_PRVT_LIB;