CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_DDL_INTERNAL
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/prvthddl.plb
Dependencies
DBA_SYS_PRIVS DBMS_SYS_ERROR DBMS_UTILITY
DBMS_DDL DBMS_SYS_SQL OBJ$
DBMS_SQL DBMS_TRIGGER_LIB TRIGGER$
Security Model No privileges granted by SYS
 
CHECK_TRIGGER_FIRING_PROPERTY

Undocumented
dbms_ddl_internal.check_trigger_firing_property(
trig_owner        IN     VARCHAR2,
trig_name         IN     VARCHAR2,
canon_owner          OUT VARCHAR2,
canon_oname          OUT VARCHAR2,
p_property        IN OUT NUMBER,
unsupported_trig     OUT BOOLEAN);
conn uwclass/uwclass
CREATE OR REPLACE TRIGGER t_trig
BEFORE UPDATE
ON airplanes
BEGIN
  NULL;
END t_trig;
/

conn / as sysdba

set serveroutput on

DECLARE
 co VARCHAR2(30);
 cn VARCHAR2(30);
 pp NUMBER(10) := 0;
 ut BOOLEAN;
BEGIN
  dbms_ddl_internal.check_trigger_firing_property('UWCLASS',
  'T_TRIG', co, cn, pp, ut);

  dbms_output.put_line(co);
  dbms_output.put_line(cn);
  dbms_output.put_line(TO_CHAR(pp));

  IF ut THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
GEN_NEW_OBJECT_ID (new 11g)
Used to generate object identifiers dbms_ddl_internal.gen_new_object_id RETURN BINARY_INTEGER;
SELECT dbms_ddl_internal.gen_new_object_id
FROM dual;
 
HAS_ALTER_ANY_TRIGGER_PRIV

Returns true of the schema owner has the name privilege
dbms_ddl_internal.has_alter_any_trigger_priv(
p_user          IN VARCHAR2,
p_trig_property IN NUMBER)
RETURN BOOLEAN
conn / as sysdba

set serveroutput on
BEGIN
  IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
    dbms_output.put_line('Granted');
  ELSE
    dbms_output.put_line('Not Granted');
  END IF;
END;
/

GRANT alter any trigger TO uwclass;

BEGIN
  IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
    dbms_output.put_line('Granted');
  ELSE
    dbms_output.put_line('Not Granted');
  END IF;
END;
/
 
HAS_EXP_IMP_PRIV

Returns true if the schema owner has export and/or import full database privileges
dbms_ddl_internal.has_exp_imp_priv(
p_uid            IN NUMBER,
p_privs_to_check IN VARCHAR2)
RETURN BOOLEAN;
-- The following does not work ... and no working combination has been
-- discovered. Solutions will be gratefully accepted.


conn / as sysdba

SELECT user_id
FROM dba_users
WHERE username = 'UWCLASS';

set serveroutput on

BEGIN
  IF dbms_ddl_internal.has_exp_imp_priv(82,'EXPORT FULL DATABASE') THEN
    dbms_output.put_line('Granted');
  ELSE
    dbms_output.put_line('Not Granted');
  END IF;
END;
/
 
IS_DDL_TRIGGER

Returns true of the trigger is a DDL Event Trigger
dbms_ddl_internal.is_ddl_trigger(sys_evts IN NUMBER) RETURN BOOLEAN;
conn / as sysdba

desc trigger$

SELECT sys_evts, type#, COUNT(*)
FROM trigger$
GROUP BY sys_evts, type#
ORDER BY 1;

-- from sql.bsq
type# number not null, /* trigger type: */
/* 0 = BEFORE TABLE, 1 = BEFORE ROW, 2 = AFTER TABLE, 3 = AFTER ROW */
/* 4 = INSTEAD OF TRIGGER */
-- also see dba_triggers in catprc.sql


set serveroutput on
BEGIN
  IF dbms_ddl_internal.is_ddl_trigger(31) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
BEGIN
  IF dbms_ddl_internal.is_ddl_trigger(32) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
SWAP_BOOTSTRAP (new 11g)
Undocumented. Used in the script utlmmig.sql dbms_ddl_internal.swap_bootstrap(replacement_tbl_name IN VARCHAR2);
TBD
 
Related Topics
DDL Event Triggers
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [79 users online]    © 2010 psoug.org