1 package dbms_ddl AUTHID CURRENT_USER is
2
3 -- used by set_trigger_firing_property
4 FIRE_ONCE CONSTANT NUMBER := 1;
5 APPLY_SERVER_ONLY CONSTANT NUMBER := 2;
6
7 ----------------------------
8 -- PROCEDURES AND FUNCTIONS
9 --
10 procedure alter_compile(type varchar2, schema varchar2, name varchar2,
11 reuse_settings boolean := FALSE);
12 -- Equivalent to SQL "ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.]
13 -- <name> COMPILE [BODY] ?REUSE SETTINGS?". If the named object is this
14 -- package, or any packages upon which it depends (currently "standard" or
15 -- "dbms_standard") then the procedure simply returns (since these
16 -- packages are clearly successfully compiled).
17 -- Input arguments:
18 -- type
19 -- Must be one of "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE
20 -- BODY" or "TRIGGER".
21 -- schema
22 -- The schema name. If NULL then use current schema.
23 -- This is uppercased unless enclosed in double quotes (").
24 -- name
25 -- The name of the object.
26 -- This is uppercased unless enclosed in double quotes (").
27 -- reuse_settings
28 -- This is a boolean flag that indicates whether the session settings in
29 -- the objects should be reused, or whether the current session settings
30 -- should be picked up instead.
31 -- Exceptions:
32 -- ORA-20000: Insufficient privileges or object does not exist.
33 -- ORA-20001: Remote object, cannot compile.
34 -- ORA-20002: Bad value for object type. Should be one of PACKAGE,
35 -- PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER.
36 procedure analyze_object
37 (type varchar2, schema varchar2, name varchar2, method varchar2,
38 estimate_rows number default null,
39 estimate_percent number default null, method_opt varchar2 default null,
40 partname varchar2 default null);
41 -- Equivalent to SQL "ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name>
42 -- [<method>] STATISTICS [SAMPLE <n> [ROWS|PERCENT]]"
43 -- Input arguments:
44 -- type
45 -- One of 'TABLE', 'CLUSTER' or 'INDEX'. If none of these, the
46 -- procedure just returns.
47 -- schema
48 -- schema of object to analyze. NULL means current schema.
49 -- This is uppercased unless enclosed in double quotes (").
50 -- name
51 -- name of object to analyze.
52 -- This is uppercased unless enclosed in double quotes (").
53 -- method
54 -- One of 'ESTIMATE', 'COMPUTE' or 'DELETE'. If 'ESTIMATE' then either
55 -- estimate_rows or estimate_percent must be non-zero.
56 -- estimate_rows
57 -- Number of rows to estimate
58 -- estimate_percent
59 -- Percentage of rows to estimate. If estimate_rows is specified
60 -- than ignore this parameter.
61 -- method_opt
62 -- method options of the following format
63 -- [ FOR TABLE ]
64 -- [ FOR ALL [INDEXED] COLUMNS] [SIZE n]
65 -- [ FOR ALL INDEXES ]
66 -- partname
67 -- specific partition to be analyzed.
68 -- Exceptions:
69 -- ORA-20000: Insufficient privileges or object does not exist.
70 -- ORA-20001: Bad value for object type. Should be one of TABLE, INDEX
71 -- or CLUSTER.
72 -- ORA-20002: METHOD must be one of COMPUTE,ESTIMATE or DELETE
73 procedure alter_table_referenceable
74 (table_name varchar2, table_schema varchar2 default null,
75 affected_schema varchar2 default null);
76 -- Alter the given object table table_schema.table_name so it becomes the
77 -- referenceable table for the given schema affected_schema.
78 -- This is equivalent to SQL "ALTER TABLE [<table_schema>.]<table_name>
79 -- REFERENCEABLE FOR <affected_schema>" which is currently not supported or
80 -- available as a DDL statement.
81 --
82 -- When you create an object table, it automatically becomes referenceable,
83 -- unless you use the OID AS clause when creating the table.
84 -- The OID AS clause allows you to create an object table and to assign
85 -- to the new table the same EOID as another object table of the same type.
86 -- After you create a new table using the OID AS clause, you end up with
87 -- two object table with the same EOID; the new table is not referenceable,
88 -- the original one is. All references that used to point to the objects
89 -- in the original table still reference the same objects in the same
90 -- original table.
91 --
92 -- If you execute this procedure on the new table, it will make the new
93 -- table the referenceable table replacing the original one; thus, those
94 -- references now point to the objects in the new table instead of the
95 -- original table.
96 --
97 -- For example, the following steps recreate an object table that needs
98 -- to be reorganized for some reasons:
99 -- CREATE TABLE EMP_NEW OF EMPLOYEE OID AS EMP;
100 -- INSERT INTO EMP_NEW (SYS_NC_OID$, EMP_NEW)
101 -- SELECT SYS_NC_OID$, EMP FROM EMP;
102 -- EXECUTE DBMS_DDL.ALTER_TABLE_REFERENCEABLE('EMP_NEW');
103 -- -- table_schema defaults to NULL, thus use the current schema, and
104 -- -- affected_schema defaults to NULL, thus use PUBLIC, which means
105 -- -- all schemas will be affected
106 -- RENAME EMP TO EMP_OLD;
107 -- RENAME EMP_NEW TO EMP;
108 --
109 -- The affected schema can be PUBLIC or a particular schema. If it is
110 -- PUBLIC, all schemas are affected. If it is a particular schema, only
111 -- that schema is affected.
112 --
113 -- The user that executes this procedure must own the new table (i.e.,
114 -- the schema is the same as the user), and the affected schema must be the
115 -- same as the user or PUBLIC. If the affected schema is PUBLIC, then the
116 -- user must own the old mapping table for PUBLIC as well.
117 --
118 -- If the user executing this procedure has ALTER ANY TABLE and SELECT ANY
119 -- TABLE and DROP ANY TABLE privileges, the user doesn't have to own the
120 -- tables, and the affected schema can be any valid schema or PUBLIC.
121 --
122 -- Input arguments:
123 -- table_name
124 -- The name of the table to be altered. Cannot be a synonym.
125 -- Must not be NULL. Case sensitive.
126 -- table_schema
127 -- The name of the schema owning the table to be altered.
128 -- If NULL then the current schema is used. Case sensitive.
129 -- affected_schema
130 -- The name of the schema affected by this alteration.
131 -- If NULL then PUBLIC is used. Case sensitive.
132 -- Exceptions:
133 -- ORA-20000: insufficient privileges, invalid schema name
134 -- or table does not exist,
135 procedure alter_table_not_referenceable
136 (table_name varchar2, table_schema varchar2 default null,
137 affected_schema varchar2 default null);
138 -- Alter the given object table table_schema.table_name so it becomes not
139 -- the default referenceable table for the schema affected_schema.
140 -- This is equivalent to SQL "ALTER TABLE [<table_schema>.]<table_name>
141 -- NOT REFERENCEABLE FOR <affected_schema>"
142 -- which is currently not supported or available as a DDL statement.
143 -- This procedure simply reverts for the affected schema to the default
144 -- table referenceable for PUBLIC; i.e., it simply undoes the previous
145 -- alter_table_referenceable call for this specific schema.
146 --
147 -- The affected schema must a particular schema (cannot be PUBLIC).
148 --
149 -- The user that executes this procedure must own the table (i.e.,
150 -- the schema is the same as the user), and the affected schema must be
151 -- the same as the user.
152 --
153 -- If the user executing this procedure has ALTER ANY TABLE and SELECT ANY
154 -- TABLE and DROP ANY TABLE privileges, the user doesn't have to own the
155 -- table and the affected schema can be any valid schema.
156 --
157 -- Input arguments:
158 -- table_name
159 -- The name of the table to be altered. Cannot be a synonym.
160 -- Must not be NULL. Case sensitive.
161 -- table_schema
162 -- The name of the schema owning the table to be altered.
163 -- If NULL then the current schema is used. Case sensitive.
164 -- affected_schema
165 -- The name of the schema affected by this alteration.
166 -- If NULL then the current schema is used. Case sensitive.
167 -- Exceptions:
168 -- ORA-20000: insufficient privileges, invalid schema name or
169 -- table does not exist,
170
171 PROCEDURE set_trigger_firing_property(trig_owner IN VARCHAR2,
172 trig_name IN VARCHAR2,
173 fire_once IN BOOLEAN);
174
175 PRAGMA SUPPLEMENTAL_LOG_DATA(set_trigger_firing_property, AUTO_WITH_COMMIT);
176
177 PROCEDURE set_trigger_firing_property(trig_owner IN VARCHAR2,
178 trig_name IN VARCHAR2,
179 property IN BINARY_INTEGER,
180 setting IN BOOLEAN);
181
182 PRAGMA SUPPLEMENTAL_LOG_DATA(set_trigger_firing_property, AUTO_WITH_COMMIT);
183
184 --- ------------------------------------------------------------------------
185 --- Any changes to the data dictionary will be committed without
186 --- interfering user's transaction flow.
187 ---
188 --- Overloaded since original implementation hardcoded only one property
189 --- with generic named procedure and is already in use.
190 --- The old prototype should be deprecated
191 ---
192 --- Input arguments:
193 --- trig_owner
194 --- owner of the trigger.
195 --- This is uppercased unless enclosed in double quotes (").
196 --- trig_name
197 --- name of the trigger.
198 --- This is uppercased unless enclosed in double quotes (").
199 --- OLD:
200 --- fire_once
201 --- If TRUE, set a bit to indicate that this trigger will only be
202 --- fired once (in one place) and won't be fired due to
203 --- data synchronization done by Oracle.
204 --- Otherwise, set the bit to FALSE and the trigger will always
205 --- be fired, subject to its enable/disable property, regardless
206 --- of whether it's in data synchronization done by Oracle.
207 --- NEW:
208 --- property
209 --- Package define of valid trigger properties that can be set
210 --- see top of package definition for valid values
211 --- setting
212 --- If TRUE set the requested property to true otherwise clear bit
213 --- Exceptions:
214 --- ORA-04072: invalid type.
215 --- ORA-23308: object %s.%s does not exist or is invalid.
216 --- ORA-01031: insufficient privilege.
217
218 FUNCTION is_trigger_fire_once(trig_owner IN VARCHAR2,
219 trig_name IN VARCHAR2) RETURN BOOLEAN;
220
221 --- ------------------------------------------------------------------------
222 --- return TRUE iff the given trigger should be fired once (one place) only.
223 ---
224 --- Input arguments:
225 --- trig_owner
226 --- owner of the trigger.
227 --- This is uppercased unless enclosed in double quotes (").
228 --- trig_name
229 --- name of the trigger.
230 --- This is uppercased unless enclosed in double quotes (").
231 --- Exceptions:
232 --- ORA-04072: invalid type.
233 --- ORA-23308: object %s.%s does not exist or is invalid.
234
235 FUNCTION is_trigger_fire_once_internal(trig_owner IN VARCHAR2,
236 trig_name IN VARCHAR2)
237 RETURN BINARY_INTEGER;
238 --- ------------------------------------------------------------------------
239 --- For internal use only.
240
241
242 /*
243 * NAME:
244 * wrap
245 *
246 * PARAMETERS:
247 * ddl (IN) - CREATE OR REPLACE statement specifying a
248 * package specification, package body, type specification,
249 * type body, procedure or function.
250 *
251 * DESCRIPTION:
252 * This API takes the input DDL statement specifying creation of a
253 * PL/SQL unit and returns a DDL statement with the PL/SQL source
254 * obfuscated. Obfuscation is done in the same manner as the standalone
255 * "wrap" tool. If the input does not specify a PL/SQL unit that can
256 * be wrapped or if the input has simple syntax errors, exception
257 * MALFORMED_WRAP_INPUT will be raised.
258 *
259 * NOTES:
260 * This API has three overload candidates. The first candidate accepts
261 * VARCHAR2 input. The second and third candidates accept collection-
262 * of-VARCHAR2 input allowing larger DDL statements.
263 *
264 * If you call dbms_sql.parse() on the result of dbms_ddl.wrap(), please
265 * note that you must set the LFFLG parameter to FALSE. If you set LFFLG
266 * to TRUE, the additional newlines in the middle of the obfuscated unit
267 * will confuse the PL/SQL compiler and cause the unit to be created with
268 * errors.
269 */
270 FUNCTION wrap(ddl VARCHAR2) RETURN VARCHAR2;
271 FUNCTION wrap(ddl dbms_sql.varchar2s, lb PLS_INTEGER, ub PLS_INTEGER)
272 RETURN dbms_sql.varchar2s;
273 FUNCTION wrap(ddl dbms_sql.varchar2a, lb PLS_INTEGER, ub PLS_INTEGER)
274 RETURN dbms_sql.varchar2a;
275
276 /*
277 * NAME:
278 * create_wrapped
279 *
280 * PARAMETERS:
281 * ddl (IN) - CREATE OR REPLACE statement specifying a
282 * package specification, package body, type specification,
283 * type body, procedure or function.
284 *
285 * DESCRIPTION:
286 * DBMS_DDL.CREATE_WRAPPED(ddl) is equivalent to
287 * - DBMS_SQL.PARSE(<cursor>, DBMS_DDL.WRAP(ddl))
288 * In other words, it obfuscates the text of the input CREATE OR
289 * REPLACE statement and executes it. This API will provide better
290 * performance than executing the individual operations.
291 *
292 * NOTES:
293 * This API has three overload candidates. The first candidate accepts
294 * VARCHAR2 input. The second and third candidates accept collection-
295 * of-VARCHAR2 input allowing larger DDL statements.
296 */
297 PROCEDURE create_wrapped(ddl VARCHAR2);
298 PROCEDURE create_wrapped(ddl dbms_sql.varchar2s, lb PLS_INTEGER,
299 ub PLS_INTEGER);
300 PROCEDURE create_wrapped(ddl dbms_sql.varchar2a, lb PLS_INTEGER,
301 ub PLS_INTEGER);
302
303 malformed_wrap_input EXCEPTION;
304 pragma exception_init(malformed_wrap_input, -24230);
305 end;