1 package ad_ddl as
2 /* $Header: adddls.pls 120.0 2005/05/25 12:09:32 appldev noship $ */
3 --
4 -- Package
5 -- AD_DDL
6 -- Purpose
7 -- Support for runtime DDL operations with functionality to
8 -- handle multiorg and distributed applications
9 -- Notes
10 -- 1. This package is created in a priviledged account.
11 -- It is recommended that this package be created in 'system'
12 -- 2. The priviledged account requires the following explicit
13 -- priviledges to run (i.e. these priviledges cannot be obtained
14 -- from a role, like 'dba'):
15 -- grant create user to system;
16 -- grant select any table to system;
17 -- grant create any procedure to system;
18 -- grant create any trigger to system;
19 -- grant create any view to system;
20 -- grant execute any procedure to system;
21 -- grant drop any procedure to system;
22 -- grant drop any trigger to system;
23 -- grant drop any view to system;
24 -- grant drop any synonym to system;
25 -- grant unlimited tablespace to system with admin option;
26 --
27 -- History
28 -- 15-May-95 B Lind Created
29 --
30
31 -- Global variables
32
33 error_buf varchar2(32760);
34 gbl_statement varchar2(32760);
35
36 -- global array to store array DDL text
37
38 glprogtext dbms_sql.varchar2s;
39
40 -- constants
41
42 create_table constant integer := 1;
43 create_view constant integer := 2;
44 create_sequence constant integer := 3;
45 -- Note we do not support packages until dbms_sys_sql.parse
46 -- is available that supports arrays publically
47 -- This has now been implemented as a separate procedure create_package
48 -- create_package constant integer := 4;
49 create_trigger constant integer := 5;
50 create_index constant integer := 6;
51 alter_trigger constant integer := 7;
52 drop_trigger constant integer := 8;
53 create_synonym constant integer := 9;
54 -- Note these are not supported as it is uncertain what to do in
55 -- a R10.7 database with these types of statements
56 -- grant_privilege constant integer := 10;
57 -- revoke_privilege constant integer := 11;
58 drop_table constant integer := 12;
59 drop_view constant integer := 13;
60 drop_sequence constant integer := 14;
61 drop_index constant integer := 15;
62 drop_synonym constant integer := 16;
63 alter_table constant integer := 17;
64 alter_view constant integer := 18;
65 alter_sequence constant integer := 19;
66 truncate_table constant integer := 20;
67 alter_package constant integer := 21;
68 drop_package constant integer := 22;
69 create_grants constant integer := 23;
70
71 -- fix bug 2804640 by adding these types for base schema,
72 -- because the synonyms were not droped or created in base schemas, but
73 -- in APPS and APPS_MRC
74 create_base_synonym constant integer := 24;
75 drop_base_synonym constant integer := 25;
76
77 -- public procedures and functions
78
79 procedure do_ddl (applsys_schema in varchar2,
80 application_short_name in varchar2,
81 statement_type in integer,
82 statement in varchar2,
83 object_name in varchar2);
84 --
85 -- Procedure
86 -- do_ddl
87 -- Purpose
88 -- Perform the DDL statement in the correct account(s) for the
89 -- application indicated by application_id
90 -- Arguments
91 -- applsys_schema The oracle username for the applsys account
92 -- application_short_name The application_id that the statement is for
93 -- statement_type One of the macros defined in this file
94 -- statement The sql statement
95 -- Example
96 -- none
97 -- Notes
98 -- 1. Which schema to perform the ddl operation in is:
99 -- Use current user to determine which product_group_num
100 -- is being accessed and find the associated schema for
101 -- application_id in that product_group.
102 --
103
104 procedure create_package (applsys_schema in varchar2,
105 application_short_name in varchar2,
106 package_name in varchar2,
107 is_package_body in varchar2,
108 lb in integer,
109 ub in integer);
110 --
111 -- Procedure
112 -- create_package
113 -- Purpose
114 -- Perform the DDL statement to create a package or package body in the
115 -- correct account(s) for the application indicated by application_id
116 --
117 -- Arguments and usage identical to create_plsql_object() except:
118 --
119 --
120 -- package_name Corresponds to object_name
121 --
122 -- is_package_body TRUE if this is a package body, else FALSE
123 --
124 -- not used internally, keep for backwards compatibility
125 --
126 -- Calls create_plsql_object()
127 --
128 -- Always inserts a newline after each line of package source text
129 --
130
131 procedure create_plsql_object
132 (applsys_schema in varchar2,
133 application_short_name in varchar2,
134 object_name in varchar2,
135 lb in integer,
136 ub in integer,
137 insert_newlines in varchar2,
138 comp_error out nocopy varchar2);
139 --
140 -- Procedure
141 -- create_plsql_object
142 -- Purpose
143 -- Perform the DDL statement to create a PL/SQL object in the
144 -- correct account(s) for the application indicated by application_id
145 -- Arguments
146 -- applsys_schema The oracle username for the applsys account
147 -- application_short_name The application_id that the statement is for
148 -- object_name The name of the object being created
149 -- lb line number of first line of loaded text
150 -- ub line number of last line of loaded text
151 -- insert_newlines Add newlines between each line of PL/SQL
152 -- source text ('TRUE' or 'FALSE')
153 -- comp_error 'TRUE' if the object created with
154 -- compilation errors
155 -- 'FALSE' if the object created
156 -- without compilation errors
157 -- Example
158 -- none
159 -- Notes
160 --
161 -- 1. The object creation statement must have already been loaded
162 -- by calls to build_package. This call executes the statement
163 -- built by build_package.
164 --
165 -- 2. lb should generally be 1 with ub being the last line of text.
166 -- all values between lb and ub should have been assigned via
167 -- build_package
168 --
169 -- 3. Which schema to perform the ddl operation in is:
170 -- Use current user to determine which product_group_num
171 -- is being accessed and find the associated schema for
172 -- application_id in that product_group.
173 --
174 -- 4. The current logic assumes Invoker's Rights by default
175 -- Objects without an AUTHID clause are automatically converted
176 -- to AUTHID CURRENT_USER.
177 -- To create a Definer's Rights PL/SQL object, you
178 -- must specify AUTHID DEFINER in the create line
179 -- See ad_invoker package for use of the /*nosync*/ keyword
180 -- with Definer's Rights PL/SQL objects
181 --
182
183 procedure build_package (ddl_text in varchar2,
184 row_num in integer);
185 --
186 -- Procedure
187 -- build_package
188 -- Purpose
189 -- Build the DDL statement create a package or package body
190 -- The resultant DDL statement is executed by create_package
191 -- Arguments
192 -- ddl_text One line of the package creation statement,
193 -- it can be upto 256 characters
194 -- rownum The line number that is being loaded
195 -- Example
196 -- none
197 -- Notes
198 -- none
199
200 procedure build_statement (ddl_text in varchar2,
201 row_num in integer);
202 --
203 -- Procedure
204 -- build_statement
205 -- Purpose
206 -- Build a DDL statement in 256-byte chunks
207 -- The resultant DDL statement is executed by do_array_ddl
208 -- Arguments
209 -- ddl_text One line of the DDL statement
210 -- It can be up to 256 characters
211 -- rownum The line number that is being loaded
212 -- Example
213 -- none
214 -- Notes
215 -- none
216
217 procedure do_array_ddl
218 (applsys_schema in varchar2,
219 application_short_name in varchar2,
220 statement_type in integer,
221 lb in integer,
222 ub in integer,
223 object_name in varchar2);
224 --
225 -- Procedure
226 -- do_array_ddl
227 -- Purpose
228 -- Perform the DDL statement in the correct account(s) for the
229 -- application indicated by application_id
230 --
231 -- The DDL statement must have been loaded into the glprogtext array
232 -- using build_statement
233 --
234 -- Arguments
235 -- applsys_schema The oracle username for the applsys account
236 -- application_short_name The application_id that the statement is for
237 -- statement_type One of the macros defined in this file
238 -- lb First line of DDL text in glprogtext array
239 -- ub Last line of DDL text in glprogtext array
240 -- object_name Name of the object on which to perform DDL
241 -- Example
242 -- none
243 -- Notes
244 -- 1. Which schema to perform the ddl operation in is:
245 -- Use current user to determine which product_group_num
246 -- is being accessed and find the associated schema for
247 -- application_id in that product_group.
248 -- 2. We currently only support the create view statement from this
249 -- function. Later we may support other statements.
250 --
251
252 procedure create_trigger_in_schema (schema_name in varchar2,
253 ddl_text in varchar2);
254 --
255 -- Procedure
256 -- create_trigger_in_schema
257 -- Purpose
258 -- Create a trigger in the specified schema
259 -- Arguments
260 -- schema_name The name of the schema in which to create the trigger
261 -- Must contain a copy of the APPS_DDL package
262 -- ddl_text The "create trigger" statement
263 -- Example
264 -- none
265 -- Notes
266 -- none
267
268 end ad_ddl;
[Home] [Help]