[Home] [Help]
PACKAGE BODY: APPS.AD_LONGTOLOB_PKG
Source
1 PACKAGE BODY Ad_LongToLob_Pkg
2 -- $Header: adl2lpkgb.pls 120.2 2012/01/02 09:03:25 asutrala ship $
3 AS
4 TYPE TableNames_Tbl_Type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER ;
5 TYPE To_DataType_Tbl_Type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER ;
6 -- Collection to store the tables to be migrated
7 -- remove
8 -- g_TableNames_Tbl TableNames_Tbl_Type ;
9 -- Collection to store the target data type.
10 -- g_To_DataType_Tbl To_DataType_Tbl_Type ;
11
12 -- g_Counter NUMBER ;
13
14 -- The following variable is for specific table to be registered
15 g_Specific_Table VARCHAR2 (40);
16
17 -- The following variable is for specific product to be registered
18 g_Specific_Product VARCHAR2 (30);
19
20 -- The following variable is for specific schema to be registered
21 g_Specific_Schema VARCHAR2 (30);
22
23 --
24 -- This function gives the current status of the table.
25 -- Status indicates the stage of processing of the table.
26 -- Refer to the defined status in the package above.
27 --
28
29 FUNCTION get_table_status( p_Schema IN VARCHAR2 ,
30 p_Table_Name IN VARCHAR2 ,
31 p_Old_Column_Name IN VARCHAR2 )
32 RETURN VARCHAR2
33 IS
34 l_status VARCHAR2 (30);
35 BEGIN
36 SELECT status
37 INTO l_status
38 FROM ad_long_column_conversions
39 WHERE schema_name = p_Schema
40 AND table_name = p_Table_Name
41 AND old_column_name = p_Old_Column_Name;
42
43 RETURN(l_status);
44
45 EXCEPTION
46 WHEN NO_DATA_FOUND THEN
47 RETURN (G_INITIALIZED_STATUS);
48 END get_table_status;
49
50 --
51 -- This procedure is used to update the status of each table
52 -- after each step in the conversion process
53 --
54 PROCEDURE update_table_status( p_Schema IN VARCHAR2 ,
55 p_Table_Name IN VARCHAR2 ,
56 p_Old_Column_Name IN VARCHAR2 ,
57 p_Status IN VARCHAR2 )
58 IS
59 l_current_status VARCHAR2 (30);
60 l_invalid_change BOOLEAN := FALSE;
61 BEGIN
62 --
63 -- if the requested status is not in the following set raise an error.
64 --
65 IF (p_Status NOT IN (G_INITIALIZED_STATUS,
66 G_ADD_NEW_COLUMN_STATUS,
67 G_ADD_TRIGGER_STATUS,
68 G_UPDATE_ROWS_STATUS,
69 G_COMPLETE_STATUS,
70 G_DROP_OLD_COLUMN_STATUS))
71 THEN
72 RAISE_APPLICATION_ERROR (-20001,
73 'update_table_status() - invalid status : '||p_Status);
74
75 END IF ;
76
77 --
78 -- Get the current status of the record and compare it with the
79 -- new status to validate for the valid combinations.
80 --
81 l_current_status := get_table_status( p_Schema,
82 p_Table_Name,
83 p_Old_Column_Name);
84
85 /*IF ((p_Status = l2l_pack.G_ADD_NEW_COLUMN_STATUS AND
86 l_current_status <> l2l_pack.G_UNINITIALIZED_STATUS)
87 OR
88 (p_Status = l2l_pack.G_ADD_TRIGGER_STATUS AND
89 l_current_status <> l2l_pack.G_ADD_NEW_COLUMN_STATUS)
90 OR
91 (p_Status = l2l_pack.G_UPDATE_ROWS_STATUS AND
92 l_current_status <> l2l_pack.G_ADD_TRIGGER_STATUS)
93 OR
94 (p_Status = l2l_pack.G_SWAP_STATUS1 AND
95 l_current_status <> l2l_pack.G_UPDATE_ROWS_STATUS)
96 OR
97 (p_Status = l2l_pack.G_SWAP_STATUS2 AND
98 l_current_status <> l2l_pack.G_SWAP_STATUS1)
99 OR
100 (p_Status = l2l_pack.G_COMPLETE_STATUS AND
101 l_current_status <> l2l_pack.G_SWAP_STATUS2)
102 )
103 THEN
104 RAISE_APPLICATION_ERROR (-20001,
105 'update_table_status() - invalid status change : '||
106 l_current_status||' to '||p_Status);
107 END IF ; */
108
109 --
110 -- Update the table with the new status.
111 --
112 UPDATE AD_LONG_COLUMN_CONVERSIONS
113 SET status = p_Status
114 WHERE schema_name = p_Schema
115 AND table_name = p_Table_Name
116 AND old_column_name = p_Old_Column_Name;
117
118 COMMIT ;
119
120 END update_table_status;
121
122 FUNCTION check_to_register(
123 p_Product IN VARCHAR2 ,
124 p_Table_Name IN VARCHAR2 ,
125 p_Schema IN VARCHAR2 )
126 RETURN BOOLEAN
127 AS
128 l_register_flag BOOLEAN ;
129 BEGIN
130 l_register_flag := FALSE ;
131 IF ( g_Specific_Table IS NULL AND g_Specific_Product IS NULL AND
132 g_Specific_Schema IS NULL ) THEN -- outer if
133 l_register_flag := TRUE ;
134 ELSE
135 -- Register the specific table passed
136 IF ( g_Specific_Table IS NOT NULL AND
137 g_Specific_Table = p_Table_Name ) THEN
138 l_register_flag := TRUE ;
139 END IF ;
140 -- Register all the tables for specific product passed
141 IF ( g_Specific_Product IS NOT NULL AND
142 g_Specific_Product = p_Product ) THEN
143 l_register_flag := TRUE ;
144 END IF ;
145 -- Register tables for specific schema passed
146 IF (g_Specific_Schema IS NOT NULL AND
147 g_Specific_Schema = p_Schema ) THEN
148 l_register_flag := TRUE ;
149 END IF ;
150 END IF ; -- outer if
151
152 RETURN l_register_flag ;
153 END check_to_register;
154 --
155 -- This procedures populated the initial data for the migration
156 --
157 PROCEDURE register_table(
158 p_Product IN VARCHAR2 ,
159 p_Table_Name IN VARCHAR2 ,
160 p_Old_Column_Name IN VARCHAR2 ,
161 p_Old_Data_Type IN VARCHAR2 ,
162 p_New_column_name IN VARCHAR2 ,
163 p_New_Data_Type IN VARCHAR2 ,
164 p_Action IN VARCHAR2 )
165 AS
166 l_Status VARCHAR2 (50);
167 l_Schema VARCHAR2 (50); -- Stores the oracle schema name
168 l_dummy_stat VARCHAR2 (100); -- dummy variable, not used
169 l_dummy_ind VARCHAR2 (100); -- dummy variable, not used
170 l_ret_val BOOLEAN ; -- dummy variable, not used
171 l_New_Column_Name VARCHAR2 (50);
172 l_register_flg BOOLEAN ;
173 BEGIN
174 IF ( p_Action <> G_NO_ACTION ) THEN -- if p_action <> no action
175
176 IF p_Action IN ( G_DROP_COLUMN ) THEN
177 l_Status := G_DROP_OLD_COLUMN_STATUS;
178 ELSE
179 l_Status := G_INITIALIZED_STATUS;
180 END IF ;
181
182 IF ( p_Old_Column_Name = p_New_Column_Name ) THEN
183 l_New_Column_Name := 'R118_'||p_Old_Column_Name;
184 ELSE
185 l_New_Column_Name := p_New_Column_Name;
186 END IF ; -- old column name, new column name
187
188 -- g_Counter := g_Counter + 1;
189 -- g_TableNames_Tbl(g_Counter) := p_Table_Name;
190 -- g_To_DataType_Tbl(g_Counter) := p_New_Data_Type;
191
192 --
193 -- The below function call is used to get the oracle schema name
194 -- for the product short name passed as parameter.
195 -- The schema names can be different from the application short names.
196 -- Here, if the p_Prodct is an invalid one the schema name returned
197 -- will be null, but since we are passing the same from a hard coded
198 -- list it should be a valid one.
199 --
200 l_ret_val := fnd_installation.get_app_info(
201 p_Product,
202 l_dummy_stat,
203 l_dummy_ind,
204 l_Schema);
205 l_register_flg := FALSE ;
206 -- Remove
207 -- dbms_output.put_line(' Product name '||p_Product);
208 -- dbms_output.put_line(' table name '||p_Table_Name);
209 -- dbms_output.put_line(' schema name '||l_Schema);
210
211 l_register_flg := check_to_register(
212 p_Product,
213 p_Table_Name,
214 l_Schema);
215 IF ( l_register_flg = TRUE ) THEN
216 INSERT INTO AD_LONG_COLUMN_CONVERSIONS (
217 schema_name, table_name, old_column_name,
218 old_data_type,
219 new_column_name, new_data_type,
220 action, status
221 )
222 SELECT l_Schema, p_Table_Name, p_Old_Column_Name,
223 p_Old_Data_Type,
224 l_New_Column_Name, p_New_Data_Type,
225 p_Action, l_Status
226 FROM dual
227 WHERE NOT EXISTS (
228 SELECT 'x'
229 FROM AD_LONG_COLUMN_CONVERSIONS l
230 WHERE l.schema_name = l_Schema
231 AND l.table_name = p_Table_Name
232 AND l.old_column_name = p_Old_Column_Name);
233 END IF ; -- end if l_register_flg
234
235 END IF ; -- end if p_action <> no action
236 END register_table;
237
238 --
239 -- This procedure is called initially to populate the
240 -- ad_long_column_conversions table with the tables and the related
241 -- information.
242 --
243 PROCEDURE initialize_process(
244 p_Specific_Table VARCHAR2 := NULL ,
245 p_Specific_Product VARCHAR2 := NULL ,
246 p_Specific_Schema VARCHAR2 := NULL )
247 IS
248 l_Par_Counter NUMBER ;
249 BEGIN
250 l_Par_Counter := 0;
251 IF ( p_Specific_Table IS NOT NULL ) THEN
252 l_Par_Counter := l_Par_Counter + 1;
253 END IF ;
254
255 IF ( p_Specific_Product IS NOT NULL ) THEN
256 l_Par_Counter := l_Par_Counter + 1;
257 END IF ;
258
259 IF ( p_Specific_Schema IS NOT NULL ) THEN
260 l_Par_Counter := l_Par_Counter + 1;
261 END IF ;
262
263 IF l_Par_Counter > 1 THEN
264 RAISE_APPLICATION_ERROR (-20001,
265 ' Error: call Initialize_process with any one of the parameters '||
266 ' p_Specific_Table/p_Specific_Product/p_Specific_Schema');
267 END IF ;
268
269 -- Set the global variables now
270 g_Specific_Table := p_Specific_Table;
271 g_Specific_Product := p_Specific_Product;
272 g_Specific_Schema := p_Specific_Schema;
273
274 -- remove
275 -- g_Counter := 0 ;
276
277 register_table('AHM', 'AHM_DBA_CONSTRAINTS', 'SEARCH_CONDITION', 'LONG', 'SEARCH_CONDITION', 'CLOB', G_WITH_DATA );
278 register_table('AHM', 'AHM_DBA_IND_EXPRESSIONS', 'COLUMN_EXPRESSION', 'LONG', 'COLUMN_EXPRESSION', 'CLOB', G_WITH_DATA );
279 register_table('AHM', 'AHM_DBA_TAB_COLUMNS', 'DATA_DEFAULT', 'LONG', 'DATA_DEFAULT', 'CLOB', G_WITH_DATA );
280 register_table('ALR', 'ALR_ALERTS', 'SQL_STATEMENT_TEXT', 'LONG', 'SQL_STATEMENT_TEXT', 'CLOB', G_WITH_DATA );
281 register_table('ALR', 'ALR_PROFILE_OPTIONS', 'PROFILE_OPTION_LONG', 'LONG', 'PROFILE_OPTION_LONG', 'CLOB', G_WITH_DATA );
282 register_table('ALR', 'ALR_RESPONSE_MESSAGES', 'BODY', 'LONG', 'BODY', 'CLOB', G_WITH_DATA );
283 register_table('ALR', 'ALR_VALID_RESPONSES', 'RESPONSE_TEXT', 'LONG', 'RESPONSE_TEXT', 'CLOB', G_WITH_DATA );
284 register_table('AMS', 'AMS_LIST_QUERIES_ALL', 'QUERY', 'LONG', 'QUERY', 'CLOB', G_WITH_DATA );
285 register_table('AR', 'AR_APP_RULE_SETS', 'RULE_SOURCE', 'LONG', 'RULE_SOURCE', 'CLOB', G_WITH_DATA );
286 register_table('PER', 'HR_ALL_ORGANIZATION_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
287 register_table('AZ', 'AZ_SELECTION_SET_APIS', 'FILTERING_PARAMETER', 'LONG', 'FILTERING_PARAMETER', 'CLOB', G_WITH_DATA );
288 register_table('AZ', 'AZ_SELECTION_SET_ENTITIES_B', 'FILTERING_PARAMETERS', 'LONG', 'FILTERING_PARAMETERS', 'CLOB', G_WITH_DATA );
289 register_table('AZ', 'AZ_STRUCTURE_APIS_B', 'FILTERING_PARAMETER', 'LONG', 'FILTERING_PARAMETER', 'CLOB', G_WITH_DATA );
290 register_table('BIC', 'BIC_DEBUG', 'MESSAGE', 'LONG', 'MESSAGE', 'CLOB', G_WITH_DATA );
291 register_table('BIS', 'BIS_SCHEDULER', 'CACHED_GRAPH', 'LONG', 'CACHED_GRAPH', 'CLOB', G_WITH_DATA );
292 register_table('BOM', 'BOM_DELETE_SQL_STATEMENTS', 'SQL_STATEMENT', 'LONG', 'SQL_STATEMENT', 'CLOB', G_WITH_DATA );
293 register_table('BSC', 'BSC_SYS_FILES', 'FILE_BODY', 'LONG RAW', 'FILE_BODY', 'BLOB', G_WITH_DATA );
294 register_table('CN', 'CN_OBJECTS_ALL', 'STATEMENT_TEXT', 'LONG', 'STATEMENT_TEXT', 'CLOB', G_WITH_DATA );
295 register_table('CN', 'CN_PROCESS_AUDITS_ALL', 'STATEMENT_TEXT', 'LONG', 'STATEMENT_TEXT', 'CLOB', G_WITH_DATA );
296 register_table('CN', 'CN_TABLE_MAPS_ALL', 'FILTER', 'LONG', 'FILTER', 'CLOB', G_WITH_DATA );
297 register_table('CZ', 'CZ_JRAD_CHUNKS', 'XML_CHUNK', 'LONG', 'XML_CHUNK', 'CLOB', G_WITH_DATA );
298 register_table('DDD', 'DDD_3DI_DATA', 'DATA', 'LONG RAW', 'DATA', 'BLOB', G_WITH_DATA );
299 register_table('DDD', 'DDD_COLLABORATION_DATA', 'BINARY_DATA', 'LONG RAW', 'BINARY_DATA', 'BLOB', G_WITH_DATA );
300 register_table('DDD', 'DDD_MODEL_ATTRIBUTE_VALUES', 'BINARYVAL', 'LONG RAW', 'BINARYVAL', 'BLOB', G_WITH_DATA );
301 register_table('DDD', 'DDD_UPDATES_DATA', 'DATA', 'LONG RAW', 'DATA', 'BLOB', G_WITH_DATA );
302 register_table('FF', 'FF_COMPILED_INFO_F', 'COMPILED_TEXT', 'LONG', 'COMPILED_TEXT', 'CLOB', G_NO_ACTION );
303 register_table('FF', 'FF_FORMULAS_F', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
304 register_table('FF', 'FF_QP_REPORTS', 'QP_TEXT', 'LONG', 'QP_TEXT', 'CLOB', G_WITH_DATA );
305 register_table('FF', 'FF_ROUTES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
306 register_table('FND', 'FND_COMPILED_DESCRIPTIVE_FLEXS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
307 register_table('FND', 'FND_COMPILED_ID_FLEX_STRUCTS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
308 register_table('FND', 'FND_COMPILED_ID_FLEXS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
309 register_table('FND', 'FND_DOCUMENTS_LONG_RAW', 'LONG_RAW', 'LONG RAW', 'LONG_RAW', 'BLOB', G_WITH_DATA );
310 register_table('FND', 'FND_DOCUMENTS_LONG_TEXT', 'LONG_TEXT', 'LONG', 'LONG_TEXT', 'CLOB', G_WITH_DATA );
311 register_table('FND', 'FND_FLEX_VALIDATION_EVENTS', 'USER_EXIT', 'LONG', 'USER_EXIT', 'CLOB', G_WITH_DATA );
312 register_table('FND', 'FND_FLEX_VALIDATION_TABLES', 'ADDITIONAL_WHERE_CLAUSE', 'LONG', 'ADDITIONAL_WHERE_CLAUSE', 'CLOB', G_WITH_DATA );
313 register_table('FND', 'FND_LOBS_DOCUMENT', 'CONTENT', 'LONG RAW', 'CONTENT', 'BLOB', G_WITH_DATA );
314 register_table('FND', 'FND_PLAN_TABLE', 'OTHER', 'LONG', 'OTHER', 'CLOB', G_WITH_DATA );
315 register_table('FND', 'FND_VIEWS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
316 register_table('FV', 'FV_CFS_REP_LINES', 'FOOTNOTES', 'LONG', 'FOOTNOTES', 'CLOB', G_WITH_DATA );
317 register_table('GMA', 'SY_PURG_DEF', 'SQLSTATEMENT', 'LONG', 'SQLSTATEMENT', 'CLOB', G_WITH_DATA );
318 register_table('GMD', 'GMD_REPLACE', 'QUERY', 'LONG', 'QUERY', 'CLOB', G_WITH_DATA );
319 register_table('HXC', 'HXC_DEBUG_TEXT', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
320 register_table('HXC', 'HXC_TIME_CATEGORIES', 'TIME_SQL', 'LONG', 'TIME_SQL', 'CLOB', G_WITH_DATA );
321 register_table('IBA', 'IBA_R_RULECONTEXTS', 'RULETEXT', 'LONG', 'RULETEXT', 'CLOB', G_WITH_DATA );
322 register_table('IES', 'IES_QUESTION_DATA', 'FREEFORM_LONG', 'LONG', 'FREEFORM_LONG', 'CLOB', G_WITH_DATA );
323 register_table('IGI', 'IGI_DOS_ITEMS', 'ITEM_TEXT', 'LONG', 'ITEM_TEXT', 'CLOB', G_WITH_DATA );
324 register_table('IGI', 'IGI_EXP_POS_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
325 register_table('IGS', 'IGS_CO_DTL', 'CORD_TEXT', 'LONG', 'CORD_TEXT', 'CLOB', G_WITH_DATA );
326 register_table('IGS', 'IGS_CO_DTL_OLE', 'CORD_OLE', 'LONG RAW', 'CORD_OLE', 'BLOB', G_WITH_DATA );
327 register_table('IGS', 'IGS_GE_NOTE', 'NOTE_OLE', 'LONG RAW', 'NOTE_OLE', 'BLOB', G_WITH_DATA );
328 register_table('IGS', 'IGS_PE_PERSON_IMAGE', 'PERSON_IMAGE', 'LONG RAW', 'PERSON_IMAGE', 'BLOB', G_WITH_DATA );
329 register_table('IGS', 'IGS_UC_APPLICANTS', 'PERSONAL_STATEMENT', 'LONG', 'PERSONAL_STATEMENT', 'CLOB', G_WITH_DATA );
330 register_table('IGS', 'IGS_UC_IREFRNC_INTS', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
331 register_table('IGS', 'IGS_UC_ISTMNT_INTS', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
332 register_table('IGS', 'IGS_UC_MV_IVSTMNT', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
333 register_table('IGS', 'IGS_UC_OFFER_CONDS', 'LETTER_TEXT', 'LONG', 'LETTER_TEXT', 'CLOB', G_WITH_DATA );
334 register_table('IGW', 'IGW_BUDGET_DETAILS', 'BUDGET_JUSTIFICATION', 'LONG', 'BUDGET_JUSTIFICATION', 'CLOB', G_WITH_DATA );
338 register_table('JTF', 'JTF_BRM_RULES_B', 'VIEW_DEFINITION', 'LONG', 'VIEW_DEFINITION', 'CLOB', G_WITH_DATA );
335 register_table('IGW', 'IGW_REPORT_BUDG_JUSTIFICATION', 'JUSTIFICATION', 'LONG', 'JUSTIFICATION', 'CLOB', G_WITH_DATA );
336 register_table('IGW', 'IGW_REPORT_ITEMIZED_BUDGET', 'EXPENDITURE_DESCRIPTION', 'LONG', 'EXPENDITURE_DESCRIPTION', 'CLOB', G_WITH_DATA );
337 register_table('INV', 'MTL_SHORT_CHK_STATEMENTS', 'SHORT_STATEMENT', 'LONG', 'SHORT_STATEMENT', 'CLOB', G_WITH_DATA );
339 register_table('JTF', 'JTF_R_RULECONTEXTS', 'RULETEXT', 'LONG', 'RULETEXT', 'CLOB', G_WITH_DATA );
340 register_table('JTF', 'JTF_R_RULESETS_B', 'RULESET_SER', 'LONG', 'RULESET_SER', 'CLOB', G_WITH_DATA );
341 register_table('MSC', 'MSC_ERRORS', 'RROW', 'LONG', 'RROW', 'CLOB', G_WITH_DATA );
342 register_table('N/A', 'BEN_COPY_ENTITY_RESULTS', 'INFORMATION323', 'LONG', 'INFORMATION323', 'CLOB', G_WITH_DATA );
343 register_table('N/A', 'CSF_M_LOBS_INQ', 'FILE_DATA', 'LONG RAW', 'FILE_DATA', 'BLOB', G_WITH_DATA );
344 register_table('N/A', 'CSI_XNP_MSGS_TEMP', 'MSG_TEXT', 'LONG', 'MSG_TEXT', 'CLOB', G_NO_ACTION );
345 register_table('N/A', 'CSM_CUSTOM_7_INQ', 'ATTRIBUTE30', 'LONG RAW', 'ATTRIBUTE30', 'BLOB', G_WITH_DATA );
346 register_table('N/A', 'IES_META_PROPERTY_VALUES', 'LONG_VAL', 'LONG', 'LONG_VAL', 'CLOB', G_WITH_DATA );
347 register_table('N/A', 'RG_REPORT_STANDARD_AXES_B', 'PERIOD_QUERY', 'LONG', 'PERIOD_QUERY', 'VARCHAR2', G_WITH_DATA );
348 register_table('OE', 'SO_ACTION_CLAUSES', 'WHERE_CLAUSE', 'LONG', 'WHERE_CLAUSE', 'CLOB', G_NO_ACTION );
349 register_table('OE', 'SO_EXCEPTIONS', 'MESSAGE_TEXT', 'LONG', 'MESSAGE_TEXT', 'CLOB', G_NO_ACTION );
350 register_table('OE', 'SO_HEADERS_INTERFACE_ALL', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_NO_ACTION );
351 register_table('OE', 'SO_HOLD_RELEASES', 'RELEASE_COMMENT', 'LONG', 'RELEASE_COMMENT', 'CLOB', G_NO_ACTION );
352 register_table('OE', 'SO_HOLD_SOURCES_ALL', 'HOLD_COMMENT', 'LONG', 'HOLD_COMMENT', 'CLOB', G_NO_ACTION );
353 register_table('OE', 'SO_LINE_APPROVALS', 'APPROVAL_COMMENT', 'LONG', 'APPROVAL_COMMENT', 'CLOB', G_WITH_DATA );
354 register_table('OE', 'SO_NOTES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_NO_ACTION );
355 register_table('OE', 'SO_OBJECTS', 'COMPILED_INFORMATION', 'LONG', 'COMPILED_INFORMATION', 'CLOB', G_NO_ACTION );
356 register_table('OE', 'SO_ORDER_APPROVALS', 'APPROVAL_COMMENT', 'LONG', 'APPROVAL_COMMENT', 'CLOB', G_WITH_DATA );
357 register_table('OE', 'SO_ORDER_CANCELLATIONS', 'CANCEL_COMMENT', 'LONG', 'CANCEL_COMMENT', 'CLOB', G_NO_ACTION );
358 register_table('OE', 'SO_PICKING_CANCELLATIONS', 'CANCEL_COMMENT', 'LONG', 'CANCEL_COMMENT', 'CLOB', G_NO_ACTION );
359 register_table('OE', 'SO_STANDARD_VALUE_RULE_SETS', 'COMPILED_INFORMATION', 'LONG', 'COMPILED_INFORMATION', 'CLOB', G_NO_ACTION );
360 register_table('OFA', 'FA_RX_LOV', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'VARCHAR2', G_WITH_DATA );
361 register_table('OKE', 'OKE_K_COMMUNICATIONS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
362 register_table('PAY', 'PAY_BALANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
363 register_table('PA', 'PA_RULES', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'CLOB', G_WITH_DATA );
364 register_table('PAY', 'PAY_CALENDARS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
365 register_table('PAY', 'PAY_CONSOLIDATION_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
366 register_table('PAY', 'PAY_CUSTOMIZED_RESTRICTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
367 register_table('PAY', 'PAY_ELEMENT_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
368 register_table('PAY', 'PAY_MONETARY_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
369 register_table('PAY', 'PAY_PAYROLL_ACTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
370 register_table('PAY', 'PAY_PROCESS_GROUP_ACTIONS', 'ARGUMENT_LIST', 'LONG', 'ARGUMENT_LIST', 'CLOB', G_WITH_DATA );
371 register_table('PAY', 'PAY_RATES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
372 register_table('PAY', 'PAY_SHADOW_FORMULAS', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
373 register_table('PAY', 'PAY_WC_FUNDS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
374 register_table('PAY', 'PAY_WCI_ACCOUNTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
375 register_table('PAY', 'PAY_WCI_OCCUPATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
376 register_table('PAY', 'PAY_WCI_RATES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
377 register_table('PAY', 'PER_PAY_PROPOSALS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
378 register_table('PER', 'BEN_BENEFIT_CLASSIFICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
379 register_table('PER', 'HR_ALL_POSITIONS_F', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
380 register_table('PER', 'HR_API_TRANSACTIONS', 'URL', 'LONG', 'URL', 'VARCHAR2', G_WITH_DATA );
381 register_table('PER', 'HR_COMMENTS', 'COMMENT_TEXT', 'LONG', 'COMMENT_TEXT', 'CLOB', G_WITH_DATA );
382 register_table('PER', 'HR_PUMP_BATCH_LINES', 'PLONGVAL', 'LONG', 'PLONGVAL', 'CLOB', G_WITH_DATA );
383 register_table('PER', 'HR_QUEST_FIELDS', 'HTML_TEXT', 'LONG', 'HTML_TEXT', 'CLOB', G_WITH_DATA );
384 register_table('PER', 'HR_QUESTIONNAIRES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
385 register_table('PER', 'HR_S_BALANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
386 register_table('PER', 'HR_S_BENEFIT_CLASSIFICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
387 register_table('PER', 'HR_S_ELEMENT_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
388 register_table('PER', 'HR_S_FORMULAS_F', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
389 register_table('PER', 'HR_S_MONETARY_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
390 register_table('PER', 'HR_S_QP_REPORTS', 'QP_TEXT', 'LONG', 'QP_TEXT', 'CLOB', G_WITH_DATA );
391 register_table('PER', 'HR_S_ROUTES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
392 register_table('PER', 'HR_TIPS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_NO_ACTION );
396 register_table('PER', 'PER_ALL_POSITIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
393 register_table('PER', 'PER_ABSENCE_ATTENDANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
394 register_table('PER', 'PER_ABSENCE_ATTENDANCES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
395 register_table('PER', 'PER_ADDRESSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
397 register_table('PER', 'PER_ALL_VACANCIES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
398 register_table('PER', 'PER_APPLICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
399 register_table('PER', 'PER_APPRAISAL_TEMPLATES', 'INSTRUCTIONS', 'LONG', 'INSTRUCTIONS', 'CLOB', G_WITH_DATA );
400 register_table('PER', 'PER_ASSESSMENT_TYPES', 'INSTRUCTIONS', 'LONG', 'INSTRUCTIONS', 'CLOB', G_WITH_DATA );
401 register_table('PER', 'PER_BOOKINGS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
402 register_table('PER', 'PER_BUDGET_VERSIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
403 register_table('PER', 'PER_BUDGETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
404 register_table('PER', 'PER_CAREER_PATHS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
405 register_table('PER', 'PER_COBRA_COV_ENROLLMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
406 register_table('PER', 'PER_COBRA_COVERAGE_STATUSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
407 register_table('PER', 'PER_CONTACT_RELATIONSHIPS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
408 register_table('PER', 'PER_EVENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
409 register_table('PER', 'PER_GRADES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
410 register_table('PER', 'PER_IMAGES', 'IMAGE', 'LONG RAW', 'IMAGE', 'BLOB', G_WITH_DATA );
411 register_table('PER', 'PER_JOB_EVALUATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
412 register_table('PER', 'PER_JOB_REQUIREMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
413 register_table('PER', 'PER_JOBS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
414 register_table('PER', 'PER_LETTER_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
415 register_table('PER', 'PER_ORGANIZATION_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
416 register_table('PER', 'PER_PARENT_SPINES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
417 register_table('PER', 'PER_PAY_BASES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
418 register_table('PER', 'PER_PAY_PROPOSAL_COMPONENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
419 register_table('PER', 'PER_PERIODS_OF_SERVICE', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
420 register_table('PER', 'PER_PERSON_ANALYSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
421 register_table('PER', 'PER_POSITION_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
422 register_table('PER', 'PER_RECRUITMENT_ACTIVITIES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
423 register_table('PER', 'PER_SCHED_COBRA_PAYMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
424 register_table('PER', 'PER_SECONDARY_ASS_STATUSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
425 register_table('PER', 'PER_SPECIAL_INFO_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
426 register_table('PER', 'PER_VALID_GRADES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
427 register_table('PO', 'PO_ACCEPTANCES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_WITH_DATA );
428 register_table('PO', 'PO_NOTES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_WITH_DATA );
429 register_table('PQH', 'PQH_COPY_ENTITY_RESULTS', 'LONG_ATTRIBUTE1', 'LONG', 'LONG_ATTRIBUTE1', 'CLOB', G_WITH_DATA );
430 register_table('PQH', 'PQH_POSITION_TRANSACTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
431 register_table('PQH', 'PQH_PTX_SHADOW', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
432 register_table('PQH', 'PQH_TJR_SHADOW', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
433 register_table('PQH', 'PQH_TXN_JOB_REQUIREMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
434 register_table('RG', 'RG_REPORT_AXIS_SETS', 'COLUMN_SET_HEADER', 'LONG', 'COLUMN_SET_HEADER', 'VARCHAR2', G_WITH_DATA );
435 register_table('RG', 'RG_REPORT_STANDARD_AXES', 'PERIOD_QUERY', 'LONG', 'PERIOD_QUERY', 'VARCHAR2', G_WITH_DATA );
436 register_table('RLA', 'RLA_DEMAND_EXCEPTIONS', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_NO_ACTION );
437 register_table('RLM', 'RLM_DEMAND_EXCEPTIONS', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_WITH_DATA );
438 register_table('WMS', 'WMS_LABEL_REQUESTS_HIST', 'LABEL_CONTENT', 'LONG', 'LABEL_CONTENT', 'CLOB', G_WITH_DATA );
439 register_table('WSH', 'WSH_SAVED_QUERIES_B', 'PSEUDO_QUERY', 'LONG', 'PSEUDO_QUERY', 'CLOB', G_WITH_DATA );
440 COMMIT ;
441
442 g_Specific_Table := NULL ;
443 g_Specific_Product := NULL ;
444 g_Specific_Schema := NULL ;
445
446 END initialize_process;
447
448 --
449 -- The following procedure alters the table to add the new column.
450 -- with the new data type.
451 -- For each of the tables from AD_LONG_COLUMN_CONVERSIONS
452 -- this procedure has to be called repeatedly.
453 --
454 PROCEDURE add_new_column(p_Schema IN VARCHAR2 ,
455 p_Table_Name IN VARCHAR2 ,
456 p_Old_Column_Name IN VARCHAR2 ,
457 p_New_Column_Name IN VARCHAR2 ,
458 p_New_Data_Type IN VARCHAR2 ,
459 p_Curr_Status IN VARCHAR2 ,
460 p_Action IN VARCHAR2 )
461 IS
462 l_stmt VARCHAR2 (500);
463 l_New_Column_Name VARCHAR2 (50);
464 l_New_Status VARCHAR2 (30);
465 l_New_Data_Type VARCHAR2 (30);
466
467 BEGIN -- procedure
468 --
469 -- The new column has to be added for actions with_data and
470 -- without_data only.
471 -- And, the new columns will be added only when the tables are in
472 -- the initialized status. The below if condition checks both.
473 --
477 l_New_Column_Name := p_New_Column_Name;
474 IF ( p_Action IN (G_WITH_DATA, G_WITHOUT_DATA) AND
475 p_Curr_Status = G_INITIALIZED_STATUS ) THEN
476
478 --
479 -- If the new column name is same as the old column name
480 -- it indicates that the new column has to be added with a
481 -- different name.
482 --
483
484 IF p_New_Data_Type = 'VARCHAR2' THEN
485 l_New_Data_Type := 'VARCHAR2(4000)';
486 ELSE
487 l_New_Data_Type := p_New_Data_Type;
488 END IF ;
489 l_stmt := 'ALTER TABLE '||p_schema||'.'||p_Table_Name||
490 ' ADD ( '||l_New_Column_Name||' '||l_New_Data_Type||')';
491
492 BEGIN -- inner block
493 EXECUTE IMMEDIATE l_stmt;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 IF (SQLCODE = -1430) THEN
498 NULL ;
499 ELSE
500 -- DBMS_OUTPUT.PUT_LINE('the exeception is here only'); --remove
501 RAISE ;
502 END IF ;
503 END ; -- inner block
504 --
505 -- If the action is without_data it means that it is enough to add
506 -- the new column. No need to migrate the data. So for such cases
507 -- the process completes here. Hence for them status is updated as
508 -- COMPLETED, so that it wont be picked up in the next stages again.
509 --
510 IF (p_Action = G_WITHOUT_DATA ) THEN
511 l_New_Status := G_DROP_OLD_COLUMN_STATUS;
512 ELSE
513 l_New_Status := G_ADD_NEW_COLUMN_STATUS;
514 END IF ;
515 update_table_status(p_Schema,
516 p_Table_Name,
517 p_Old_Column_Name,
518 l_New_Status );
519
520 END IF ; -- end if p_action
521 END add_new_column;
522
523 --
524 -- This function creates the triggers those are needed to handle
525 -- online updates to the long data
526 --
527 PROCEDURE create_transform_triggers(
528 p_Schema IN VARCHAR2 ,
529 p_Table_Name IN VARCHAR2 ,
530 p_Old_Column_Name IN VARCHAR2 ,
531 p_New_Column_Name IN VARCHAR2 ,
532 p_New_Data_Type IN VARCHAR2 )
533
534 IS
535 l_stmt VARCHAR2 (2000);
536 l_trig_name VARCHAR2 (30);
537 l_lob_value_col VARCHAR2 (30);
538 BEGIN
539 --
540 -- Depending on the target data type needed, assign the column name
541 -- in the temporary table.
542 --
543 IF (p_New_Data_Type = 'CLOB') THEN
544 l_lob_value_col := 'clob_value';
545 ELSIF (p_New_Data_Type = 'BLOB') THEN
546 l_lob_value_col := 'blob_value';
547 ELSIF (p_New_Data_Type = 'VARCHAR2') THEN
548 l_lob_value_col := 'clob_value';
549 END IF ;
550
551 --
552 -- create a ROW level trigger that inserts rowid for changed or newly
553 -- added rows into a temporary table. Trigger is created on the
554 -- LONG column.
555 --
556
557 l_trig_name := substr(p_Table_Name, 1, 24)||'_$R2U1';
558
559 l_stmt := ' CREATE OR REPLACE TRIGGER '||l_trig_name||
560 ' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
561 ' ON '||p_Schema||'.'||p_Table_Name||
562 ' FOR EACH ROW'||
563 ' BEGIN'||
564 ' INSERT INTO AD_LONG_CONV_TEMP(table_name, apps_rowid) '||
565 ' VALUES ('''||p_Table_Name||''', :new.rowid);'||
566 ' END;';
567
568 EXECUTE IMMEDIATE l_stmt;
569 -- dbms_output.put_line('drop trigger '||l_trig_name ||chr(10)||'/');
570 -- dbms_output.put_line('the first trigger '); -- remove
571 -- dbms_output.put_line(substr(l_stmt,1,220)); -- remove
572 -- dbms_output.put_line(substr(l_stmt,220,200)); -- remove
573
574 --
575 -- create a STATEMENT level trigger that updates the main table
576 -- for the changed rows
577 --
578
579 l_trig_name := substr(p_Table_Name, 1, 24)||'_$R2U2';
580
581
582 l_stmt := ' CREATE OR REPLACE TRIGGER '||l_trig_name||
583 ' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
584 ' ON '||p_Schema||'.'||p_Table_Name||
585 ''||
586 ' DECLARE '||
587 ' CURSOR c_tmp IS '||
588 ' SELECT apps_rowid, '||l_lob_value_col||
589 ' FROM AD_LONG_CONV_TEMP2'||
590 ' WHERE table_name = '''||p_Table_Name||''';'||
591 ''||
592 ' BEGIN'||
593 ''||
594 ' INSERT INTO AD_LONG_CONV_TEMP2('||
595 ' table_name, apps_rowid, '||l_lob_value_col ||' ) '||
596 ' SELECT '''||p_Table_Name||''', t.apps_rowid, '||
597 ' to_lob(f.'||p_Old_Column_Name ||')'||
598 ' FROM AD_LONG_CONV_TEMP t, '||p_Schema||
599 '.'||p_Table_Name||' f '||
600 ' WHERE f.rowid = t.apps_rowid;'||
601 ''||
602 ' FOR c_rec in c_tmp LOOP '||
603 ' UPDATE '||p_Schema||'.'||p_Table_Name ||
604 ' SET '||p_New_Column_Name ||' = c_rec.'||l_lob_value_col||
605 ' WHERE rowid = c_rec.apps_rowid;'||
606 ' END LOOP;'||
607 ' END;';
608
609 -- dbms_output.put_line('drop trigger '||l_trig_name ||chr(10)||'/');
610 -- remove
611 -- dbms_output.put_line(substr(l_stmt,1,200));
612 -- dbms_output.put_line(substr(l_stmt,201,200));
616 -- dbms_output.put_line('after creation of the second trigger ');
613 -- dbms_output.put_line(substr(l_stmt,401,200));
614 -- dbms_output.put_line(substr(l_stmt,601,200));
615 EXECUTE IMMEDIATE l_stmt;
617
618 --
619 -- Update the table status to G_ADD_TRIGGER_STATUS
620 --
621 update_table_status( p_Schema ,
622 p_Table_Name ,
623 p_Old_Column_Name ,
624 G_ADD_TRIGGER_STATUS);
625
626 END create_transform_triggers;
627
628 PROCEDURE update_new_data(p_Schema IN VARCHAR2 ,
629 p_Old_Table_Name IN VARCHAR2 ,
630 p_Old_Column_Name IN VARCHAR2 ,
631 p_Old_Data_Type IN VARCHAR2 ,
632 p_New_Column_Name IN VARCHAR2 ,
633 p_Batch_Size IN NUMBER DEFAULT 1000)
634 IS
635 l_stmt VARCHAR2 (10000);
636 l_lob_value_col_name VARCHAR2 (30);
637 BEGIN
638
639 IF (p_Old_Data_Type = 'LONG') THEN
640 l_lob_value_col_name := 'clob_value';
641 ELSE
642 l_lob_value_col_name := 'blob_value';
643 END IF ;
644
645 l_stmt := ' declare '||
646 ' cursor c_tab is '||
647 ' select rowid'||
648 ' from '||p_Schema||'.'||p_Old_Table_Name||
649 ' where '||p_Old_Column_Name||' is not null '||
650 ' and '||p_New_Column_Name||' is null; '||
651 ''||
652 ' cursor c_tab2 is'||
653 ' select apps_rowid, '||l_lob_value_col_name||
654 ' from ad_long_conv_temp2;'||
655 ''||
656 ' rowtab dbms_sql.urowid_table;'||
657 ''||
658 ' begin'||
659 ' open c_tab;'||
660 ''||
661 ' loop'||
662 ''||
663 ' fetch c_tab bulk collect into rowtab limit '||
664 p_Batch_Size||';'||
665 ''||
666 ' exit when rowtab.count = 0;'||
667 ''||
668 ' forall i in rowtab.FIRST..rowtab.LAST'||
669 ' insert into ad_long_conv_temp2(table_name,'||
670 ' apps_rowid, '||l_lob_value_col_name||')'||
671 ' select '''||p_Old_Table_Name||''','||
672 ' rowid, to_lob('||p_Old_Column_Name||')'||
673 ' from '||p_Schema||'.'||p_Old_Table_Name||
674 ' where rowid = rowtab(i);'||
675 ''||
676 ' for c_rec2 in c_tab2 loop'||
677 ' update '||p_Schema||'.'||p_Old_Table_Name||
678 ' set '||p_New_Column_Name||' = c_rec2.'||
679 l_lob_value_col_name||
680 ' where rowid = c_rec2.apps_rowid;'||
681 ' end loop;'||
682 ''||
683 ' commit;'||
684 ' end loop;'||
685 ''||
686 ' close c_tab;'||
687 ' end;';
688
689 --remove
690 --dbms_output.put_line(substr(l_stmt,1,200));
691 --dbms_output.put_line(substr(l_stmt,201,200));
692 --dbms_output.put_line(substr(l_stmt,401,200));
693 --dbms_output.put_line(substr(l_stmt,601,200));
694
695 EXECUTE IMMEDIATE l_stmt;
696
697 --
698 -- Update the table status to rows processed.
699 --
700 update_table_status( p_Schema,
701 p_Old_Table_Name,
702 p_Old_Column_Name,
703 G_UPDATE_ROWS_STATUS);
704 END update_new_data;
705
706 --
707 -- This function is used to get the length of the LONG data
708 --
709 FUNCTION get_long_length( p_Table_Name IN VARCHAR2,
710 p_Long_Column_Name IN VARCHAR2 ,
711 p_Rowid IN VARCHAR2 )
712 RETURN NUMBER
713 IS
714 l_cursor INTEGER;
715 l_ignore INTEGER;
716 l_stmt VARCHAR2(100);
717 out_val VARCHAR2(1001);
718 out_length INTEGER;
719 l_row_length NUMBER ;
720 num_bytes INTEGER := 1000; -- length in bytes of the chunk of data to be read.
721 -- value to be selected
722 l_offset INTEGER; -- the byte position in the LONG column at which
723
724 BEGIN
725 l_stmt := 'SELECT '||p_Long_Column_Name||' FROM '||p_Table_Name||' WHERE ROWID = '''||p_Rowid||'''';
726 l_cursor := DBMS_SQL.OPEN_CURSOR;
727 DBMS_SQL.PARSE(l_cursor, l_stmt, DBMS_SQL.NATIVE);
728 --
729 --Define the LONG column.
730 --
731 DBMS_SQL.DEFINE_COLUMN_LONG(l_cursor, 1);
732 --
733 -- Execute the query.
734 --
735 l_ignore := DBMS_SQL.EXECUTE(l_cursor);
736
737 IF DBMS_SQL.FETCH_ROWS(l_cursor) > 0 THEN
738 l_offset := 0;
739 l_row_length := 0;
740 --
741 -- Get the value of the LONG column piece by piece. Here a loop
742 -- is used to get the entire column. The loop exits when there
743 -- is no more data.
744 --
745 LOOP
746 --
747 -- Get the value of a portion of the LONG column.
748 --
749 DBMS_SQL.COLUMN_VALUE_LONG(l_cursor, 1, num_bytes, l_offset, out_val, out_length);
750 IF out_length <> 0 THEN
751 l_offset := l_offset + num_bytes;
752 l_row_length := l_row_length + out_length;
753 ELSE
754 EXIT;
755 END IF;
756 IF out_length < num_bytes THEN
757 EXIT;
758 END IF;
759 END LOOP;
760 ELSE
761 l_row_length := 0;
762 END IF;
766 WHEN OTHERS THEN
763 DBMS_SQL.CLOSE_CURSOR(l_cursor);
764 RETURN l_row_length ;
765 EXCEPTION
767 -- DBMS_OUTPUT.PUT_LINE ('Errors in function get_long_length');
768 -- DBMS_OUTPUT.PUT_LINE(sqlerrm);
769 IF DBMS_SQL.is_open(l_cursor) THEN
770 DBMS_SQL.CLOSE_CURSOR(l_cursor);
771 END IF;
772 END;
773
774 PROCEDURE write_long_rep
775 IS
776 BEGIN
777 write_long_rep('NA');
778 END ;
779
780 PROCEDURE write_long_rep( p_Path VARCHAR2)
781 IS
782
783 TYPE cur_type IS REF CURSOR ;
784 l_cursor cur_type;
785
786 fp utl_file.file_type;
787 l_Row_4000_Count NUMBER ;
788 l_str VARCHAR2 (2000);
789 l_query VARCHAR2 (400);
790 l_number NUMBER ;
791 l_Rowid VARCHAR2 (30);
792 l_File_Name VARCHAR2 (100);
793 l_Path VARCHAR2 (200);
794 l_pr NUMBER := 0;
795 l_Sl_No NUMBER := 0;
796
797 l_TableNames_Tbl TableNames_Tbl_Type ;
798 l_To_DataType_Tbl To_DataType_Tbl_Type;
799
800 CURSOR c1(p_Table_Name VARCHAR2 ) IS
801 SELECT table_name, column_name, a.owner, data_type, bytes/1024/1024 t_size
802 FROM dba_tab_columns a, dba_segments b
803 WHERE data_type IN ('LONG', 'LONG RAW')
804 AND table_name = p_Table_name
805 AND segment_name = table_name
806 AND a.owner NOT IN ('SYS','SYSTEM','OUTLN')
807 AND b.owner = a.owner ;
808
809 PROCEDURE print_data(p_String VARCHAR2 )
810 IS
811 BEGIN
812 IF l_pr = 0 THEN
813 DBMS_OUTPUT.PUT_LINE(l_str);
814 ELSE
815 utl_file.put_line(fp,l_str);
816 END IF ;
817 END print_data;
818 BEGIN
819 l_Path := p_Path;
820 IF INSTR (l_Path,'/') = 0 THEN
821 IF INSTR (l_Path,'\') = 0 THEN
822 l_Path := UPPER (p_Path);
823 END IF ;
824 END IF ;
825 IF l_Path = 'NA' THEN
826 l_pr := 0;
827 ELSE
828 l_pr := 1;
829 END IF ;
830
831 -- initialize_process;
832 -- Load the information from the control table
833 SELECT table_name, NEW_DATA_TYPE
834 BULK COLLECT
835 INTO l_TableNames_Tbl, l_To_DataType_Tbl
836 FROM ad_long_column_conversions;
837
838 IF l_TableNames_Tbl.COUNT = 0 THEN
839 RAISE_APPLICATION_ERROR (-20001,
840 ' The tables are not Initialized.'||
841 ' Please use Ad_LongToLob_Pkg.initialize_process to'||
842 ' initialize the tables for generating report. ');
843 END IF ;
844
845 BEGIN -- begin for the main block of actions
846 l_File_Name := 'long_report.'||to_char(sysdate,'DD-MON-YY.HH24:MI:SS')||'.txt';
847 IF (l_pr = 1 ) THEN
848 fp := utl_file.fopen(l_Path,l_File_Name,'w');
849 END IF ;
850
851 l_str := 'Sl.No '||lpad('Table Name',28)||'*'|| lpad('Column Name',29)||'*'|| lpad('Owner',11)||'*'||
852 lpad('Data Type',14)||'*'|| lpad('Table Size',14)||'*'|| lpad('Total Rows',14) ||'*'||
853 lpad('Not Null Rows',20)||'*'||lpad('Rows > 4000 ',20);
854 print_data(l_str);
855 l_str := lpad('-',161,'-');
856 print_data(l_str);
857
858 FOR i IN l_TableNames_Tbl.FIRST .. l_TableNames_Tbl.LAST LOOP
859 FOR rec IN c1(l_TableNames_Tbl(i)) LOOP
860 l_str := NULL ;
861 l_Row_4000_Count := 0;
862 l_Sl_No := l_Sl_No + 1;
863 l_str := rpad(l_Sl_No,6)||'-'||lpad(rec.table_name,29)||lpad(rec.column_name,30)|| lpad(rec.owner,12)||
864 lpad(rec.data_type,15)|| lpad(rec.t_size,15);
865 l_query := null;
866 l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name;
867 execute immediate l_query into l_number;
868 l_str := l_str || lpad(l_number,15);
869 l_query := null;
870 l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name||' where '||rec.column_name||' is not null';
871 execute immediate l_query into l_number;
872 l_str := l_str || lpad(l_number,20);
873 IF l_To_DataType_Tbl(i) = 'VARCHAR2' THEN
874 IF l_cursor%ISOPEN THEN
875 CLOSE l_cursor;
876 END IF ; -- if %is open
877 OPEN l_cursor FOR 'SELECT ROWID FROM '||rec.table_name;
878 LOOP
879 FETCH l_cursor INTO l_Rowid;
880 EXIT WHEN l_cursor%NOTFOUND ;
881 IF get_long_length(rec.table_name,
882 rec.column_name,
883 l_Rowid) > 4000 THEN
884 l_Row_4000_Count := l_Row_4000_Count +1;
885 END IF ;
886 END LOOP ;
887 END IF ;
888 l_str := l_str || lpad(l_Row_4000_Count ,20);
889 print_data(l_str);
890 END LOOP ;
891 END LOOP ;
892 IF (l_pr = 1 ) THEN
893 utl_file.fclose(fp);
894 END IF ;
895 END; -- end for the main block of actions
896 END write_long_rep;
897
898 --
899 -- This procedure can be used to defer the processing of a table.
900 -- It can be called at any stage of processing of the table.
901 -- I.e. Before starting the Long To LOB conversion, after adding the
902 -- new column, after creating the triggers or after converting the data
903 --
904 PROCEDURE defer_table( p_Schema IN VARCHAR2 ,
905 p_Table_Name IN VARCHAR2 )
906 IS
907 l_Current_Status ad_long_column_conversions.status%TYPE ;
908 l_Count NUMBER(3) ;
909 BEGIN
910 --
911 -- Since this procedure can be called even before starting any of the
912 -- processing steps,
913 -- the follwing select checks whether the table is initialized.
917 INTO l_Count
914 -- If not the initialize_process is called to initialize the tables.
915 --
916 SELECT COUNT(*)
918 FROM ad_long_column_conversions
919 WHERE schema_name = p_Schema
920 AND table_name = p_Table_Name ;
921
922 --
923 -- The table is not initialized, so call initialize_process
924 --
925 IF l_Count = 0 THEN
926 -- initialize_process;
927 -- Instead raise an error saying that the table is not initialized
928 raise_application_error(-20001,
929 ' The table is not Initialized.'||
930 ' Please use Ad_LongToLob_Pkg.initialize_process to'||
931 ' initialize the table for processing ');
932 END IF ;
933
934 --
935 -- Lock the row for the table to make sure that status of the
936 -- table is not being updated by any other session.
937 -- If the table name being passed is a wrong one, this select
938 -- comes out saying the exception.
939 --
940 SELECT status
941 INTO l_Current_Status
942 FROM ad_long_column_conversions
943 WHERE schema_name = p_Schema
944 AND table_name = p_Table_Name
945 FOR UPDATE NOWAIT ;
946
947 --
948 -- Now update the status to indicate that the table is deferred.
949 -- The status will be 'DEFFERRED_'||old_status.
950 --
951 UPDATE ad_long_column_conversions
952 SET status = G_DEFERRED_STATUS||'_'||status
953 WHERE schema_name = p_Schema
954 AND table_name = p_Table_Name;
955
956 -- commit the transaction
957 COMMIT ;
958 EXCEPTION
959 WHEN NO_DATA_FOUND THEN
960 RAISE_APPLICATION_ERROR(-20002, 'The table '||p_Schema||'.'||p_Table_Name||
961 ' is not initialized for Long to Lob conversion. ');
962 END defer_table;
963
964 --
965 -- This procedure is used to re-enable the deferred tables
966 --
967 PROCEDURE re_enable_table(p_Schema IN VARCHAR2 ,
968 p_Table_Name IN VARCHAR2 )
969 IS
970 l_Prev_Status ad_long_column_conversions.status%TYPE ;
971 BEGIN
972 --
973 -- Lock the entry in ad_long_column_conversions corresponding to the
974 -- table being re-enabled.
975 --
976 SELECT LTRIM (status,G_DEFERRED_STATUS||'_')
977 INTO l_Prev_Status
978 FROM ad_long_column_conversions
979 WHERE schema_name = p_Schema
980 AND table_name = p_Table_Name
981 AND status LIKE G_DEFERRED_STATUS||'_%'
982 FOR UPDATE NOWAIT ;
983
984 --
985 -- Update status of the table to the prior status.
986 --
987 UPDATE ad_long_column_conversions
988 SET status = l_Prev_Status
989 WHERE schema_name = p_Schema
990 AND table_name = p_Table_Name;
991
992 -- commit the transaction
993 COMMIT ;
994 EXCEPTION
995 WHEN NO_DATA_FOUND THEN
996 RAISE_APPLICATION_ERROR(-20002, 'The table '||p_Schema||'.'||p_Table_Name||
997 ' is not a deferred table. ');
998 END re_enable_table;
999
1000 END Ad_LongToLob_Pkg;