[Home] [Help]
PACKAGE BODY: APPS.AD_LONGTOLOB_PKG
Source
1 PACKAGE BODY Ad_LongToLob_Pkg
2 -- $Header: adl2lpkgb.pls 120.1 2005/09/30 06:40:19 vpalakur noship $
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_EXP_LIST_HEADERS_ALL', 'FINAL_SQL_STRING', 'LONG', 'FINAL_SQL_STRING', 'CLOB', G_WITH_DATA );
285 register_table('AMS', 'AMS_EXP_MODIFIED_TEMPLATE', 'USER_SELECT_CLAUSE', 'LONG', 'USER_SELECT_CLAUSE', 'CLOB', G_WITH_DATA );
286 register_table('AMS', 'AMS_EXP_TEMPLATE', 'SEEDED_SELECT_CLAUSE', 'LONG', 'SEEDED_SELECT_CLAUSE', 'CLOB', G_WITH_DATA );
287 register_table('AMS', 'AMS_LIST_QUERIES_ALL', 'QUERY', 'LONG', 'QUERY', 'CLOB', G_WITH_DATA );
288 register_table('AR', 'AR_APP_RULE_SETS', 'RULE_SOURCE', 'LONG', 'RULE_SOURCE', 'CLOB', G_WITH_DATA );
289 register_table('AS', 'AS_LIST_QUERIES', 'QUERY', 'LONG', 'QUERY', 'CLOB', G_WITH_DATA );
290 register_table('PER', 'HR_ALL_ORGANIZATION_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
291 register_table('AZ', 'AZ_SELECTION_SET_APIS', 'FILTERING_PARAMETER', 'LONG', 'FILTERING_PARAMETER', 'CLOB', G_WITH_DATA );
292 register_table('AZ', 'AZ_SELECTION_SET_ENTITIES_B', 'FILTERING_PARAMETERS', 'LONG', 'FILTERING_PARAMETERS', 'CLOB', G_WITH_DATA );
293 register_table('AZ', 'AZ_STRUCTURE_APIS_B', 'FILTERING_PARAMETER', 'LONG', 'FILTERING_PARAMETER', 'CLOB', G_WITH_DATA );
294 register_table('BIC', 'BIC_DEBUG', 'MESSAGE', 'LONG', 'MESSAGE', 'CLOB', G_WITH_DATA );
295 register_table('BIS', 'BIS_SCHEDULER', 'CACHED_GRAPH', 'LONG', 'CACHED_GRAPH', 'CLOB', G_WITH_DATA );
296 register_table('BOM', 'BOM_DELETE_SQL_STATEMENTS', 'SQL_STATEMENT', 'LONG', 'SQL_STATEMENT', 'CLOB', G_WITH_DATA );
297 register_table('BSC', 'BSC_SYS_FILES', 'FILE_BODY', 'LONG RAW', 'FILE_BODY', 'BLOB', G_WITH_DATA );
298 register_table('CN', 'CN_OBJECTS_ALL', 'STATEMENT_TEXT', 'LONG', 'STATEMENT_TEXT', 'CLOB', G_WITH_DATA );
299 register_table('CN', 'CN_PROCESS_AUDITS_ALL', 'STATEMENT_TEXT', 'LONG', 'STATEMENT_TEXT', 'CLOB', G_WITH_DATA );
300 register_table('CN', 'CN_TABLE_MAPS_ALL', 'FILTER', 'LONG', 'FILTER', 'CLOB', G_WITH_DATA );
301 register_table('CZ', 'CZ_JRAD_CHUNKS', 'XML_CHUNK', 'LONG', 'XML_CHUNK', 'CLOB', G_WITH_DATA );
302 register_table('DDD', 'DDD_3DI_DATA', 'DATA', 'LONG RAW', 'DATA', 'BLOB', G_WITH_DATA );
303 register_table('DDD', 'DDD_COLLABORATION_DATA', 'BINARY_DATA', 'LONG RAW', 'BINARY_DATA', 'BLOB', G_WITH_DATA );
304 register_table('DDD', 'DDD_MODEL_ATTRIBUTE_VALUES', 'BINARYVAL', 'LONG RAW', 'BINARYVAL', 'BLOB', G_WITH_DATA );
305 register_table('DDD', 'DDD_UPDATES_DATA', 'DATA', 'LONG RAW', 'DATA', 'BLOB', G_WITH_DATA );
306 register_table('FF', 'FF_COMPILED_INFO_F', 'COMPILED_TEXT', 'LONG', 'COMPILED_TEXT', 'CLOB', G_NO_ACTION );
307 register_table('FF', 'FF_FORMULAS_F', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
308 register_table('FF', 'FF_QP_REPORTS', 'QP_TEXT', 'LONG', 'QP_TEXT', 'CLOB', G_WITH_DATA );
309 register_table('FF', 'FF_ROUTES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
310 register_table('FND', 'FND_COMPILED_DESCRIPTIVE_FLEXS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
311 register_table('FND', 'FND_COMPILED_ID_FLEX_STRUCTS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
312 register_table('FND', 'FND_COMPILED_ID_FLEXS', 'COMPILED_DEFINITION', 'LONG', 'COMPILED_DEFINITION', 'CLOB', G_WITH_DATA );
313 register_table('FND', 'FND_DOCUMENTS_LONG_RAW', 'LONG_RAW', 'LONG RAW', 'LONG_RAW', 'BLOB', G_WITH_DATA );
314 register_table('FND', 'FND_DOCUMENTS_LONG_TEXT', 'LONG_TEXT', 'LONG', 'LONG_TEXT', 'CLOB', G_WITH_DATA );
315 register_table('FND', 'FND_FLEX_VALIDATION_EVENTS', 'USER_EXIT', 'LONG', 'USER_EXIT', 'CLOB', G_WITH_DATA );
316 register_table('FND', 'FND_FLEX_VALIDATION_TABLES', 'ADDITIONAL_WHERE_CLAUSE', 'LONG', 'ADDITIONAL_WHERE_CLAUSE', 'CLOB', G_WITH_DATA );
317 register_table('FND', 'FND_LOBS_DOCUMENT', 'CONTENT', 'LONG RAW', 'CONTENT', 'BLOB', G_WITH_DATA );
318 register_table('FND', 'FND_PLAN_TABLE', 'OTHER', 'LONG', 'OTHER', 'CLOB', G_WITH_DATA );
319 register_table('FND', 'FND_VIEWS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
320 register_table('FV', 'FV_CFS_REP_LINES', 'FOOTNOTES', 'LONG', 'FOOTNOTES', 'CLOB', G_WITH_DATA );
321 register_table('GMA', 'SY_PURG_DEF', 'SQLSTATEMENT', 'LONG', 'SQLSTATEMENT', 'CLOB', G_WITH_DATA );
322 register_table('GMD', 'GMD_REPLACE', 'QUERY', 'LONG', 'QUERY', 'CLOB', G_WITH_DATA );
323 register_table('HXC', 'HXC_DEBUG_TEXT', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
324 register_table('HXC', 'HXC_TIME_CATEGORIES', 'TIME_SQL', 'LONG', 'TIME_SQL', 'CLOB', G_WITH_DATA );
325 register_table('IBA', 'IBA_R_RULECONTEXTS', 'RULETEXT', 'LONG', 'RULETEXT', 'CLOB', G_WITH_DATA );
326 register_table('IES', 'IES_QUESTION_DATA', 'FREEFORM_LONG', 'LONG', 'FREEFORM_LONG', 'CLOB', G_WITH_DATA );
327 register_table('IGI', 'IGI_DOS_ITEMS', 'ITEM_TEXT', 'LONG', 'ITEM_TEXT', 'CLOB', G_WITH_DATA );
331 register_table('IGS', 'IGS_GE_NOTE', 'NOTE_OLE', 'LONG RAW', 'NOTE_OLE', 'BLOB', G_WITH_DATA );
328 register_table('IGI', 'IGI_EXP_POS_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
329 register_table('IGS', 'IGS_CO_DTL', 'CORD_TEXT', 'LONG', 'CORD_TEXT', 'CLOB', G_WITH_DATA );
330 register_table('IGS', 'IGS_CO_DTL_OLE', 'CORD_OLE', 'LONG RAW', 'CORD_OLE', 'BLOB', G_WITH_DATA );
332 register_table('IGS', 'IGS_PE_PERSON_IMAGE', 'PERSON_IMAGE', 'LONG RAW', 'PERSON_IMAGE', 'BLOB', G_WITH_DATA );
333 register_table('IGS', 'IGS_UC_APPLICANTS', 'PERSONAL_STATEMENT', 'LONG', 'PERSONAL_STATEMENT', 'CLOB', G_WITH_DATA );
334 register_table('IGS', 'IGS_UC_IREFRNC_INTS', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
335 register_table('IGS', 'IGS_UC_ISTMNT_INTS', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
336 register_table('IGS', 'IGS_UC_MV_IVSTMNT', 'STATEMENT', 'LONG', 'STATEMENT', 'CLOB', G_WITH_DATA );
337 register_table('IGS', 'IGS_UC_OFFER_CONDS', 'LETTER_TEXT', 'LONG', 'LETTER_TEXT', 'CLOB', G_WITH_DATA );
338 register_table('IGW', 'IGW_BUDGET_DETAILS', 'BUDGET_JUSTIFICATION', 'LONG', 'BUDGET_JUSTIFICATION', 'CLOB', G_WITH_DATA );
339 register_table('IGW', 'IGW_REPORT_BUDG_JUSTIFICATION', 'JUSTIFICATION', 'LONG', 'JUSTIFICATION', 'CLOB', G_WITH_DATA );
340 register_table('IGW', 'IGW_REPORT_ITEMIZED_BUDGET', 'EXPENDITURE_DESCRIPTION', 'LONG', 'EXPENDITURE_DESCRIPTION', 'CLOB', G_WITH_DATA );
341 register_table('INV', 'MTL_SHORT_CHK_STATEMENTS', 'SHORT_STATEMENT', 'LONG', 'SHORT_STATEMENT', 'CLOB', G_WITH_DATA );
342 register_table('JTF', 'JTF_BRM_RULES_B', 'VIEW_DEFINITION', 'LONG', 'VIEW_DEFINITION', 'CLOB', G_WITH_DATA );
343 register_table('JTF', 'JTF_R_RULECONTEXTS', 'RULETEXT', 'LONG', 'RULETEXT', 'CLOB', G_WITH_DATA );
344 register_table('JTF', 'JTF_R_RULESETS_B', 'RULESET_SER', 'LONG', 'RULESET_SER', 'CLOB', G_WITH_DATA );
345 register_table('MSC', 'MSC_ERRORS', 'RROW', 'LONG', 'RROW', 'CLOB', G_WITH_DATA );
346 register_table('N/A', 'BEN_COPY_ENTITY_RESULTS', 'INFORMATION323', 'LONG', 'INFORMATION323', 'CLOB', G_WITH_DATA );
347 register_table('N/A', 'CSF_M_LOBS_INQ', 'FILE_DATA', 'LONG RAW', 'FILE_DATA', 'BLOB', G_WITH_DATA );
348 register_table('N/A', 'CSI_XNP_MSGS_TEMP', 'MSG_TEXT', 'LONG', 'MSG_TEXT', 'CLOB', G_NO_ACTION );
349 register_table('N/A', 'CSM_CUSTOM_7_INQ', 'ATTRIBUTE30', 'LONG RAW', 'ATTRIBUTE30', 'BLOB', G_WITH_DATA );
350 register_table('N/A', 'IES_META_PROPERTY_VALUES', 'LONG_VAL', 'LONG', 'LONG_VAL', 'CLOB', G_WITH_DATA );
351 register_table('N/A', 'RG_REPORT_STANDARD_AXES_B', 'PERIOD_QUERY', 'LONG', 'PERIOD_QUERY', 'VARCHAR2', G_WITH_DATA );
352 register_table('OE', 'SO_ACTION_CLAUSES', 'WHERE_CLAUSE', 'LONG', 'WHERE_CLAUSE', 'CLOB', G_NO_ACTION );
353 register_table('OE', 'SO_EXCEPTIONS', 'MESSAGE_TEXT', 'LONG', 'MESSAGE_TEXT', 'CLOB', G_NO_ACTION );
354 register_table('OE', 'SO_HEADERS_INTERFACE_ALL', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_NO_ACTION );
355 register_table('OE', 'SO_HOLD_RELEASES', 'RELEASE_COMMENT', 'LONG', 'RELEASE_COMMENT', 'CLOB', G_NO_ACTION );
356 register_table('OE', 'SO_HOLD_SOURCES_ALL', 'HOLD_COMMENT', 'LONG', 'HOLD_COMMENT', 'CLOB', G_NO_ACTION );
357 register_table('OE', 'SO_LINE_APPROVALS', 'APPROVAL_COMMENT', 'LONG', 'APPROVAL_COMMENT', 'CLOB', G_WITH_DATA );
358 register_table('OE', 'SO_NOTES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_NO_ACTION );
359 register_table('OE', 'SO_OBJECTS', 'COMPILED_INFORMATION', 'LONG', 'COMPILED_INFORMATION', 'CLOB', G_NO_ACTION );
360 register_table('OE', 'SO_ORDER_APPROVALS', 'APPROVAL_COMMENT', 'LONG', 'APPROVAL_COMMENT', 'CLOB', G_WITH_DATA );
361 register_table('OE', 'SO_ORDER_CANCELLATIONS', 'CANCEL_COMMENT', 'LONG', 'CANCEL_COMMENT', 'CLOB', G_NO_ACTION );
362 register_table('OE', 'SO_PICKING_CANCELLATIONS', 'CANCEL_COMMENT', 'LONG', 'CANCEL_COMMENT', 'CLOB', G_NO_ACTION );
363 register_table('OE', 'SO_STANDARD_VALUE_RULE_SETS', 'COMPILED_INFORMATION', 'LONG', 'COMPILED_INFORMATION', 'CLOB', G_NO_ACTION );
364 register_table('OFA', 'FA_RX_LOV', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'VARCHAR2', G_WITH_DATA );
365 register_table('OKE', 'OKE_K_COMMUNICATIONS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
366 register_table('PAY', 'PAY_BALANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
367 register_table('PA', 'PA_RULES', 'SELECT_STATEMENT', 'LONG', 'SELECT_STATEMENT', 'CLOB', G_WITH_DATA );
368 register_table('PAY', 'PAY_CALENDARS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
369 register_table('PAY', 'PAY_CONSOLIDATION_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
370 register_table('PAY', 'PAY_CUSTOMIZED_RESTRICTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
371 register_table('PAY', 'PAY_ELEMENT_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
372 register_table('PAY', 'PAY_MONETARY_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
373 register_table('PAY', 'PAY_PAYROLL_ACTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
374 register_table('PAY', 'PAY_PROCESS_GROUP_ACTIONS', 'ARGUMENT_LIST', 'LONG', 'ARGUMENT_LIST', 'CLOB', G_WITH_DATA );
375 register_table('PAY', 'PAY_RATES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
376 register_table('PAY', 'PAY_SHADOW_FORMULAS', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
377 register_table('PAY', 'PAY_WC_FUNDS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
378 register_table('PAY', 'PAY_WCI_ACCOUNTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
379 register_table('PAY', 'PAY_WCI_OCCUPATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
380 register_table('PAY', 'PAY_WCI_RATES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
381 register_table('PAY', 'PER_PAY_PROPOSALS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
382 register_table('PER', 'BEN_BENEFIT_CLASSIFICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
383 register_table('PER', 'HR_ALL_POSITIONS_F', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
384 register_table('PER', 'HR_API_TRANSACTIONS', 'URL', 'LONG', 'URL', 'VARCHAR2', G_WITH_DATA );
388 register_table('PER', 'HR_QUESTIONNAIRES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
385 register_table('PER', 'HR_COMMENTS', 'COMMENT_TEXT', 'LONG', 'COMMENT_TEXT', 'CLOB', G_WITH_DATA );
386 register_table('PER', 'HR_PUMP_BATCH_LINES', 'PLONGVAL', 'LONG', 'PLONGVAL', 'CLOB', G_WITH_DATA );
387 register_table('PER', 'HR_QUEST_FIELDS', 'HTML_TEXT', 'LONG', 'HTML_TEXT', 'CLOB', G_WITH_DATA );
389 register_table('PER', 'HR_S_BALANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
390 register_table('PER', 'HR_S_BENEFIT_CLASSIFICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
391 register_table('PER', 'HR_S_ELEMENT_SETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
392 register_table('PER', 'HR_S_FORMULAS_F', 'FORMULA_TEXT', 'LONG', 'FORMULA_TEXT', 'CLOB', G_WITH_DATA );
393 register_table('PER', 'HR_S_MONETARY_UNITS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
394 register_table('PER', 'HR_S_QP_REPORTS', 'QP_TEXT', 'LONG', 'QP_TEXT', 'CLOB', G_WITH_DATA );
395 register_table('PER', 'HR_S_ROUTES', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_WITH_DATA );
396 register_table('PER', 'HR_TIPS', 'TEXT', 'LONG', 'TEXT', 'CLOB', G_NO_ACTION );
397 register_table('PER', 'PER_ABSENCE_ATTENDANCE_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
398 register_table('PER', 'PER_ABSENCE_ATTENDANCES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
399 register_table('PER', 'PER_ADDRESSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
400 register_table('PER', 'PER_ALL_POSITIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
401 register_table('PER', 'PER_ALL_VACANCIES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
402 register_table('PER', 'PER_APPLICATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
403 register_table('PER', 'PER_APPRAISAL_TEMPLATES', 'INSTRUCTIONS', 'LONG', 'INSTRUCTIONS', 'CLOB', G_WITH_DATA );
404 register_table('PER', 'PER_ASSESSMENT_TYPES', 'INSTRUCTIONS', 'LONG', 'INSTRUCTIONS', 'CLOB', G_WITH_DATA );
405 register_table('PER', 'PER_BOOKINGS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
406 register_table('PER', 'PER_BUDGET_VERSIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
407 register_table('PER', 'PER_BUDGETS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
408 register_table('PER', 'PER_CAREER_PATHS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
409 register_table('PER', 'PER_COBRA_COV_ENROLLMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
410 register_table('PER', 'PER_COBRA_COVERAGE_STATUSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
411 register_table('PER', 'PER_CONTACT_RELATIONSHIPS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
412 register_table('PER', 'PER_EVENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
413 register_table('PER', 'PER_GRADES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
414 register_table('PER', 'PER_IMAGES', 'IMAGE', 'LONG RAW', 'IMAGE', 'BLOB', G_WITH_DATA );
415 register_table('PER', 'PER_JOB_EVALUATIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
416 register_table('PER', 'PER_JOB_REQUIREMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
417 register_table('PER', 'PER_JOBS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
418 register_table('PER', 'PER_LETTER_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
419 register_table('PER', 'PER_ORGANIZATION_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
420 register_table('PER', 'PER_PARENT_SPINES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
421 register_table('PER', 'PER_PAY_BASES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
422 register_table('PER', 'PER_PAY_PROPOSAL_COMPONENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
423 register_table('PER', 'PER_PERIODS_OF_SERVICE', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
424 register_table('PER', 'PER_PERSON_ANALYSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
425 register_table('PER', 'PER_POSITION_STRUCTURES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
426 register_table('PER', 'PER_RECRUITMENT_ACTIVITIES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
427 register_table('PER', 'PER_SCHED_COBRA_PAYMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
428 register_table('PER', 'PER_SECONDARY_ASS_STATUSES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
429 register_table('PER', 'PER_SPECIAL_INFO_TYPES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
430 register_table('PER', 'PER_VALID_GRADES', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
431 register_table('PO', 'PO_ACCEPTANCES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_WITH_DATA );
432 register_table('PO', 'PO_NOTES', 'NOTE', 'LONG', 'NOTE', 'CLOB', G_WITH_DATA );
433 register_table('PQH', 'PQH_COPY_ENTITY_RESULTS', 'LONG_ATTRIBUTE1', 'LONG', 'LONG_ATTRIBUTE1', 'CLOB', G_WITH_DATA );
434 register_table('PQH', 'PQH_POSITION_TRANSACTIONS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
435 register_table('PQH', 'PQH_PTX_SHADOW', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
436 register_table('PQH', 'PQH_TJR_SHADOW', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
437 register_table('PQH', 'PQH_TXN_JOB_REQUIREMENTS', 'COMMENTS', 'LONG', 'COMMENTS', 'CLOB', G_WITH_DATA );
438 register_table('RG', 'RG_REPORT_AXIS_SETS', 'COLUMN_SET_HEADER', 'LONG', 'COLUMN_SET_HEADER', 'VARCHAR2', G_WITH_DATA );
439 register_table('RG', 'RG_REPORT_STANDARD_AXES', 'PERIOD_QUERY', 'LONG', 'PERIOD_QUERY', 'VARCHAR2', G_WITH_DATA );
440 register_table('RLA', 'RLA_DEMAND_EXCEPTIONS', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_NO_ACTION );
441 register_table('RLM', 'RLM_DEMAND_EXCEPTIONS', 'REPORT_SUMMARY', 'LONG', 'REPORT_SUMMARY', 'CLOB', G_WITH_DATA );
442 register_table('WMS', 'WMS_LABEL_REQUESTS_HIST', 'LABEL_CONTENT', 'LONG', 'LABEL_CONTENT', 'CLOB', G_WITH_DATA );
443 register_table('WSH', 'WSH_SAVED_QUERIES_B', 'PSEUDO_QUERY', 'LONG', 'PSEUDO_QUERY', 'CLOB', G_WITH_DATA );
444 COMMIT ;
445
446 g_Specific_Table := NULL ;
447 g_Specific_Product := NULL ;
451
448 g_Specific_Schema := NULL ;
449
450 END initialize_process;
452 --
453 -- The following procedure alters the table to add the new column.
454 -- with the new data type.
455 -- For each of the tables from AD_LONG_COLUMN_CONVERSIONS
456 -- this procedure has to be called repeatedly.
457 --
458 PROCEDURE add_new_column(p_Schema IN VARCHAR2 ,
459 p_Table_Name IN VARCHAR2 ,
460 p_Old_Column_Name IN VARCHAR2 ,
461 p_New_Column_Name IN VARCHAR2 ,
462 p_New_Data_Type IN VARCHAR2 ,
463 p_Curr_Status IN VARCHAR2 ,
464 p_Action IN VARCHAR2 )
465 IS
466 l_stmt VARCHAR2 (500);
467 l_New_Column_Name VARCHAR2 (50);
468 l_New_Status VARCHAR2 (30);
469 l_New_Data_Type VARCHAR2 (30);
470
471 BEGIN -- procedure
472 --
473 -- The new column has to be added for actions with_data and
474 -- without_data only.
475 -- And, the new columns will be added only when the tables are in
476 -- the initialized status. The below if condition checks both.
477 --
478 IF ( p_Action IN (G_WITH_DATA, G_WITHOUT_DATA) AND
479 p_Curr_Status = G_INITIALIZED_STATUS ) THEN
480
481 l_New_Column_Name := p_New_Column_Name;
482 --
483 -- If the new column name is same as the old column name
484 -- it indicates that the new column has to be added with a
485 -- different name.
486 --
487
488 IF p_New_Data_Type = 'VARCHAR2' THEN
489 l_New_Data_Type := 'VARCHAR2(4000)';
490 ELSE
491 l_New_Data_Type := p_New_Data_Type;
492 END IF ;
493 l_stmt := 'ALTER TABLE '||p_schema||'.'||p_Table_Name||
494 ' ADD ( '||l_New_Column_Name||' '||l_New_Data_Type||')';
495
496 BEGIN -- inner block
497 EXECUTE IMMEDIATE l_stmt;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 IF (SQLCODE = -1430) THEN
502 NULL ;
503 ELSE
504 -- DBMS_OUTPUT.PUT_LINE('the exeception is here only'); --remove
505 RAISE ;
506 END IF ;
507 END ; -- inner block
508 --
509 -- If the action is without_data it means that it is enough to add
510 -- the new column. No need to migrate the data. So for such cases
511 -- the process completes here. Hence for them status is updated as
512 -- COMPLETED, so that it wont be picked up in the next stages again.
513 --
514 IF (p_Action = G_WITHOUT_DATA ) THEN
515 l_New_Status := G_DROP_OLD_COLUMN_STATUS;
516 ELSE
517 l_New_Status := G_ADD_NEW_COLUMN_STATUS;
518 END IF ;
519 update_table_status(p_Schema,
520 p_Table_Name,
521 p_Old_Column_Name,
522 l_New_Status );
523
524 END IF ; -- end if p_action
525 END add_new_column;
526
527 --
528 -- This function creates the triggers those are needed to handle
529 -- online updates to the long data
530 --
531 PROCEDURE create_transform_triggers(
532 p_Schema IN VARCHAR2 ,
533 p_Table_Name IN VARCHAR2 ,
534 p_Old_Column_Name IN VARCHAR2 ,
535 p_New_Column_Name IN VARCHAR2 ,
536 p_New_Data_Type IN VARCHAR2 )
537
538 IS
539 l_stmt VARCHAR2 (2000);
540 l_trig_name VARCHAR2 (30);
541 l_lob_value_col VARCHAR2 (30);
542 BEGIN
543 --
544 -- Depending on the target data type needed, assign the column name
545 -- in the temporary table.
546 --
547 IF (p_New_Data_Type = 'CLOB') THEN
548 l_lob_value_col := 'clob_value';
549 ELSIF (p_New_Data_Type = 'BLOB') THEN
550 l_lob_value_col := 'blob_value';
551 ELSIF (p_New_Data_Type = 'VARCHAR2') THEN
552 l_lob_value_col := 'clob_value';
553 END IF ;
554
555 --
556 -- create a ROW level trigger that inserts rowid for changed or newly
557 -- added rows into a temporary table. Trigger is created on the
558 -- LONG column.
559 --
560
561 l_trig_name := substr(p_Table_Name, 1, 24)||'_$R2U1';
562
563 l_stmt := ' CREATE OR REPLACE TRIGGER '||l_trig_name||
564 ' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
565 ' ON '||p_Schema||'.'||p_Table_Name||
566 ' FOR EACH ROW'||
567 ' BEGIN'||
568 ' INSERT INTO AD_LONG_CONV_TEMP(table_name, apps_rowid) '||
569 ' VALUES ('''||p_Table_Name||''', :new.rowid);'||
570 ' END;';
571
572 EXECUTE IMMEDIATE l_stmt;
573 -- dbms_output.put_line('drop trigger '||l_trig_name ||chr(10)||'/');
574 -- dbms_output.put_line('the first trigger '); -- remove
575 -- dbms_output.put_line(substr(l_stmt,1,220)); -- remove
576 -- dbms_output.put_line(substr(l_stmt,220,200)); -- remove
577
578 --
579 -- create a STATEMENT level trigger that updates the main table
580 -- for the changed rows
581 --
582
583 l_trig_name := substr(p_Table_Name, 1, 24)||'_$R2U2';
584
585
586 l_stmt := ' CREATE OR REPLACE TRIGGER '||l_trig_name||
587 ' AFTER INSERT OR UPDATE OF '||p_Old_Column_Name||
588 ' ON '||p_Schema||'.'||p_Table_Name||
589 ''||
590 ' DECLARE '||
591 ' CURSOR c_tmp IS '||
595 ''||
592 ' SELECT apps_rowid, '||l_lob_value_col||
593 ' FROM AD_LONG_CONV_TEMP2'||
594 ' WHERE table_name = '''||p_Table_Name||''';'||
596 ' BEGIN'||
597 ''||
598 ' INSERT INTO AD_LONG_CONV_TEMP2('||
599 ' table_name, apps_rowid, '||l_lob_value_col ||' ) '||
600 ' SELECT '''||p_Table_Name||''', t.apps_rowid, '||
601 ' to_lob(f.'||p_Old_Column_Name ||')'||
602 ' FROM AD_LONG_CONV_TEMP t, '||p_Schema||
603 '.'||p_Table_Name||' f '||
604 ' WHERE f.rowid = t.apps_rowid;'||
605 ''||
606 ' FOR c_rec in c_tmp LOOP '||
607 ' UPDATE '||p_Schema||'.'||p_Table_Name ||
608 ' SET '||p_New_Column_Name ||' = c_rec.'||l_lob_value_col||
609 ' WHERE rowid = c_rec.apps_rowid;'||
610 ' END LOOP;'||
611 ' END;';
612
613 -- dbms_output.put_line('drop trigger '||l_trig_name ||chr(10)||'/');
614 -- remove
615 -- dbms_output.put_line(substr(l_stmt,1,200));
616 -- dbms_output.put_line(substr(l_stmt,201,200));
617 -- dbms_output.put_line(substr(l_stmt,401,200));
618 -- dbms_output.put_line(substr(l_stmt,601,200));
619 EXECUTE IMMEDIATE l_stmt;
620 -- dbms_output.put_line('after creation of the second trigger ');
621
622 --
623 -- Update the table status to G_ADD_TRIGGER_STATUS
624 --
625 update_table_status( p_Schema ,
626 p_Table_Name ,
627 p_Old_Column_Name ,
628 G_ADD_TRIGGER_STATUS);
629
630 END create_transform_triggers;
631
632 PROCEDURE update_new_data(p_Schema IN VARCHAR2 ,
633 p_Old_Table_Name IN VARCHAR2 ,
634 p_Old_Column_Name IN VARCHAR2 ,
635 p_Old_Data_Type IN VARCHAR2 ,
636 p_New_Column_Name IN VARCHAR2 ,
637 p_Batch_Size IN NUMBER DEFAULT 1000)
638 IS
639 l_stmt VARCHAR2 (10000);
640 l_lob_value_col_name VARCHAR2 (30);
641 BEGIN
642
643 IF (p_Old_Data_Type = 'LONG') THEN
644 l_lob_value_col_name := 'clob_value';
645 ELSE
646 l_lob_value_col_name := 'blob_value';
647 END IF ;
648
649 l_stmt := ' declare '||
650 ' cursor c_tab is '||
651 ' select rowid'||
652 ' from '||p_Schema||'.'||p_Old_Table_Name||
653 ' where '||p_Old_Column_Name||' is not null '||
654 ' and '||p_New_Column_Name||' is null; '||
655 ''||
656 ' cursor c_tab2 is'||
657 ' select apps_rowid, '||l_lob_value_col_name||
658 ' from ad_long_conv_temp2;'||
659 ''||
660 ' rowtab dbms_sql.urowid_table;'||
661 ''||
662 ' begin'||
663 ' open c_tab;'||
664 ''||
665 ' loop'||
666 ''||
667 ' fetch c_tab bulk collect into rowtab limit '||
668 p_Batch_Size||';'||
669 ''||
670 ' exit when rowtab.count = 0;'||
671 ''||
672 ' forall i in rowtab.FIRST..rowtab.LAST'||
673 ' insert into ad_long_conv_temp2(table_name,'||
674 ' apps_rowid, '||l_lob_value_col_name||')'||
675 ' select '''||p_Old_Table_Name||''','||
676 ' rowid, to_lob('||p_Old_Column_Name||')'||
677 ' from '||p_Schema||'.'||p_Old_Table_Name||
678 ' where rowid = rowtab(i);'||
679 ''||
680 ' for c_rec2 in c_tab2 loop'||
681 ' update '||p_Schema||'.'||p_Old_Table_Name||
682 ' set '||p_New_Column_Name||' = c_rec2.'||
683 l_lob_value_col_name||
684 ' where rowid = c_rec2.apps_rowid;'||
685 ' end loop;'||
686 ''||
687 ' commit;'||
688 ' end loop;'||
689 ''||
690 ' close c_tab;'||
691 ' end;';
692
693 --remove
694 --dbms_output.put_line(substr(l_stmt,1,200));
695 --dbms_output.put_line(substr(l_stmt,201,200));
696 --dbms_output.put_line(substr(l_stmt,401,200));
697 --dbms_output.put_line(substr(l_stmt,601,200));
698
699 EXECUTE IMMEDIATE l_stmt;
700
701 --
702 -- Update the table status to rows processed.
703 --
704 update_table_status( p_Schema,
705 p_Old_Table_Name,
706 p_Old_Column_Name,
707 G_UPDATE_ROWS_STATUS);
708 END update_new_data;
709
710 --
711 -- This function is used to get the length of the LONG data
712 --
713 FUNCTION get_long_length( p_Table_Name IN VARCHAR2,
714 p_Long_Column_Name IN VARCHAR2 ,
715 p_Rowid IN VARCHAR2 )
716 RETURN NUMBER
717 IS
718 l_cursor INTEGER;
719 l_ignore INTEGER;
720 l_stmt VARCHAR2(100);
721 out_val VARCHAR2(1001);
722 out_length INTEGER;
723 l_row_length NUMBER ;
724 num_bytes INTEGER := 1000; -- length in bytes of the chunk of data to be read.
725 -- value to be selected
726 l_offset INTEGER; -- the byte position in the LONG column at which
727
728 BEGIN
732 --
729 l_stmt := 'SELECT '||p_Long_Column_Name||' FROM '||p_Table_Name||' WHERE ROWID = '''||p_Rowid||'''';
730 l_cursor := DBMS_SQL.OPEN_CURSOR;
731 DBMS_SQL.PARSE(l_cursor, l_stmt, DBMS_SQL.NATIVE);
733 --Define the LONG column.
734 --
735 DBMS_SQL.DEFINE_COLUMN_LONG(l_cursor, 1);
736 --
737 -- Execute the query.
738 --
739 l_ignore := DBMS_SQL.EXECUTE(l_cursor);
740
741 IF DBMS_SQL.FETCH_ROWS(l_cursor) > 0 THEN
742 l_offset := 0;
743 l_row_length := 0;
744 --
745 -- Get the value of the LONG column piece by piece. Here a loop
746 -- is used to get the entire column. The loop exits when there
747 -- is no more data.
748 --
749 LOOP
750 --
751 -- Get the value of a portion of the LONG column.
752 --
753 DBMS_SQL.COLUMN_VALUE_LONG(l_cursor, 1, num_bytes, l_offset, out_val, out_length);
754 IF out_length <> 0 THEN
755 l_offset := l_offset + num_bytes;
756 l_row_length := l_row_length + out_length;
757 ELSE
758 EXIT;
759 END IF;
760 IF out_length < num_bytes THEN
761 EXIT;
762 END IF;
763 END LOOP;
764 ELSE
765 l_row_length := 0;
766 END IF;
767 DBMS_SQL.CLOSE_CURSOR(l_cursor);
768 RETURN l_row_length ;
769 EXCEPTION
770 WHEN OTHERS THEN
771 -- DBMS_OUTPUT.PUT_LINE ('Errors in function get_long_length');
772 -- DBMS_OUTPUT.PUT_LINE(sqlerrm);
773 IF DBMS_SQL.is_open(l_cursor) THEN
774 DBMS_SQL.CLOSE_CURSOR(l_cursor);
775 END IF;
776 END;
777
778 PROCEDURE write_long_rep
779 IS
780 BEGIN
781 write_long_rep('NA');
782 END ;
783
784 PROCEDURE write_long_rep( p_Path VARCHAR2)
785 IS
786
787 TYPE cur_type IS REF CURSOR ;
788 l_cursor cur_type;
789
790 fp utl_file.file_type;
791 l_Row_4000_Count NUMBER ;
792 l_str VARCHAR2 (2000);
793 l_query VARCHAR2 (400);
794 l_number NUMBER ;
795 l_Rowid VARCHAR2 (30);
796 l_File_Name VARCHAR2 (100);
797 l_Path VARCHAR2 (200);
798 l_pr NUMBER := 0;
799 l_Sl_No NUMBER := 0;
800
801 l_TableNames_Tbl TableNames_Tbl_Type ;
802 l_To_DataType_Tbl To_DataType_Tbl_Type;
803
804 CURSOR c1(p_Table_Name VARCHAR2 ) IS
805 SELECT table_name, column_name, a.owner, data_type, bytes/1024/1024 t_size
806 FROM dba_tab_columns a, dba_segments b
807 WHERE data_type IN ('LONG', 'LONG RAW')
808 AND table_name = p_Table_name
809 AND segment_name = table_name
810 AND a.owner NOT IN ('SYS','SYSTEM','OUTLN')
811 AND b.owner = a.owner ;
812
813 PROCEDURE print_data(p_String VARCHAR2 )
814 IS
815 BEGIN
816 IF l_pr = 0 THEN
817 DBMS_OUTPUT.PUT_LINE(l_str);
818 ELSE
819 utl_file.put_line(fp,l_str);
820 END IF ;
821 END print_data;
822 BEGIN
823 l_Path := p_Path;
824 IF INSTR (l_Path,'/') = 0 THEN
825 IF INSTR (l_Path,'\') = 0 THEN
826 l_Path := UPPER (p_Path);
827 END IF ;
828 END IF ;
829 IF l_Path = 'NA' THEN
830 l_pr := 0;
831 ELSE
832 l_pr := 1;
833 END IF ;
834
835 -- initialize_process;
836 -- Load the information from the control table
837 SELECT table_name, NEW_DATA_TYPE
838 BULK COLLECT
839 INTO l_TableNames_Tbl, l_To_DataType_Tbl
840 FROM ad_long_column_conversions;
841
842 IF l_TableNames_Tbl.COUNT = 0 THEN
843 RAISE_APPLICATION_ERROR (-20001,
844 ' The tables are not Initialized.'||
845 ' Please use Ad_LongToLob_Pkg.initialize_process to'||
846 ' initialize the tables for generating report. ');
847 END IF ;
848
849 BEGIN -- begin for the main block of actions
850 l_File_Name := 'long_report.'||to_char(sysdate,'DD-MON-YY.HH24:MI:SS')||'.txt';
851 IF (l_pr = 1 ) THEN
852 fp := utl_file.fopen(l_Path,l_File_Name,'w');
853 END IF ;
854
855 l_str := 'Sl.No '||lpad('Table Name',28)||'*'|| lpad('Column Name',29)||'*'|| lpad('Owner',11)||'*'||
856 lpad('Data Type',14)||'*'|| lpad('Table Size',14)||'*'|| lpad('Total Rows',14) ||'*'||
857 lpad('Not Null Rows',20)||'*'||lpad('Rows > 4000 ',20);
858 print_data(l_str);
859 l_str := lpad('-',161,'-');
860 print_data(l_str);
861
862 FOR i IN l_TableNames_Tbl.FIRST .. l_TableNames_Tbl.LAST LOOP
863 FOR rec IN c1(l_TableNames_Tbl(i)) LOOP
864 l_str := NULL ;
865 l_Row_4000_Count := 0;
866 l_Sl_No := l_Sl_No + 1;
867 l_str := rpad(l_Sl_No,6)||'-'||lpad(rec.table_name,29)||lpad(rec.column_name,30)|| lpad(rec.owner,12)||
868 lpad(rec.data_type,15)|| lpad(rec.t_size,15);
869 l_query := null;
870 l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name;
871 execute immediate l_query into l_number;
872 l_str := l_str || lpad(l_number,15);
873 l_query := null;
874 l_query := 'select count(*) from '||rec.owner||'.'||rec.table_name||' where '||rec.column_name||' is not null';
875 execute immediate l_query into l_number;
876 l_str := l_str || lpad(l_number,20);
877 IF l_To_DataType_Tbl(i) = 'VARCHAR2' THEN
878 IF l_cursor%ISOPEN THEN
879 CLOSE l_cursor;
880 END IF ; -- if %is open
881 OPEN l_cursor FOR 'SELECT ROWID FROM '||rec.table_name;
882 LOOP
883 FETCH l_cursor INTO l_Rowid;
887 l_Rowid) > 4000 THEN
884 EXIT WHEN l_cursor%NOTFOUND ;
885 IF get_long_length(rec.table_name,
886 rec.column_name,
888 l_Row_4000_Count := l_Row_4000_Count +1;
889 END IF ;
890 END LOOP ;
891 END IF ;
892 l_str := l_str || lpad(l_Row_4000_Count ,20);
893 print_data(l_str);
894 END LOOP ;
895 END LOOP ;
896 IF (l_pr = 1 ) THEN
897 utl_file.fclose(fp);
898 END IF ;
899 END; -- end for the main block of actions
900 END write_long_rep;
901
902 --
903 -- This procedure can be used to defer the processing of a table.
904 -- It can be called at any stage of processing of the table.
905 -- I.e. Before starting the Long To LOB conversion, after adding the
906 -- new column, after creating the triggers or after converting the data
907 --
908 PROCEDURE defer_table( p_Schema IN VARCHAR2 ,
909 p_Table_Name IN VARCHAR2 )
910 IS
911 l_Current_Status ad_long_column_conversions.status%TYPE ;
912 l_Count NUMBER(3) ;
913 BEGIN
914 --
915 -- Since this procedure can be called even before starting any of the
916 -- processing steps,
917 -- the follwing select checks whether the table is initialized.
918 -- If not the initialize_process is called to initialize the tables.
919 --
920 SELECT COUNT(*)
921 INTO l_Count
922 FROM ad_long_column_conversions
923 WHERE schema_name = p_Schema
924 AND table_name = p_Table_Name ;
925
926 --
927 -- The table is not initialized, so call initialize_process
928 --
929 IF l_Count = 0 THEN
930 -- initialize_process;
931 -- Instead raise an error saying that the table is not initialized
932 raise_application_error(-20001,
933 ' The table is not Initialized.'||
934 ' Please use Ad_LongToLob_Pkg.initialize_process to'||
935 ' initialize the table for processing ');
936 END IF ;
937
938 --
939 -- Lock the row for the table to make sure that status of the
940 -- table is not being updated by any other session.
941 -- If the table name being passed is a wrong one, this select
942 -- comes out saying the exception.
943 --
944 SELECT status
945 INTO l_Current_Status
946 FROM ad_long_column_conversions
947 WHERE schema_name = p_Schema
948 AND table_name = p_Table_Name
949 FOR UPDATE NOWAIT ;
950
951 --
952 -- Now update the status to indicate that the table is deferred.
953 -- The status will be 'DEFFERRED_'||old_status.
954 --
955 UPDATE ad_long_column_conversions
956 SET status = G_DEFERRED_STATUS||'_'||status
957 WHERE schema_name = p_Schema
958 AND table_name = p_Table_Name;
959
960 -- commit the transaction
961 COMMIT ;
962 EXCEPTION
963 WHEN NO_DATA_FOUND THEN
964 RAISE_APPLICATION_ERROR(-20002, 'The table '||p_Schema||'.'||p_Table_Name||
965 ' is not initialized for Long to Lob conversion. ');
966 END defer_table;
967
968 --
969 -- This procedure is used to re-enable the deferred tables
970 --
971 PROCEDURE re_enable_table(p_Schema IN VARCHAR2 ,
972 p_Table_Name IN VARCHAR2 )
973 IS
974 l_Prev_Status ad_long_column_conversions.status%TYPE ;
975 BEGIN
976 --
977 -- Lock the entry in ad_long_column_conversions corresponding to the
978 -- table being re-enabled.
979 --
980 SELECT LTRIM (status,G_DEFERRED_STATUS||'_')
981 INTO l_Prev_Status
982 FROM ad_long_column_conversions
983 WHERE schema_name = p_Schema
984 AND table_name = p_Table_Name
985 AND status LIKE G_DEFERRED_STATUS||'_%'
986 FOR UPDATE NOWAIT ;
987
988 --
989 -- Update status of the table to the prior status.
990 --
991 UPDATE ad_long_column_conversions
992 SET status = l_Prev_Status
993 WHERE schema_name = p_Schema
994 AND table_name = p_Table_Name;
995
996 -- commit the transaction
997 COMMIT ;
998 EXCEPTION
999 WHEN NO_DATA_FOUND THEN
1000 RAISE_APPLICATION_ERROR(-20002, 'The table '||p_Schema||'.'||p_Table_Name||
1001 ' is not a deferred table. ');
1002 END re_enable_table;
1003
1004 END Ad_LongToLob_Pkg;