Table of Contents
This chapter describes the basic concepts of DBMS_DDL package and how to use it.
DBMS_DDL provides WRAPPING and other DDL related functions for DDLs that start with CREATE OR REPLACE PROCEDURE, or PACKAGE.
This section describes the procedures and functions provided by DBMS_DDL package in alphabetical order.
Wraps a DDL that starts with CREATE OR REPLACE PROCEDURE, FUNCTION, PACKAGE or PACKAGE BODY, and then executes it.
This procedure performs the same action as the following.
EXECUTE IMMEDIATE
DBMS_DDL.WRAP(ddl);
Details about the CREATE_WRAPPED procedure are as follows:
Prototype
DBMS_DDL.CREATE_WRAPPED
( ddl IN VARCHAR2 )
Parameter
| Parameter | Description |
|---|---|
| ddl | DDL that starts with CREATE OR REPLACE PROCEDURE, FUNCTION, PACKAGE or PACKAGE BODY. Unlike tbwrap tool, only one DDL is allowed. |
Example
create or replace procedure create_wrapped_example
is
hello_ddl varchar(1000) := 'create or replace procedure hello
is
str varchar(10) := ''hello'';
begin
DBMS_OUTPUT.PUT_LINE(str);
end;';
begin
DBMS_DDL.CREATE_WRAPPED(hello_ddl);
end;
/
DBMS_DDL.CREATE_WRAPPED is executed based on the caller's privilege without referencing the caller's role. Hence, the previous hello_ddl example can only be executed by a caller with the CREATE ANY PROCEDURE privilege.
Returns whether FireOnce, an attribute of TRIGGER, is set.
Details about the IS_TRIGGER_FIRE_ONCE function are as follows:
Prototype
DBMS_DDL.IS_TRIGGER_FIRE_ONCE
( trig_owner IN VARCHAR2, trig_name IN VARCHAR2 ) RETURN
BOOLEAN;
Parameter
| Parameter | Description |
|---|---|
| trig_owner | TRIGGER owner. |
| trig_name | TRIGGER name. |
Sets the value of FireOnce, an attribute of TRIGGER.
Details about the SET_TRIGGER_FIRING_PROPERTY procedure are as follows:
Prototype
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY
( trig_owner IN VARCHAR2, trig_name IN VARCHAR2, fire_once IN
BOOLEAN )
Parameter
| Parameter | Description |
|---|---|
| trig_owner | TRIGGER owner. |
| trig_name | TRIGGER name. |
| fire_once | New FireOnce value. |
Returns the result of wrapping a DDL that starts with CREATE OR REPLACE PROCEDURE, FUNCTION, PACKAGE or PACKAGE BODY.
A ddl can be wrapped by executing the following.
EXECUTE IMMEDIATE
DBMS_DDL.WRAP(ddl);
Details about the WRAP function are as follows:
Prototype
DBMS_DDL.WRAP (
ddl IN VARCHAR2 ) RETURN VARCHAR2;
Parameter
| Parameter | Description |
|---|---|
| ddl | DDL that starts with CREATE OR REPLACE PROCEDURE, FUNCTION, PACKAGE or PACKAGE BODY. Unlike tbwrap tool, only one DDL is allowed. |
Example
create or replace procedure wrap_example
is
hello_ddl varchar(1000) := 'create or replace procedure hello
is
str varchar(10) := ''hello'';
begin
DBMS_OUTPUT.PUT_LINE(str);
end;';
begin
EXECUTE IMMEDIATE DBMS_DDL.WRAP(hello_ddl);
end;
/