Oracle DBMS_SQL_TRANSLATOR_EXPORT
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose Support utilities for DBMS_SQL_TRANSLATOR
AUTHID CURRENT_USER
Dependencies
ALL_OBJECTS DBMS_ASSERT DBMS_SYS_SQL
ALL_TAB_PRIVS DBMS_LOB SQLTXL$
DBA_PRIV_AUDIT_OPTS DBMS_SQL USER_OBJECTS
DBA_SYS_PRIVS DBMS_SQL_TRANSLATOR USER_OBJ_AUDIT_OPTS
Documented No
Exceptions
Error Code Reason
ORA-24252 SQL translation profile does not exist
First Available 12.1.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/prvtsqll.plb
Subprograms
 
AUDIT_EXP
Audit SQL Translator Export dbms_sql_translator_export.audit_exp(
objid     IN  NUMBER,
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
DECLARE
 nbo    PLS_INTEGER;
 RetVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.audit_exp(100, '19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/
 
AUDIT_SYSPRIVS_EXP
Audit SQL Translator SYS Privileges Export dbms_sql_translator_export.audit_sysprivs_exp(
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
DECLARE
 nbo    PLS_INTEGER;
 RetVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.audit_sysprivs_exp('19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/
 
CREATE_EXP
Create an export dbms_sql_translator_export.create_exp(
objid     IN  NUMBER,
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
exec dbms_sql_translator.create_profile('UW_SQLTRANS', TRUE);

col txlrowner format a10
col txlrname format a10
col audit$ format a40

SELECT *
FROM sys.sqltxl$;

DECLARE
 nbo    PLS_INTEGER;
 retVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.create_exp(94548, '19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/
 
DROP_EXP
Drop an export dbms_sql_translator_export.drop_exp(
objid     IN  NUMBER,
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
DECLARE
 nbo    PLS_INTEGER;
 RetVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.drop_exp(100, '19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/
 
GRANT_EXP
Grant SQL Translator Export Privileges dbms_sql_translator_export.grant_exp(
objid     IN  NUMBER,
isdba     IN  BINARY_INTEGER,
grantor   OUT VARCHAR2,
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
DECLARE
 nbo    PLS_INTEGER;
 Grntr  VARCHAR2(30);
 RetVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.grant_exp(100, 1, Grntr, '19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/
 
GRANT_SYSPRIVS_EXP
Grant SQL Translator SYS Export Privileges dbms_sql_translator_export.grant_sysprivs_exp(
version   IN  VARCHAR2,
new_block OUT BINARY_INTEGER)
RETURN VARCHAR2;
DECLARE
 nbo    PLS_INTEGER;
 RetVal VARCHAR2(100);
BEGIN
  RetVal := dbms_sql_translator_export.grant_sysprivs_exp('19.2.0', nbo);
  dbms_output.put_line('Return: ' || RetVal);
  dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));
END;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_SQL_TRANSLATOR
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
DBSecWorx