Oracle DBMSOBJG Demos

Oracle DBMSOBJG Version 11.2.0.3

General Information

Purpose

Undocumented object generator

AUTHID

DEFINER

Constants

Name	Data Type	Value
ATTRCOL$	DBA_TRIGGERS	DBMS_SYSTEM
CCOL$	DBA_TRIGGER_COLS	DBMS_SYS_ERROR
CDEF$	DBA_TYPES	DBMS_SYS_SQL
COL$	DBA_VIEWS	DBMS_UTILITY
CON$	DBMSOBJG2	DUAL
DBA_COL_PRIVS	DBMSOBJGWRAPPER	ICOL$
DBA_CONS_COLUMNS	DBMSOBJG_DP	IND$
DBA_OBJECTS	DBMS_ASSERT	INDPART$
DBA_PART_TABLES	DBMS_DEFERGEN	LOB$
DBA_REPGROUP_PRIVILEGES	DBMS_REPCAT_DECL	OBJ$
DBA_REPOBJECT	DBMS_REPCAT_MAS	PLITBLM
DBA_ROLE_PRIVS	DBMS_REPCAT_OBJ_UTL	SEG$
DBA_SEQUENCES	DBMS_REPCAT_RGT_CUST	TAB$
DBA_SOURCE	DBMS_REPCAT_SNA_UTL	TABPART$
DBA_SYNONYMS	DBMS_REPCAT_SQL_UTL	TABSUBPART$
DBA_TAB_COLUMNS	DBMS_REPCAT_UTL	TS$
DBA_TAB_PRIVS	DBMS_SQL	USER$

Dependencies

SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMSOBJG'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMSOBJG';

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/prvtbobg.plb & prvthobg.plb

Subprograms

CHECK_PERMISSIONS	FIND_TS_INFO	GET_NLS_SUBSTR
FIND_COL_INFO	GENERATE_DDL	GET_TAB_SPACE

CHECK_PERMISSIONS

Undocumented

Verifies that the named object exists in the named schema and is the object type identified

dbmsobjg.check_permissions(
sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2);

conn / as sysdba

exec dbmsobjg.check_permissions('UWCLASS','AIRPLANES','TYPE');

exec dbmsobjg.check_permissions('UWCLASS','AIRPLANES','TABLE');

FIND_COL_INFO

Undocumented

Finds some type of information about columns

dbmsobjg.find_col_info(
column_name IN VARCHAR2,
coltab IN dbms_utility.coltab)
RETURN NUMBER;

set serveroutput on

DECLARE
coltb dbms_utility.name_array;
i NUMBER;
BEGIN
coltb(1) := 'UWCLASS.SERVERS';
coltb(2) := 'UWCLASS.SERV_INST';
i := dbmsobjg.find_col_info('SRVR_ID', coltb);

dbms_output.put_line(TO_CHAR(i));
END;
/

FIND_TS_INFO

Undocumented

Finds some type of information about tablespaces

dbmsobjg.find_ts_info(
tablespace_name IN VARCHAR2(30),
tstab IN dbmsobjg.tspace_tab,
tscnt IN NUMBER)
RETURN NUMBER;

set serveroutput on

DECLARE
tstab dbmsobjg.tspace_tab;
tscnt NUMBER(3);
i NUMBER;
BEGIN
dbmsobjg.get_tab_space('UWCLASS','AIRPLANES','TABLE',tstab, tscnt);

i := dbmsobjg.find_ts_info('UWDATA', tstab, tscnt);

dbms_output.put_line(TO_CHAR(i));
END;
/

GENERATE_DDL

Undocumented

Overload 1

dbmsobjg.generate_ddl(
sname          IN VARCHAR2,
oname          IN VARCHAR2,
type           IN VARCHAR2,
flavor_columns IN name_array,
tablespace     OUT VARCHAR2,
ddltab         OUT VARCHAR2S,
ddllen_tab     OUT number_array,
use_tablespace IN VARCHAR2,
create_storage IN VARCHAR2,
disabled_trig OUT VARCHAR2,
force_view     IN VARCHAR2,
debug_table    IN VARCHAR2,
ddlcnt         OUT NUMBER,
tstab          IN tspace_tab,
tscnt          IN NUMBER);

set linesize 121
col argument_name format a20
col position format 99
col data_type format a15
col in_out format a6
col type_owner format a12
col type_name format a20
col type_subname format a20

SELECT argument_name, position, data_type, in_out, type_owner, type_name, type_subname
FROM all_arguments
WHERE object_name = 'GENERATE_DDL'
AND package_name = 'DBMSOBJG'
AND overload = 1
ORDER BY position, data_level;

DECLARE
flavor_cols dbms_utility.name_array;
ddltab dbms_sql.varchar2s;
ddllentab dbms_utility.number_array;
distrig VARCHAR2(20);
ddl_count NUMBER;
tblsp_tab dbmsobjg.tspace_tab;
tblsp_name user_tablespaces.tablespace_name%TYPE;
BEGIN
flavor_cols(1) := 'RID';
flavor_cols(2) := 'FNAME';
flavor_cols(3) := 'LNAME';
tblsp_tab(1) := 'UWDATA';

dbmsobjg.generate_ddl(USER, 'TEST', 'TABLE', flavor_cols, tblsp_name,
ddltab, ddllentab, 'Y','N', distrig, 'N', 'N', ddl_count, tblsp_tab, 1);

dbms_output.put_line(distrig);
dbms_output.put_line(ddl_count);
dbms_output.put_line(ddl_count);
dbms_output.put_line(tblsp_name);
END;
/

Overload 2

dbmsobjg.generate_ddl(
sname             IN     VARCHAR2,
oname            IN     VARCHAR2,
type              IN     VARCHAR2,
flavor_columns    IN     name_array,
tablespace        IN     VARCHAR2,
ddltab            IN OUT VARCHAR2S,
use_tablespace    IN     VARCHAR2,
create_storage    IN     VARCHAR2,
disabled_trig        OUT VARCHAR2,
disabled_trig_ddl IN OUT VARCHAR2S,
force_view        IN     VARCHAR2,
debug_table       IN     VARCHAR2,
ddlcnt            IN OUT NUMBER,
disabledcnt       IN OUT NUMBER,
tstab             IN     tspace_tab,
tscnt             IN     NUMBER);

GET_NLS_SUBSTR

Undocumented

dbmsobjg.get_nls_substr(
s IN VARCHAR2,
offset IN OUT NUMBER,
len IN NUMBER)
RETURN VARCHAR2;

set serveroutput on

DECLARE
ofs NUMBER := 3;
x VARCHAR2(100);
BEGIN
x := dbmsobjg.get_nls_substr('ZZDUALZZ', ofs, 4);

dbms_output.put_line(ofs);
dbms_output.put_line(x);
END;
/

GET_TAB_SPACE

Undocumented

dbmsobjg.get_tab_space(
sname     IN     VARCHAR2,
oname     IN     VARCHAR2,
otype     IN     VARCHAR2
tspacetab    OUT dbmsobjg.tspace_tab,
tspacecnt    OUT NUMBER);

set serveroutput on

DECLARE
tstab dbmsobjg.tspace_tab;
tscnt NUMBER(3);
BEGIN
dbmsobjg.get_tab_space(USER, 'TAB$', 'TABLE', tstab, tscnt);
-- dbms_output.put_line(tstab(tscnt));
dbms_output.put_line(TO_CHAR(tscnt));
END;
/