Oracle | DBMS_EXPORT

Oracle DBMS_EXPORT_EXTENSION Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose PL/SQL package containing functions called by export to dynamically link in PL/SQL logic in the export process.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Function codes for the expact$ table
func_pre_table (execute before loading table)	NUMBER	1
func_post_tables (execute after loading all tables)	NUMBER	2
func_pre_row (execute before loading row)	NUMBER	3
func_post_row (execute after loading row)	NUMBER	4
func_row (execute in lieu of loading row)	NUMBER	5

Dependencies

DBMS_ASSERT	DBMS_METADATA_UTIL	DBMS_UTILITY
DBMS_EXPORT_EXTENSION_I	DBMS_SQL	EXTIDX_IMP_LIB
DBMS_I_INDEX_UTL	DBMS_SYS_SQL

Documented No

Exceptions

Error Code	Reason
unExecutedActions	A function was not called with the same parameters until it returned NULL indicating an internal error in EXPORT.

First Available Not Known

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmspexp.sql

Subprograms

BEGIN_IMPORT_DOMAIN_INDEX	PRE_TABLE	SET_IMP_TIMEZONE
CHECK_MATCH_TEMPLATE	RESET_EXP_OPQ_TYP_EVENT	SET_IOT_EVENT
FUNC_INDEX_DEFAULT	RESET_NLS_NUMERIC_CHAR	SET_NLS_NUMERIC_CHAR
GET_DOMAIN_INDEX_METADATA	RESET_SECONDARYOBJ_EVENT	SET_NO_OUTLINES
GET_DOMAIN_INDEX_TABLES	SET_EXP_OPQ_TYP_EVENT	SET_RESUM
GET_OBJECT_COMMENT	SET_EXP_SORTSIZE	SET_RESUMNAM
GET_OBJECT_SOURCE	SET_EXP_TIMEZONE	SET_RESUMNAMTIM
GET_V2_DOMAIN_INDEX_TABLES	SET_HAKAN_EVENT	SET_RESUMTIM
INSERT_SECOBJ	SET_IMP_EVENTS	SET_SECONDARYOBJ_EVENT
NULLTOCHR0	SET_IMP_SKIP_INDEXES_OFF	SET_STATSON
POST_TABLES	SET_IMP_SKIP_INDEXES_ON

BEGIN_IMPORT_DOMAIN_INDEX

Truncates the table odci_secobj$, and set up index schema and name dbms_export_extension.begin_import_domain_index( idxschema IN VARCHAR2, idxname IN VARCHAR2);

exec dbms_export_extension.begin_import_domain_index(USER, 'UW_TEST_DOMIDX');

CHECK_MATCH_TEMPLATE

Checks to see if a partition has made use of a template partition clause dbms_export_extension.check_match_template(pobjno IN INTEGER) RETURN INTEGER;

TBD

FUNC_INDEX_DEFAULT

Returns default$ from col$ for a function-base index and converts it to VARCHAR2 from LONG dbms_export_extension.func_index_default( tabobj IN NUMBER, colname IN VARCHAR2) RETURN CLOB;

SELECT object_name, object_id, col#, name

      FROM dba_objects o, col$ c

      WHERE  o.object_id = c.obj#

      AND o.object_type = 'TABLE'

      AND c.default$ IS NOT NULL

      AND rownum < 11;

      

      OBJECT_NAME            OBJECT_ID       COL# NAME

      --------------------- ---------- ---------- ----------------

      USER$                         22         16 ASTATUS

      USER$                         22         17 LCOUNT

      HISTGRM$                      66          8 EP_REPEAT_COUNT

      NTAB$                        117          5 NAME

      CDB_LOCAL_ADMINAUTH$         150          3 PRIVILEGES

      CDB_LOCAL_ADMINAUTH$         150          5 FLAGS

      CDB_LOCAL_ADMINAUTH$         150          8 LCOUNT

      CDB_LOCAL_ADMINAUTH$         150          9 ASTATUS

      CDB_LOCAL_ADMINAUTH$         150         15 FED_PRIVILEGES

      SQLERROR$                    161          5 SPARE1

      

      SELECT dbms_export_extension.func_index_default(117, 'NAME')

      FROM dual;

      

      DBMS_EXPORT_EXTENSION.FUNC_INDEX_DEFAULT(117,'NAME')

      -----------------------------------------------------

      'NT$'

GET_DOMAIN_INDEX_METADATA

Acts as intermediary between export and the ODCIIndexGetMetadata method on a domain index's implementation type which allows the index to return PL/SQL-based "metadata" such as policy info. Strings are returned representing pieces of PL/SQL blocks to execute at import time. dbms_export_extension.get_domain_index_metadata( index_name IN VARCHAR2, index_schema IN VARCHAR2, type_name IN VARCHAR2, type_schema IN VARCHAR2, version IN VARCHAR2, newblock OUT PLS_INTEGER, gmflags IN NUMBER DEFAULT -1, -- Post-v1 DI only datapump_debug IN NUMBER DEFAULT -1, partition_name IN VARCHAR2 DEFAULT NULL ) RETURN VARCHAR2;

TBD

GET_DOMAIN_INDEX_TABLES

Acts as intermediary between export and the ODCIIndexUtilGetTableNames method on a domain index's implementation type allowing theindex to return list of secondary table names (separated by comma) that are to be exported and imported to speed up rebuild of domain indexes during import. dbms_export_extension.get_domain_index_tables( index_name IN VARCHAR2, index_schema IN VARCHAR2, type_name IN VARCHAR2, type_schema IN VARCHAR2, read_only IN PLS_INTEGER, version IN VARCHAR2, get_tables IN PLS_INTEGER) RETURN VARCHAR2;

TBD

GET_OBJECT_COMMENT

Reurns the source string for COMMENT OPERATOR and COMMENT INDEXTYPE dbms_export_extension.get_object_comment( objid IN NUMBER, objtype IN NUMBER) RETURN VARCHAR2;

TBD

GET_OBJECT_SOURCE

Used to get the source string for CREATE OPERATOR and CREATE INDEXTYPE. The function is passed the object number and the object type and the string returned is the SQL statement needed to create the operator or the index type.

As you will note in the demo, at right, if you don't pass it the right object_id it returns NULL. dbms_export_extension.get_object_source( objid IN NUMBER, objtype IN NUMBER) RETURN VARCHAR2;

SELECT object_id

      FROM dba_objects

      WHERE object_name = 'TAB$';

      

       OBJECT_ID

      ----------

               4

      

      SELECT type# FROM obj$ WHERE obj# = 4;

      

           TYPE#

      ----------

               2

      

      SELECT dbms_export_extension.get_object_source(4, 2)

      FROM dual;

      SELECT dbms_export_extension.get_object_source(4, 2)

      *

      ERROR at line 1:

      ORA-16540: invalid argument

      ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 297

GET_V2_DOMAIN_INDEX_TABLES

Acts as intermediary between export and the ODCIIndexUtilGetTableNames method on a domain index's implementation type. Unlike the initial (v1) implementation, the _v2_ 0/1 value which export will use to determine if all the secondary objects associated with a domain index should be exported (1) or not (0) dbms_export_extension.get_v2_domain_index_tables( index_name IN VARCHAR2, index_schema IN VARCHAR2, type_name IN VARCHAR2, type_schema IN VARCHAR2, read_only IN PLS_INTEGER, version IN VARCHAR2, get_tables IN PLS_INTEGER, gmflags IN NUMBER) RETURN INTEGER;

TBD

INSERT_SECOBJ

Inserts an entry into the table odci_secobj$, dbms_export_extension.insert_secobj( secobjschema IN VARCHAR2, secobjname IN VARCHAR2);

col idxschema format a10

      col idxname format a10

      col secobjschema format a13

      col secobjname format a15

      

      SELECT *

      FROM odci_secobj$;

      

      no rows selected

      

      exec dbms_export_extension.insert_secobj(USER, 'UW_TEST_SECOBJ');

      

      PL/SQL procedure successfully completed.

      

      SELECT *

      FROM odci_secobj$;

      

      IDXSCHEMA  IDXNAME    SECOBJSCHEMA  SECOBJNAME

      ---------- ---------- ------------- ---------------

                            SYS           UW_TEST_SECOBJ

      

      DELETE FROM odci_secobj$;

      

      1 row deleted.

      

      COMMIT;

      

      Commit complete.

NULLTOCHR0

Replaces \0 with CHR(0) in a VARCHAR2 dbms_export_extension.nulltochr0(value IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_export_extension.nulltochr0('MOR\0GAN')

      FROM dual;

      

      DBMS_EXPORT_EXTENSION.NULLTOCHR0('MOR\0GAN')

      ---------------------------------------------

      MOR\0GAN

      

      DECLARE

       inVal VARCHAR2(20) := '\0';

       retVal VARCHAR2(20);

      BEGIN

        retVal := dbms_export_extension.nulltochr0(inVal);

        dbms_output.put_line(retVal);

        dbms_output.put_line(ASCII(retVal));

      END;

      /

      \0

      92

      

      PL/SQL procedure successfully completed.

POST_TABLES

Execute post-tables functions from the expact$ table, for the specified object dbms_export_extension.post_tables( obj_schema IN VARCHAR2, obj_name IN VARCHAR2) RETURN VARCHAR2;

CREATE TABLE t AS SELECT * FROM user_users;

      

      SELECT dbms_export_extension.post_tables(USER, 'T')

      FROM dual;

      

      DBMS_EXPORT_EXTENSION.POST_TABLES(USER,'T')

      --------------------------------------------

PRE_TABLE

Execute pre-table functions from the expact$ table, for the specified object dbms_export_extension.pre_table( obj_schema IN VARCHAR2, obj_name IN VARCHAR2) RETURN VARCHAR2;

CREATE TABLE t AS SELECT * FROM user_views;

      

      SELECT dbms_export_extension.pre_table(USER, 'T')

      FROM dual;

      

      DBMS_EXPORT_EXTENSION.PRE_TABLE(USER,'T')

      ------------------------------------------

RESET_EXP_OPQ_TYP_EVENT

Undocumented dbms_export_extension.reset_exp_opq_typ_event;

exec dbms_export_extension.reset_exp_opq_typ_event;

      

      PL/SQL procedure successfully completed.

RESET_NLS_NUMERIC_CHAR

Undocumented dbms_export_extension.reset_nls_numeric_char;

exec dbms_export_extension.reset_nls_numeric_char;

      

      PL/SQL procedure successfully completed.

RESET_SECONDARYOBJ_EVENT

Undocumented dbms_export_extension.reset_secondaryobj_event;

exec dbms_export_extension.reset_secondaryobj_event;

      

      PL/SQL procedure successfully completed.

SET_EXP_OPQ_TYP_EVENT

Undocumented dbms_export_extension.set_exp_opq_typ_event;

exec dbms_export_extension.set_exp_opq_typ_event;

      

      PL/SQL procedure successfully completed.

SET_EXP_SORTSIZE

Undocumented dbms_export_extension.set_exp_sortsize;

exec dbms_export_extension.set_exp_sortsize;

      

      PL/SQL procedure successfully completed.

SET_EXP_TIMEZONE

Undocumented dbms_export_extension.set_exp_timezone;

exec dbms_export_extension.set_exp_timezone;

      

      PL/SQL procedure successfully completed.

SET_HAKAN_EVENT

Undocumented dbms_export_extension.set_hakan_event;

exec dbms_export_extension.set_hakan_event;

      

      PL/SQL procedure successfully completed.

SET_IMP_EVENTS

Undocumented dbms_export_extension.set_imp_events;

exec dbms_export_extension.set_imp_events;

      

      PL/SQL procedure successfully completed.

SET_IMP_SKIP_INDEXES_OFF

Undocumented dbms_export_extension.set_imp_skip_indexes_off;

exec dbms_export_extension.set_imp_skip_indexes_off;

      

      PL/SQL procedure successfully completed.

SET_IMP_SKIP_INDEXES_ON

Undocumented dbms_export_extension.set_imp_skip_indexes_on;

exec dbms_export_extension.set_imp_skip_indexes_on;

      

      PL/SQL procedure successfully completed.

SET_IMP_TIMEZONE

Undocumented dbms_export_extension.set_imp_timezone(timezone IN VARCHAR2);

SELECT DISTINCT tzname

      FROM v$timezone_names

      WHERE SUBSTR(tzname,1,1) NOT IN ('A', 'B', 'C', 'E', 'G', 'I', 'P', 'U')

      ORDER BY 1;

      

      TZNAME

      -----------------

      HST

      Hongkong

      Jamaica

      Japan

      Kwajalein

      Libya

      MET

      MST

      MST7MDT

      Mexico/BajaNorte

      Mexico/BajaSur

      Mexico/General

      NZ

      NZ-CHAT

      Navajo

      ROC

      ROK

      Singapore

      Turkey

      W-SU

      WET

      Zulu

      

      exec dbms_export_extension.set_imp_timezone('NAVAJO');

      

      PL/SQL procedure successfully completed.

SET_IOT_EVENT

Undocumented dbms_export_extension.set_iot_event;

exec dbms_export_extension.set_iot_event;

      

      PL/SQL procedure successfully completed.

SET_NLS_NUMERIC_CHAR

Undocumented dbms_export_extension.set_nls_numeric_char;

exec dbms_export_extension.set_nls_numeric_char;

      

      PL/SQL procedure successfully completed.

SET_NO_OUTLINES

Undocumented dbms_export_extension.set_no_outlines;

exec dbms_export_extension.set_no_outlines;

      

      PL/SQL procedure successfully completed.

SET_RESUM

Undocumented dbms_export_extension.set_resum;

exec dbms_export_extension.set_resum;

      

      PL/SQL procedure successfully completed.

SET_RESUMNAM

Undocumented dbms_export_extension.set_resumnam(name IN VARCHAR2);

TBD

SET_RESUMNAMTIM

Undocumented dbms_export_extension.set_resumnamtim( name IN VARCHAR2, time IN INTEGER);

TBD

SET_RESUMTIM

Undocumented dbms_export_extension.set_resumTim(time IN INTEGER);

exec dbms_export_extension.set_resumTim(1);

      

      PL/SQL procedure successfully completed.

SET_SECONDARYOBJ_EVENT

Undocumented dbms_export_extension.set_secondaryObj_event;

exec dbms_export_extension.set_secondaryObj_event;

      

      PL/SQL procedure successfully completed.

SET_STATSON

Undocumented dbms_export_extension.set_statson;

exec dbms_export_extension.set_statson;

      

      PL/SQL procedure successfully completed.