APPS.WF_INSTALL dependencies on UTL_FILE

Line 25: i_file_handle utl_file.file_type;

21: v_initrans varchar2(30);
22: v_pctfree varchar2(30);
23: v_pctused varchar2(30);
24: v_degree varchar2(30);
25: i_file_handle utl_file.file_type;
26:
27: begin
28: begin
29: if (partition) then

Line 30: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);

26:
27: begin
28: begin
29: if (partition) then
30: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);
31: SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT,
32: PCT_INCREASE,PARTITIONED
33: INTO v_tablespace_name, v_initial, v_next,
34: v_pctinc, v_partitioned

Line 47: i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);

43: else
44: v_next := greatest(v_next,1048576);
45: end if;
46: else
47: i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);
48: SELECT NVL(UT.TABLESPACE_NAME,UPT.DEF_TABLESPACE_NAME),
49: TO_CHAR(GREATEST(NVL(UT.INITIAL_EXTENT,
50: TO_NUMBER(UPT.DEF_INITIAL_EXTENT)),65536)),
51: TO_CHAR(GREATEST(NVL(UT.NEXT_EXTENT,

Line 73: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

69:
70: end if;
71: exception
72: when NO_DATA_FOUND then
73: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
74: utl_file.put_line(i_file_handle,'-- Table '||tblname||
75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

Line 74: utl_file.put_line(i_file_handle,'-- Table '||tblname||

70: end if;
71: exception
72: when NO_DATA_FOUND then
73: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
74: utl_file.put_line(i_file_handle,'-- Table '||tblname||
75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78: utl_file.fflush(i_file_handle);

Line 76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

72: when NO_DATA_FOUND then
73: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
74: utl_file.put_line(i_file_handle,'-- Table '||tblname||
75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78: utl_file.fflush(i_file_handle);
79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);

Line 77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

73: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
74: utl_file.put_line(i_file_handle,'-- Table '||tblname||
75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78: utl_file.fflush(i_file_handle);
79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);
81: when OTHERS then

Line 78: utl_file.fflush(i_file_handle);

74: utl_file.put_line(i_file_handle,'-- Table '||tblname||
75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78: utl_file.fflush(i_file_handle);
79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);
81: when OTHERS then
82: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

Line 79: utl_file.fclose(i_file_handle);

75: ' does not exist.');
76: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78: utl_file.fflush(i_file_handle);
79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);
81: when OTHERS then
82: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
83: utl_file.fclose(i_file_handle);

Line 82: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

78: utl_file.fflush(i_file_handle);
79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);
81: when OTHERS then
82: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
83: utl_file.fclose(i_file_handle);
84: raise;
85: end;
86:

Line 83: utl_file.fclose(i_file_handle);

79: utl_file.fclose(i_file_handle);
80: raise_application_error(-20001,'Missing table '||tblname);
81: when OTHERS then
82: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
83: utl_file.fclose(i_file_handle);
84: raise;
85: end;
86:
87: if (partition) then

Line 90: utl_file.put_line(i_file_handle,'--Table '||tblname||

86:
87: if (partition) then
88: if (v_partitioned = 'YES') then
89: -- already partitioned. Just exit.
90: utl_file.put_line(i_file_handle,'--Table '||tblname||
91: ' already partitioned .');
92: utl_file.fclose(i_file_handle);
93: modified := FALSE;
94: return;

Line 92: utl_file.fclose(i_file_handle);

88: if (v_partitioned = 'YES') then
89: -- already partitioned. Just exit.
90: utl_file.put_line(i_file_handle,'--Table '||tblname||
91: ' already partitioned .');
92: utl_file.fclose(i_file_handle);
93: modified := FALSE;
94: return;
95: end if;
96:

Line 112: utl_file.put_line(i_file_handle,'--Name conflict.Please first drop '||

108: SELECT 1
109: FROM USER_OBJECTS
110: WHERE OBJECT_NAME = v_new_table
111: );
112: utl_file.put_line(i_file_handle,'--Name conflict.Please first drop '||
113: v_new_table);
114: utl_file.fclose(i_file_handle);
115: raise_application_error(-20002, v_new_table||' already exists.');
116: exception

Line 114: utl_file.fclose(i_file_handle);

110: WHERE OBJECT_NAME = v_new_table
111: );
112: utl_file.put_line(i_file_handle,'--Name conflict.Please first drop '||
113: v_new_table);
114: utl_file.fclose(i_file_handle);
115: raise_application_error(-20002, v_new_table||' already exists.');
116: exception
117: when NO_DATA_FOUND then
118: null;

Line 120: utl_file.put_line(i_file_handle,'--Error while checking the new table name.');

116: exception
117: when NO_DATA_FOUND then
118: null;
119: when OTHERS then
120: utl_file.put_line(i_file_handle,'--Error while checking the new table name.');
121: utl_file.fclose(i_file_handle);
122: raise;
123: end;
124:

Line 121: utl_file.fclose(i_file_handle);

117: when NO_DATA_FOUND then
118: null;
119: when OTHERS then
120: utl_file.put_line(i_file_handle,'--Error while checking the new table name.');
121: utl_file.fclose(i_file_handle);
122: raise;
123: end;
124:
125: --v_initial := 40K; -- force it to 40k

Line 129: utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);

125: --v_initial := 40K; -- force it to 40k
126: --v_next := 1M; --force to 1MB
127:
128:
129: utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);
130: v_sql :=' create table %s'||'\n'||
131: ' pctfree 10'||'\n'||
132: ' pctused 80'||'\n'||
133: ' initrans 10'||'\n'||

Line 224: utl_file.putf(i_file_handle,v_sql,v_new_table,v_tablespace_name,

220: ' as select * from %s ; \n';
221:
222:
223: begin
224: utl_file.putf(i_file_handle,v_sql,v_new_table,v_tablespace_name,
225: to_char(v_next),to_char(v_pctinc),tblname);
226: utl_file.fflush(i_file_handle);
227: exception
228: when utl_file.WRITE_ERROR then

Line 226: utl_file.fflush(i_file_handle);

222:
223: begin
224: utl_file.putf(i_file_handle,v_sql,v_new_table,v_tablespace_name,
225: to_char(v_next),to_char(v_pctinc),tblname);
226: utl_file.fflush(i_file_handle);
227: exception
228: when utl_file.WRITE_ERROR then
229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);

Line 228: when utl_file.WRITE_ERROR then

224: utl_file.putf(i_file_handle,v_sql,v_new_table,v_tablespace_name,
225: to_char(v_next),to_char(v_pctinc),tblname);
226: utl_file.fflush(i_file_handle);
227: exception
228: when utl_file.WRITE_ERROR then
229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then

Line 229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');

225: to_char(v_next),to_char(v_pctinc),tblname);
226: utl_file.fflush(i_file_handle);
227: exception
228: when utl_file.WRITE_ERROR then
229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then
233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');

Line 230: utl_file.fclose(i_file_handle);

226: utl_file.fflush(i_file_handle);
227: exception
228: when utl_file.WRITE_ERROR then
229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then
233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234: utl_file.fclose(i_file_handle);

Line 232: when utl_file.INTERNAL_ERROR then

228: when utl_file.WRITE_ERROR then
229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then
233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234: utl_file.fclose(i_file_handle);
235: raise;
236: when OTHERS then

Line 233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');

229: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then
233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234: utl_file.fclose(i_file_handle);
235: raise;
236: when OTHERS then
237: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 234: utl_file.fclose(i_file_handle);

230: utl_file.fclose(i_file_handle);
231: raise;
232: when utl_file.INTERNAL_ERROR then
233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234: utl_file.fclose(i_file_handle);
235: raise;
236: when OTHERS then
237: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
238: utl_file.fclose(i_file_handle);

Line 237: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

233: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234: utl_file.fclose(i_file_handle);
235: raise;
236: when OTHERS then
237: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
238: utl_file.fclose(i_file_handle);
239: raise;
240: end;
241:

Line 238: utl_file.fclose(i_file_handle);

234: utl_file.fclose(i_file_handle);
235: raise;
236: when OTHERS then
237: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
238: utl_file.fclose(i_file_handle);
239: raise;
240: end;
241:
242: utl_file.put_line(i_file_handle,'--Changing new table '||

Line 242: utl_file.put_line(i_file_handle,'--Changing new table '||

238: utl_file.fclose(i_file_handle);
239: raise;
240: end;
241:
242: utl_file.put_line(i_file_handle,'--Changing new table '||
243: v_new_table||' to noparalle,logging');
244: v_sql :='alter table %s'||'\n'||
245: 'noparallel '||'\n'||
246: 'logging;'||'\n';

Line 248: utl_file.putf(i_file_handle,v_sql,v_new_table);

244: v_sql :='alter table %s'||'\n'||
245: 'noparallel '||'\n'||
246: 'logging;'||'\n';
247: begin
248: utl_file.putf(i_file_handle,v_sql,v_new_table);
249: utl_file.fflush(i_file_handle);
250: exception
251: when OTHERS then
252: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 249: utl_file.fflush(i_file_handle);

245: 'noparallel '||'\n'||
246: 'logging;'||'\n';
247: begin
248: utl_file.putf(i_file_handle,v_sql,v_new_table);
249: utl_file.fflush(i_file_handle);
250: exception
251: when OTHERS then
252: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
253: raise;

Line 252: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

248: utl_file.putf(i_file_handle,v_sql,v_new_table);
249: utl_file.fflush(i_file_handle);
250: exception
251: when OTHERS then
252: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
253: raise;
254: end;
255: -- rename orig table to old table
256: v_old_table := substr('WFO_'||tblname, 1, 30);

Line 259: utl_file.put_line(i_file_handle,'--Execute the following statement :');

255: -- rename orig table to old table
256: v_old_table := substr('WFO_'||tblname, 1, 30);
257: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
258: begin
259: utl_file.put_line(i_file_handle,'--Execute the following statement :');
260: utl_file.put_line(i_file_handle,v_sql);
261: utl_file.putf(i_file_handle,' '||'\n');
262: exception
263: when OTHERS then

Line 260: utl_file.put_line(i_file_handle,v_sql);

256: v_old_table := substr('WFO_'||tblname, 1, 30);
257: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
258: begin
259: utl_file.put_line(i_file_handle,'--Execute the following statement :');
260: utl_file.put_line(i_file_handle,v_sql);
261: utl_file.putf(i_file_handle,' '||'\n');
262: exception
263: when OTHERS then
264: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));

Line 261: utl_file.putf(i_file_handle,' '||'\n');

257: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
258: begin
259: utl_file.put_line(i_file_handle,'--Execute the following statement :');
260: utl_file.put_line(i_file_handle,v_sql);
261: utl_file.putf(i_file_handle,' '||'\n');
262: exception
263: when OTHERS then
264: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
265: utl_file.fclose(i_file_handle);

Line 264: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));

260: utl_file.put_line(i_file_handle,v_sql);
261: utl_file.putf(i_file_handle,' '||'\n');
262: exception
263: when OTHERS then
264: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
265: utl_file.fclose(i_file_handle);
266: raise;
267: end;
268:

Line 265: utl_file.fclose(i_file_handle);

261: utl_file.putf(i_file_handle,' '||'\n');
262: exception
263: when OTHERS then
264: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
265: utl_file.fclose(i_file_handle);
266: raise;
267: end;
268:
269: -- rename new table to orig table

Line 272: utl_file.put_line(i_file_handle,'--Execute the following statement :');

268:
269: -- rename new table to orig table
270: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
271: begin
272: utl_file.put_line(i_file_handle,'--Execute the following statement :');
273: utl_file.put_line(i_file_handle,v_sql);
274: utl_file.putf(i_file_handle,' '||'\n');
275: exception
276: when OTHERS then

Line 273: utl_file.put_line(i_file_handle,v_sql);

269: -- rename new table to orig table
270: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
271: begin
272: utl_file.put_line(i_file_handle,'--Execute the following statement :');
273: utl_file.put_line(i_file_handle,v_sql);
274: utl_file.putf(i_file_handle,' '||'\n');
275: exception
276: when OTHERS then
277: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));

Line 274: utl_file.putf(i_file_handle,' '||'\n');

270: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
271: begin
272: utl_file.put_line(i_file_handle,'--Execute the following statement :');
273: utl_file.put_line(i_file_handle,v_sql);
274: utl_file.putf(i_file_handle,' '||'\n');
275: exception
276: when OTHERS then
277: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
278: utl_file.fclose(i_file_handle);

Line 277: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));

273: utl_file.put_line(i_file_handle,v_sql);
274: utl_file.putf(i_file_handle,' '||'\n');
275: exception
276: when OTHERS then
277: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
278: utl_file.fclose(i_file_handle);
279: raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,3000));
280: end;
281: else

Line 278: utl_file.fclose(i_file_handle);

274: utl_file.putf(i_file_handle,' '||'\n');
275: exception
276: when OTHERS then
277: utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
278: utl_file.fclose(i_file_handle);
279: raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,3000));
280: end;
281: else
282: --If the table is already unpartitioned

Line 285: utl_file.put_line(i_file_handle,'--Table '||tblname||

281: else
282: --If the table is already unpartitioned
283: if (v_partitioned = 'NO') then
284: -- already unpartitioned. Just exit.
285: utl_file.put_line(i_file_handle,'--Table '||tblname||
286: ' not partitioned .');
287: utl_file.fclose(i_file_handle);
288: modified := FALSE;
289: return;

Line 287: utl_file.fclose(i_file_handle);

283: if (v_partitioned = 'NO') then
284: -- already unpartitioned. Just exit.
285: utl_file.put_line(i_file_handle,'--Table '||tblname||
286: ' not partitioned .');
287: utl_file.fclose(i_file_handle);
288: modified := FALSE;
289: return;
290: end if;
291:

Line 300: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

296: FROM V$PARAMETER
297: WHERE NAME = 'cpu_count';
298: exception
299: when NO_DATA_FOUND then
300: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
301: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304: raise_application_error(-20001,'Missing view V$PARAMETER');

Line 301: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');

297: WHERE NAME = 'cpu_count';
298: exception
299: when NO_DATA_FOUND then
300: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
301: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304: raise_application_error(-20001,'Missing view V$PARAMETER');
305: when OTHERS then

Line 302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

298: exception
299: when NO_DATA_FOUND then
300: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
301: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304: raise_application_error(-20001,'Missing view V$PARAMETER');
305: when OTHERS then
306: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

Line 303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

299: when NO_DATA_FOUND then
300: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
301: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304: raise_application_error(-20001,'Missing view V$PARAMETER');
305: when OTHERS then
306: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
307: raise;

Line 306: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

302: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304: raise_application_error(-20001,'Missing view V$PARAMETER');
305: when OTHERS then
306: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
307: raise;
308: end;
309: begin
310: SELECT to_char(min(to_number(VALUE)))

Line 316: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');

312: FROM V$PARAMETER
313: WHERE NAME IN ('parallel_max_servers','cpu_count');
314: exception
315: when NO_DATA_FOUND then
316: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
317: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320: raise_application_error(-20001,'Missing view V$PARAMETER');

Line 317: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');

313: WHERE NAME IN ('parallel_max_servers','cpu_count');
314: exception
315: when NO_DATA_FOUND then
316: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
317: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320: raise_application_error(-20001,'Missing view V$PARAMETER');
321: when OTHERS then

Line 318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

314: exception
315: when NO_DATA_FOUND then
316: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
317: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320: raise_application_error(-20001,'Missing view V$PARAMETER');
321: when OTHERS then
322: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

Line 319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

315: when NO_DATA_FOUND then
316: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
317: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320: raise_application_error(-20001,'Missing view V$PARAMETER');
321: when OTHERS then
322: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
323: raise;

Line 322: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

318: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320: raise_application_error(-20001,'Missing view V$PARAMETER');
321: when OTHERS then
322: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
323: raise;
324: end;
325: if (tblspcname is not null) then
326: v_tablespace_name := tblspcname;

Line 341: utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||

337: WHERE exists (SELECT 1
338: FROM USER_OBJECTS
339: WHERE OBJECT_NAME = v_new_table
340: );
341: utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||
342: v_new_table);
343: raise_application_error(-20002, v_new_table||' already exists.');
344: exception
345: when NO_DATA_FOUND then

Line 348: utl_file.put_line(i_file_handle,'--Error while checking the new table name.');

344: exception
345: when NO_DATA_FOUND then
346: null;
347: when OTHERS then
348: utl_file.put_line(i_file_handle,'--Error while checking the new table name.');
349: raise;
350: end;
351:
352: --v_initial := 1M; -- Force it to smaller 1MB

Line 372: utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);

368: end if;
369:
370: -- Creating new table
371: begin
372: utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);
373: v_sql := ' create table %s'||'\n'||
374: ' pctfree %s'||'\n'||
375: ' pctused %s'||'\n'||
376: ' initrans %s'||'\n'||

Line 379: utl_file.putf(i_file_handle,v_sql,v_new_table,v_pctfree,

375: ' pctused %s'||'\n'||
376: ' initrans %s'||'\n'||
377: ' tablespace %s';
378:
379: utl_file.putf(i_file_handle,v_sql,v_new_table,v_pctfree,
380: v_pctused,v_initrans,v_tablespace_name);
381: utl_file.putf(i_file_handle,'\n');
382: utl_file.fflush(i_file_handle);
383: v_sql := ' storage (initial %s next %s '||'\n'||

Line 381: utl_file.putf(i_file_handle,'\n');

377: ' tablespace %s';
378:
379: utl_file.putf(i_file_handle,v_sql,v_new_table,v_pctfree,
380: v_pctused,v_initrans,v_tablespace_name);
381: utl_file.putf(i_file_handle,'\n');
382: utl_file.fflush(i_file_handle);
383: v_sql := ' storage (initial %s next %s '||'\n'||
384: ' freelists 2 freelist groups %s'||'\n'||
385: ' pctincrease 0 )' ||'\n'||

Line 382: utl_file.fflush(i_file_handle);

378:
379: utl_file.putf(i_file_handle,v_sql,v_new_table,v_pctfree,
380: v_pctused,v_initrans,v_tablespace_name);
381: utl_file.putf(i_file_handle,'\n');
382: utl_file.fflush(i_file_handle);
383: v_sql := ' storage (initial %s next %s '||'\n'||
384: ' freelists 2 freelist groups %s'||'\n'||
385: ' pctincrease 0 )' ||'\n'||
386: ' parallel %s'||'\n'||

Line 390: utl_file.putf(i_file_handle,v_sql,

386: ' parallel %s'||'\n'||
387: ' logging'||
388: ' as select * from %s ;'||'\n';
389:
390: utl_file.putf(i_file_handle,v_sql,
391: to_char(v_initial),to_char(v_next),v_freelist_groups,
392: v_degree,tblname);
393: utl_file.fflush(i_file_handle);
394: exception

Line 393: utl_file.fflush(i_file_handle);

389:
390: utl_file.putf(i_file_handle,v_sql,
391: to_char(v_initial),to_char(v_next),v_freelist_groups,
392: v_degree,tblname);
393: utl_file.fflush(i_file_handle);
394: exception
395: when utl_file.WRITE_ERROR then
396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);

Line 395: when utl_file.WRITE_ERROR then

391: to_char(v_initial),to_char(v_next),v_freelist_groups,
392: v_degree,tblname);
393: utl_file.fflush(i_file_handle);
394: exception
395: when utl_file.WRITE_ERROR then
396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then

Line 396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');

392: v_degree,tblname);
393: utl_file.fflush(i_file_handle);
394: exception
395: when utl_file.WRITE_ERROR then
396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then
400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');

Line 397: utl_file.fclose(i_file_handle);

393: utl_file.fflush(i_file_handle);
394: exception
395: when utl_file.WRITE_ERROR then
396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then
400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401: utl_file.fclose(i_file_handle);

Line 399: when utl_file.INTERNAL_ERROR then

395: when utl_file.WRITE_ERROR then
396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then
400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401: utl_file.fclose(i_file_handle);
402: raise;
403: when OTHERS then

Line 400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');

396: utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then
400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401: utl_file.fclose(i_file_handle);
402: raise;
403: when OTHERS then
404: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 401: utl_file.fclose(i_file_handle);

397: utl_file.fclose(i_file_handle);
398: raise;
399: when utl_file.INTERNAL_ERROR then
400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401: utl_file.fclose(i_file_handle);
402: raise;
403: when OTHERS then
404: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
405: utl_file.fclose(i_file_handle);

Line 404: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

400: utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401: utl_file.fclose(i_file_handle);
402: raise;
403: when OTHERS then
404: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
405: utl_file.fclose(i_file_handle);
406: raise;
407: end;
408:

Line 405: utl_file.fclose(i_file_handle);

401: utl_file.fclose(i_file_handle);
402: raise;
403: when OTHERS then
404: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
405: utl_file.fclose(i_file_handle);
406: raise;
407: end;
408:
409: -- changing new table

Line 410: utl_file.put_line(i_file_handle,'--Changing new table '||

406: raise;
407: end;
408:
409: -- changing new table
410: utl_file.put_line(i_file_handle,'--Changing new table '||
411: v_new_table||' to logging');
412: v_sql :='alter table %s'||'\n'||
413: 'noparallel '||'\n'||
414: 'logging;'||'\n';

Line 416: utl_file.putf(i_file_handle,v_sql,v_new_table);

412: v_sql :='alter table %s'||'\n'||
413: 'noparallel '||'\n'||
414: 'logging;'||'\n';
415: begin
416: utl_file.putf(i_file_handle,v_sql,v_new_table);
417: utl_file.fflush(i_file_handle);
418: exception
419: when OTHERS then
420: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 417: utl_file.fflush(i_file_handle);

413: 'noparallel '||'\n'||
414: 'logging;'||'\n';
415: begin
416: utl_file.putf(i_file_handle,v_sql,v_new_table);
417: utl_file.fflush(i_file_handle);
418: exception
419: when OTHERS then
420: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
421: raise;

Line 420: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

416: utl_file.putf(i_file_handle,v_sql,v_new_table);
417: utl_file.fflush(i_file_handle);
418: exception
419: when OTHERS then
420: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
421: raise;
422: end;
423: -- gathering CBO stats
424: utl_file.put_line(i_file_handle,'--Gathering CBO Stats on new table '||v_new_table);

Line 424: utl_file.put_line(i_file_handle,'--Gathering CBO Stats on new table '||v_new_table);

420: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
421: raise;
422: end;
423: -- gathering CBO stats
424: utl_file.put_line(i_file_handle,'--Gathering CBO Stats on new table '||v_new_table);
425: -- begin
426: -- utl_file.put_line(apps.fnd_stats.gather_table_stats(ownname => 'APPLSYS',
427: -- tabname => v_new_table);
428: -- null;

Line 426: -- utl_file.put_line(apps.fnd_stats.gather_table_stats(ownname => 'APPLSYS',

422: end;
423: -- gathering CBO stats
424: utl_file.put_line(i_file_handle,'--Gathering CBO Stats on new table '||v_new_table);
425: -- begin
426: -- utl_file.put_line(apps.fnd_stats.gather_table_stats(ownname => 'APPLSYS',
427: -- tabname => v_new_table);
428: -- null;
429: -- end;
430:

Line 433: utl_file.put_line(i_file_handle,'--Rename '||tblname||' to '||v_old_table);

429: -- end;
430:
431: -- rename orig table to old table
432: v_old_table := 'OO'||substr(tblname, 3, 28);
433: utl_file.put_line(i_file_handle,'--Rename '||tblname||' to '||v_old_table);
434: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
435: begin
436: utl_file.put_line(i_file_handle,v_sql);
437: utl_file.putf(i_file_handle,'\n');

Line 436: utl_file.put_line(i_file_handle,v_sql);

432: v_old_table := 'OO'||substr(tblname, 3, 28);
433: utl_file.put_line(i_file_handle,'--Rename '||tblname||' to '||v_old_table);
434: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
435: begin
436: utl_file.put_line(i_file_handle,v_sql);
437: utl_file.putf(i_file_handle,'\n');
438: utl_file.fflush(i_file_handle);
439: exception
440: when OTHERS then

Line 437: utl_file.putf(i_file_handle,'\n');

433: utl_file.put_line(i_file_handle,'--Rename '||tblname||' to '||v_old_table);
434: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
435: begin
436: utl_file.put_line(i_file_handle,v_sql);
437: utl_file.putf(i_file_handle,'\n');
438: utl_file.fflush(i_file_handle);
439: exception
440: when OTHERS then
441: utl_file.put_line(i_file_handle,'Error in: '||substr(v_sql,1,220));

Line 438: utl_file.fflush(i_file_handle);

434: v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
435: begin
436: utl_file.put_line(i_file_handle,v_sql);
437: utl_file.putf(i_file_handle,'\n');
438: utl_file.fflush(i_file_handle);
439: exception
440: when OTHERS then
441: utl_file.put_line(i_file_handle,'Error in: '||substr(v_sql,1,220));
442: raise;

Line 441: utl_file.put_line(i_file_handle,'Error in: '||substr(v_sql,1,220));

437: utl_file.putf(i_file_handle,'\n');
438: utl_file.fflush(i_file_handle);
439: exception
440: when OTHERS then
441: utl_file.put_line(i_file_handle,'Error in: '||substr(v_sql,1,220));
442: raise;
443: end;
444:
445: -- rename new table to orig table

Line 446: utl_file.put_line(i_file_handle,'--Rename '||v_new_table||

442: raise;
443: end;
444:
445: -- rename new table to orig table
446: utl_file.put_line(i_file_handle,'--Rename '||v_new_table||
447: ' to '||tblname);
448: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
449: begin
450: utl_file.put_line(i_file_handle,v_sql);

Line 450: utl_file.put_line(i_file_handle,v_sql);

446: utl_file.put_line(i_file_handle,'--Rename '||v_new_table||
447: ' to '||tblname);
448: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
449: begin
450: utl_file.put_line(i_file_handle,v_sql);
451: utl_file.putf(i_file_handle,'\n');
452: exception
453: when OTHERS then
454: raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,220));

Line 451: utl_file.putf(i_file_handle,'\n');

447: ' to '||tblname);
448: v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
449: begin
450: utl_file.put_line(i_file_handle,v_sql);
451: utl_file.putf(i_file_handle,'\n');
452: exception
453: when OTHERS then
454: raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,220));
455: end;

Line 457: utl_file.fclose(i_file_handle);

453: when OTHERS then
454: raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,220));
455: end;
456: end if;
457: utl_file.fclose(i_file_handle);
458: --set the out parameter value to TRUE for indicating
459: --whether or not we sould create index or not.
460: --If the table is already partitioned then we set this parameter
461: --to FALSE to indicate we don't need to recreate indexes on it.

Line 465: utl_file.put_line(i_file_handle,'--Error in CreateTable '||tblname);

461: --to FALSE to indicate we don't need to recreate indexes on it.
462: modified := TRUE;
463: exception
464: when OTHERS then
465: utl_file.put_line(i_file_handle,'--Error in CreateTable '||tblname);
466: if (utl_file.is_open(i_file_handle)) then
467: utl_file.fclose(i_file_handle);
468: end if;
469: raise;

Line 466: if (utl_file.is_open(i_file_handle)) then

462: modified := TRUE;
463: exception
464: when OTHERS then
465: utl_file.put_line(i_file_handle,'--Error in CreateTable '||tblname);
466: if (utl_file.is_open(i_file_handle)) then
467: utl_file.fclose(i_file_handle);
468: end if;
469: raise;
470: end CreateTable;

Line 467: utl_file.fclose(i_file_handle);

463: exception
464: when OTHERS then
465: utl_file.put_line(i_file_handle,'--Error in CreateTable '||tblname);
466: if (utl_file.is_open(i_file_handle)) then
467: utl_file.fclose(i_file_handle);
468: end if;
469: raise;
470: end CreateTable;
471:

Line 496: i_file_handle utl_file.file_type;

492: v_freelist_groups varchar2(30);
493: v_initrans varchar2(30);
494: v_pctfree varchar2(30);
495: v_degree varchar2(30);
496: i_file_handle utl_file.file_type;
497: begin
498: begin
499: if (partition) then
500: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);

Line 500: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);

496: i_file_handle utl_file.file_type;
497: begin
498: begin
499: if (partition) then
500: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);
501: utl_file.putf(i_file_handle,' '||'\n');
502: SELECT I.TABLESPACE_NAME, I.INITIAL_EXTENT, I.NEXT_EXTENT, I.PCT_INCREASE,
503: I.PARTITIONED, T.PARTITIONED
504: INTO v_tablespace_name, v_initial, v_next, v_pctinc,

Line 501: utl_file.putf(i_file_handle,' '||'\n');

497: begin
498: begin
499: if (partition) then
500: i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);
501: utl_file.putf(i_file_handle,' '||'\n');
502: SELECT I.TABLESPACE_NAME, I.INITIAL_EXTENT, I.NEXT_EXTENT, I.PCT_INCREASE,
503: I.PARTITIONED, T.PARTITIONED
504: INTO v_tablespace_name, v_initial, v_next, v_pctinc,
505: v_idxpartitioned, v_tblpartitioned

Line 519: i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);

515: else
516: v_next := greatest(v_next,1048576);
517: end if;
518: else
519: i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);
520: SELECT NVL(I.TABLESPACE_NAME,UPI.DEF_TABLESPACE_NAME),
521: TO_CHAR(GREATEST(NVL(I.INITIAL_EXTENT,
522: TO_NUMBER(UPI.DEF_INITIAL_EXTENT)),65536)),
523: TO_CHAR(GREATEST(NVL(I.NEXT_EXTENT,

Line 546: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

542:
543: end if;
544: exception
545: when NO_DATA_FOUND then
546: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
547: utl_file.put_line(i_file_handle,'-- Index '||idxname||
548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

Line 547: utl_file.put_line(i_file_handle,'-- Index '||idxname||

543: end if;
544: exception
545: when NO_DATA_FOUND then
546: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
547: utl_file.put_line(i_file_handle,'-- Index '||idxname||
548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551: utl_file.fflush(i_file_handle);

Line 549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

545: when NO_DATA_FOUND then
546: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
547: utl_file.put_line(i_file_handle,'-- Index '||idxname||
548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551: utl_file.fflush(i_file_handle);
552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);

Line 550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

546: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
547: utl_file.put_line(i_file_handle,'-- Index '||idxname||
548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551: utl_file.fflush(i_file_handle);
552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);
554: when OTHERS then

Line 551: utl_file.fflush(i_file_handle);

547: utl_file.put_line(i_file_handle,'-- Index '||idxname||
548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551: utl_file.fflush(i_file_handle);
552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);
554: when OTHERS then
555: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

Line 552: utl_file.fclose(i_file_handle);

548: ' does not exist.');
549: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551: utl_file.fflush(i_file_handle);
552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);
554: when OTHERS then
555: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
556: utl_file.fclose(i_file_handle);

Line 555: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');

551: utl_file.fflush(i_file_handle);
552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);
554: when OTHERS then
555: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
556: utl_file.fclose(i_file_handle);
557: raise;
558: end;
559:

Line 556: utl_file.fclose(i_file_handle);

552: utl_file.fclose(i_file_handle);
553: raise_application_error(-20011,'Missing index '||idxname);
554: when OTHERS then
555: utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
556: utl_file.fclose(i_file_handle);
557: raise;
558: end;
559:
560: if (partition) then

Line 577: utl_file.put_line(i_file_handle,'--Name conflict. Please first drop '||v_new_index);

573: SELECT 1
574: FROM USER_OBJECTS
575: WHERE OBJECT_NAME = v_new_index
576: );
577: utl_file.put_line(i_file_handle,'--Name conflict. Please first drop '||v_new_index);
578: utl_file.fclose(i_file_handle);
579: raise_application_error(-20012, v_new_index||' already exists.');
580: exception
581: when NO_DATA_FOUND then

Line 578: utl_file.fclose(i_file_handle);

574: FROM USER_OBJECTS
575: WHERE OBJECT_NAME = v_new_index
576: );
577: utl_file.put_line(i_file_handle,'--Name conflict. Please first drop '||v_new_index);
578: utl_file.fclose(i_file_handle);
579: raise_application_error(-20012, v_new_index||' already exists.');
580: exception
581: when NO_DATA_FOUND then
582: null;

Line 584: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');

580: exception
581: when NO_DATA_FOUND then
582: null;
583: when OTHERS then
584: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
585: utl_file.fclose(i_file_handle);
586: raise;
587: end;
588:

Line 585: utl_file.fclose(i_file_handle);

581: when NO_DATA_FOUND then
582: null;
583: when OTHERS then
584: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
585: utl_file.fclose(i_file_handle);
586: raise;
587: end;
588:
589: utl_file.put_line(i_file_handle,'--Recreate index '||idxname);

Line 589: utl_file.put_line(i_file_handle,'--Recreate index '||idxname);

585: utl_file.fclose(i_file_handle);
586: raise;
587: end;
588:
589: utl_file.put_line(i_file_handle,'--Recreate index '||idxname);
590: v_prefix := 'WFO_';
591: --v_initial := 40K; -- force it to 40k
592: if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
593: partition_index := true;

Line 995: utl_file.put_line(i_file_handle,'--Index '||idxname||

991: -- now check if we need to proceed
992: if (partition_index) then
993: if (v_idxpartitioned = 'YES') then
994: -- already partitioned. Just exit.
995: utl_file.put_line(i_file_handle,'--Index '||idxname||
996: ' already partitioned.');
997: utl_file.fflush(i_file_handle);
998: utl_file.fclose(i_file_handle);
999: return;

Line 997: utl_file.fflush(i_file_handle);

993: if (v_idxpartitioned = 'YES') then
994: -- already partitioned. Just exit.
995: utl_file.put_line(i_file_handle,'--Index '||idxname||
996: ' already partitioned.');
997: utl_file.fflush(i_file_handle);
998: utl_file.fclose(i_file_handle);
999: return;
1000: elsif (v_tblpartitioned = 'YES') then
1001: -- first drop the index

Line 998: utl_file.fclose(i_file_handle);

994: -- already partitioned. Just exit.
995: utl_file.put_line(i_file_handle,'--Index '||idxname||
996: ' already partitioned.');
997: utl_file.fflush(i_file_handle);
998: utl_file.fclose(i_file_handle);
999: return;
1000: elsif (v_tblpartitioned = 'YES') then
1001: -- first drop the index
1002: drop_index := true;

Line 1004: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');

1000: elsif (v_tblpartitioned = 'YES') then
1001: -- first drop the index
1002: drop_index := true;
1003: begin
1004: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1005: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1006: utl_file.putf(i_file_handle,' '||'\n');
1007: exception
1008: when OTHERS then

Line 1005: utl_file.put_line(i_file_handle,'Drop index '||idxname);

1001: -- first drop the index
1002: drop_index := true;
1003: begin
1004: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1005: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1006: utl_file.putf(i_file_handle,' '||'\n');
1007: exception
1008: when OTHERS then
1009: utl_file.put_line(i_file_handle,'--Error in SQL: '||

Line 1006: utl_file.putf(i_file_handle,' '||'\n');

1002: drop_index := true;
1003: begin
1004: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1005: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1006: utl_file.putf(i_file_handle,' '||'\n');
1007: exception
1008: when OTHERS then
1009: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1010: substr('drop index '||idxname,1,200));

Line 1009: utl_file.put_line(i_file_handle,'--Error in SQL: '||

1005: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1006: utl_file.putf(i_file_handle,' '||'\n');
1007: exception
1008: when OTHERS then
1009: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1010: substr('drop index '||idxname,1,200));
1011: utl_file.fclose(i_file_handle);
1012: raise;
1013: end;

Line 1011: utl_file.fclose(i_file_handle);

1007: exception
1008: when OTHERS then
1009: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1010: substr('drop index '||idxname,1,200));
1011: utl_file.fclose(i_file_handle);
1012: raise;
1013: end;
1014:
1015: end if;

Line 1020: utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');

1016: else
1017: if (v_tblpartitioned = 'YES') then
1018: -- Detected table partitioned meant this script had already been run.
1019: -- Just exit.
1020: utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');
1021: utl_file.fclose(i_file_handle);
1022: return;
1023: end if;
1024: end if;

Line 1021: utl_file.fclose(i_file_handle);

1017: if (v_tblpartitioned = 'YES') then
1018: -- Detected table partitioned meant this script had already been run.
1019: -- Just exit.
1020: utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');
1021: utl_file.fclose(i_file_handle);
1022: return;
1023: end if;
1024: end if;
1025:

Line 1027: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);

1023: end if;
1024: end if;
1025:
1026: begin
1027: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1028: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1029: to_char(v_next),to_char(v_pctinc));
1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);

Line 1028: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,

1024: end if;
1025:
1026: begin
1027: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1028: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1029: to_char(v_next),to_char(v_pctinc));
1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);
1032: exception

Line 1030: utl_file.putf(i_file_handle,'\n');

1026: begin
1027: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1028: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1029: to_char(v_next),to_char(v_pctinc));
1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);
1032: exception
1033: when UTL_FILE.WRITE_ERROR then
1034: utl_file.put_line(i_file_handle,'--Error writing sql '||

Line 1031: utl_file.fflush(i_file_handle);

1027: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1028: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1029: to_char(v_next),to_char(v_pctinc));
1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);
1032: exception
1033: when UTL_FILE.WRITE_ERROR then
1034: utl_file.put_line(i_file_handle,'--Error writing sql '||
1035: substr(v_sql,1,220)||'to file');

Line 1033: when UTL_FILE.WRITE_ERROR then

1029: to_char(v_next),to_char(v_pctinc));
1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);
1032: exception
1033: when UTL_FILE.WRITE_ERROR then
1034: utl_file.put_line(i_file_handle,'--Error writing sql '||
1035: substr(v_sql,1,220)||'to file');
1036: utl_file.fclose(i_file_handle);
1037: raise;

Line 1034: utl_file.put_line(i_file_handle,'--Error writing sql '||

1030: utl_file.putf(i_file_handle,'\n');
1031: utl_file.fflush(i_file_handle);
1032: exception
1033: when UTL_FILE.WRITE_ERROR then
1034: utl_file.put_line(i_file_handle,'--Error writing sql '||
1035: substr(v_sql,1,220)||'to file');
1036: utl_file.fclose(i_file_handle);
1037: raise;
1038: when UTL_FILE.INVALID_FILEHANDLE then

Line 1036: utl_file.fclose(i_file_handle);

1032: exception
1033: when UTL_FILE.WRITE_ERROR then
1034: utl_file.put_line(i_file_handle,'--Error writing sql '||
1035: substr(v_sql,1,220)||'to file');
1036: utl_file.fclose(i_file_handle);
1037: raise;
1038: when UTL_FILE.INVALID_FILEHANDLE then
1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);

Line 1038: when UTL_FILE.INVALID_FILEHANDLE then

1034: utl_file.put_line(i_file_handle,'--Error writing sql '||
1035: substr(v_sql,1,220)||'to file');
1036: utl_file.fclose(i_file_handle);
1037: raise;
1038: when UTL_FILE.INVALID_FILEHANDLE then
1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then

Line 1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');

1035: substr(v_sql,1,220)||'to file');
1036: utl_file.fclose(i_file_handle);
1037: raise;
1038: when UTL_FILE.INVALID_FILEHANDLE then
1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then
1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1040: utl_file.fclose(i_file_handle);

1036: utl_file.fclose(i_file_handle);
1037: raise;
1038: when UTL_FILE.INVALID_FILEHANDLE then
1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then
1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1044: utl_file.fclose(i_file_handle);

Line 1042: when UTL_FILE.INVALID_OPERATION then

1038: when UTL_FILE.INVALID_FILEHANDLE then
1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then
1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1044: utl_file.fclose(i_file_handle);
1045: raise;
1046: when OTHERS then

Line 1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

1039: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then
1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1044: utl_file.fclose(i_file_handle);
1045: raise;
1046: when OTHERS then
1047: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 1044: utl_file.fclose(i_file_handle);

1040: utl_file.fclose(i_file_handle);
1041: raise;
1042: when UTL_FILE.INVALID_OPERATION then
1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1044: utl_file.fclose(i_file_handle);
1045: raise;
1046: when OTHERS then
1047: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1048: utl_file.fclose(i_file_handle);

Line 1047: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

1043: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1044: utl_file.fclose(i_file_handle);
1045: raise;
1046: when OTHERS then
1047: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1048: utl_file.fclose(i_file_handle);
1049: raise;
1050: end;
1051:

Line 1048: utl_file.fclose(i_file_handle);

1044: utl_file.fclose(i_file_handle);
1045: raise;
1046: when OTHERS then
1047: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1048: utl_file.fclose(i_file_handle);
1049: raise;
1050: end;
1051:
1052: -- Rename only if we did not drop index before

Line 1056: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||

1052: -- Rename only if we did not drop index before
1053: if (not drop_index) then
1054: -- rename orig index to old index
1055: v_old_index := substr(v_prefix||idxname, 1, 30);
1056: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||
1057: v_old_index);
1058: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1059: begin
1060: utl_file.put_line(i_file_handle,'--Execute the statement :');

Line 1060: utl_file.put_line(i_file_handle,'--Execute the statement :');

1056: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||
1057: v_old_index);
1058: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1059: begin
1060: utl_file.put_line(i_file_handle,'--Execute the statement :');
1061: utl_file.put_line(i_file_handle,v_sql);
1062: utl_file.putf(i_file_handle,'\n');
1063: exception
1064: when UTL_FILE.WRITE_ERROR then

Line 1061: utl_file.put_line(i_file_handle,v_sql);

1057: v_old_index);
1058: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1059: begin
1060: utl_file.put_line(i_file_handle,'--Execute the statement :');
1061: utl_file.put_line(i_file_handle,v_sql);
1062: utl_file.putf(i_file_handle,'\n');
1063: exception
1064: when UTL_FILE.WRITE_ERROR then
1065: utl_file.put_line(i_file_handle,'--Error writing sql '||

Line 1062: utl_file.putf(i_file_handle,'\n');

1058: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1059: begin
1060: utl_file.put_line(i_file_handle,'--Execute the statement :');
1061: utl_file.put_line(i_file_handle,v_sql);
1062: utl_file.putf(i_file_handle,'\n');
1063: exception
1064: when UTL_FILE.WRITE_ERROR then
1065: utl_file.put_line(i_file_handle,'--Error writing sql '||
1066: substr(v_sql,1,220)||'to file');

Line 1064: when UTL_FILE.WRITE_ERROR then

1060: utl_file.put_line(i_file_handle,'--Execute the statement :');
1061: utl_file.put_line(i_file_handle,v_sql);
1062: utl_file.putf(i_file_handle,'\n');
1063: exception
1064: when UTL_FILE.WRITE_ERROR then
1065: utl_file.put_line(i_file_handle,'--Error writing sql '||
1066: substr(v_sql,1,220)||'to file');
1067: utl_file.fclose(i_file_handle);
1068: raise;

Line 1065: utl_file.put_line(i_file_handle,'--Error writing sql '||

1061: utl_file.put_line(i_file_handle,v_sql);
1062: utl_file.putf(i_file_handle,'\n');
1063: exception
1064: when UTL_FILE.WRITE_ERROR then
1065: utl_file.put_line(i_file_handle,'--Error writing sql '||
1066: substr(v_sql,1,220)||'to file');
1067: utl_file.fclose(i_file_handle);
1068: raise;
1069: when UTL_FILE.INVALID_FILEHANDLE then

Line 1067: utl_file.fclose(i_file_handle);

1063: exception
1064: when UTL_FILE.WRITE_ERROR then
1065: utl_file.put_line(i_file_handle,'--Error writing sql '||
1066: substr(v_sql,1,220)||'to file');
1067: utl_file.fclose(i_file_handle);
1068: raise;
1069: when UTL_FILE.INVALID_FILEHANDLE then
1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);

Line 1069: when UTL_FILE.INVALID_FILEHANDLE then

1065: utl_file.put_line(i_file_handle,'--Error writing sql '||
1066: substr(v_sql,1,220)||'to file');
1067: utl_file.fclose(i_file_handle);
1068: raise;
1069: when UTL_FILE.INVALID_FILEHANDLE then
1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then

Line 1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');

1066: substr(v_sql,1,220)||'to file');
1067: utl_file.fclose(i_file_handle);
1068: raise;
1069: when UTL_FILE.INVALID_FILEHANDLE then
1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then
1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1071: utl_file.fclose(i_file_handle);

1067: utl_file.fclose(i_file_handle);
1068: raise;
1069: when UTL_FILE.INVALID_FILEHANDLE then
1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then
1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1075: utl_file.fclose(i_file_handle);

Line 1073: when UTL_FILE.INVALID_OPERATION then

1069: when UTL_FILE.INVALID_FILEHANDLE then
1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then
1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1075: utl_file.fclose(i_file_handle);
1076: raise;
1077: when others then

Line 1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

1070: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then
1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1075: utl_file.fclose(i_file_handle);
1076: raise;
1077: when others then
1078: utl_file.fclose(i_file_handle);

Line 1075: utl_file.fclose(i_file_handle);

1071: utl_file.fclose(i_file_handle);
1072: raise;
1073: when UTL_FILE.INVALID_OPERATION then
1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1075: utl_file.fclose(i_file_handle);
1076: raise;
1077: when others then
1078: utl_file.fclose(i_file_handle);
1079: raise;

Line 1078: utl_file.fclose(i_file_handle);

1074: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1075: utl_file.fclose(i_file_handle);
1076: raise;
1077: when others then
1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: end;
1081: end if;
1082:

Line 1084: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);

1080: end;
1081: end if;
1082:
1083: -- rename new index to orig index
1084: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1085: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1086: begin
1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1088: utl_file.put_line(i_file_handle,v_sql);

Line 1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');

1083: -- rename new index to orig index
1084: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1085: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1086: begin
1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1088: utl_file.put_line(i_file_handle,v_sql);
1089: utl_file.putf(i_file_handle,' '||'\n');
1090: exception
1091: when UTL_FILE.WRITE_ERROR then

Line 1088: utl_file.put_line(i_file_handle,v_sql);

1084: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1085: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1086: begin
1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1088: utl_file.put_line(i_file_handle,v_sql);
1089: utl_file.putf(i_file_handle,' '||'\n');
1090: exception
1091: when UTL_FILE.WRITE_ERROR then
1092: utl_file.put_line(i_file_handle,'--Error writing sql '||

Line 1089: utl_file.putf(i_file_handle,' '||'\n');

1085: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1086: begin
1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1088: utl_file.put_line(i_file_handle,v_sql);
1089: utl_file.putf(i_file_handle,' '||'\n');
1090: exception
1091: when UTL_FILE.WRITE_ERROR then
1092: utl_file.put_line(i_file_handle,'--Error writing sql '||
1093: substr(v_sql,1,220)||'to file');

Line 1091: when UTL_FILE.WRITE_ERROR then

1087: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1088: utl_file.put_line(i_file_handle,v_sql);
1089: utl_file.putf(i_file_handle,' '||'\n');
1090: exception
1091: when UTL_FILE.WRITE_ERROR then
1092: utl_file.put_line(i_file_handle,'--Error writing sql '||
1093: substr(v_sql,1,220)||'to file');
1094: utl_file.fclose(i_file_handle);
1095: raise;

Line 1092: utl_file.put_line(i_file_handle,'--Error writing sql '||

1088: utl_file.put_line(i_file_handle,v_sql);
1089: utl_file.putf(i_file_handle,' '||'\n');
1090: exception
1091: when UTL_FILE.WRITE_ERROR then
1092: utl_file.put_line(i_file_handle,'--Error writing sql '||
1093: substr(v_sql,1,220)||'to file');
1094: utl_file.fclose(i_file_handle);
1095: raise;
1096: when UTL_FILE.INVALID_FILEHANDLE then

Line 1094: utl_file.fclose(i_file_handle);

1090: exception
1091: when UTL_FILE.WRITE_ERROR then
1092: utl_file.put_line(i_file_handle,'--Error writing sql '||
1093: substr(v_sql,1,220)||'to file');
1094: utl_file.fclose(i_file_handle);
1095: raise;
1096: when UTL_FILE.INVALID_FILEHANDLE then
1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);

Line 1096: when UTL_FILE.INVALID_FILEHANDLE then

1092: utl_file.put_line(i_file_handle,'--Error writing sql '||
1093: substr(v_sql,1,220)||'to file');
1094: utl_file.fclose(i_file_handle);
1095: raise;
1096: when UTL_FILE.INVALID_FILEHANDLE then
1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then

Line 1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');

1093: substr(v_sql,1,220)||'to file');
1094: utl_file.fclose(i_file_handle);
1095: raise;
1096: when UTL_FILE.INVALID_FILEHANDLE then
1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then
1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1098: utl_file.fclose(i_file_handle);

1094: utl_file.fclose(i_file_handle);
1095: raise;
1096: when UTL_FILE.INVALID_FILEHANDLE then
1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then
1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1102: utl_file.fclose(i_file_handle);

Line 1100: when UTL_FILE.INVALID_OPERATION then

1096: when UTL_FILE.INVALID_FILEHANDLE then
1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then
1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1102: utl_file.fclose(i_file_handle);
1103: raise;
1104: when others then

Line 1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

1097: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then
1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1102: utl_file.fclose(i_file_handle);
1103: raise;
1104: when others then
1105: utl_file.fclose(i_file_handle);

Line 1102: utl_file.fclose(i_file_handle);

1098: utl_file.fclose(i_file_handle);
1099: raise;
1100: when UTL_FILE.INVALID_OPERATION then
1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1102: utl_file.fclose(i_file_handle);
1103: raise;
1104: when others then
1105: utl_file.fclose(i_file_handle);
1106: raise;

Line 1105: utl_file.fclose(i_file_handle);

1101: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1102: utl_file.fclose(i_file_handle);
1103: raise;
1104: when others then
1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: end;
1108:
1109: else

Line 1118: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

1114: FROM V$PARAMETER
1115: WHERE NAME = 'cpu_count';
1116: exception
1117: when NO_DATA_FOUND then
1118: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1119: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1122: raise_application_error(-20001,'Missing view V$PARAMETER');

Line 1119: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');

1115: WHERE NAME = 'cpu_count';
1116: exception
1117: when NO_DATA_FOUND then
1118: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1119: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1122: raise_application_error(-20001,'Missing view V$PARAMETER');
1123: when OTHERS then

Line 1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

1116: exception
1117: when NO_DATA_FOUND then
1118: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1119: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1122: raise_application_error(-20001,'Missing view V$PARAMETER');
1123: when OTHERS then
1124: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

Line 1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

1117: when NO_DATA_FOUND then
1118: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1119: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1122: raise_application_error(-20001,'Missing view V$PARAMETER');
1123: when OTHERS then
1124: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1125: raise;

Line 1124: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

1120: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1121: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1122: raise_application_error(-20001,'Missing view V$PARAMETER');
1123: when OTHERS then
1124: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1125: raise;
1126: end;
1127:
1128: begin

Line 1135: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');

1131: FROM V$PARAMETER
1132: WHERE NAME IN ('parallel_max_servers','cpu_count');
1133: exception
1134: when NO_DATA_FOUND then
1135: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1136: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1137: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1138:
1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

Line 1136: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');

1132: WHERE NAME IN ('parallel_max_servers','cpu_count');
1133: exception
1134: when NO_DATA_FOUND then
1135: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1136: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1137: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1138:
1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1140: raise_application_error(-20001,'Missing view V$PARAMETER');

Line 1137: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');

1133: exception
1134: when NO_DATA_FOUND then
1135: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1136: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1137: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1138:
1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1140: raise_application_error(-20001,'Missing view V$PARAMETER');
1141:

Line 1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

1135: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1136: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1137: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1138:
1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1140: raise_application_error(-20001,'Missing view V$PARAMETER');
1141:
1142: when OTHERS then
1143: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

Line 1143: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

1139: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1140: raise_application_error(-20001,'Missing view V$PARAMETER');
1141:
1142: when OTHERS then
1143: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1144: raise;
1145: end;
1146:
1147: if (tblspcname is not null) then

Line 1162: utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||v_new_index);

1158: where exists (select 1
1159: from USER_OBJECTS
1160: where OBJECT_NAME = v_new_index
1161: );
1162: utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||v_new_index);
1163: raise_application_error(-20012, v_new_index||' already exists.');
1164: exception
1165: when NO_DATA_FOUND then
1166: null;

Line 1168: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');

1164: exception
1165: when NO_DATA_FOUND then
1166: null;
1167: when OTHERS then
1168: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
1169: raise;
1170: end;
1171:
1172:

Line 1173: utl_file.put_line(i_file_handle,'--Recreating index '||idxname);

1169: raise;
1170: end;
1171:
1172:
1173: utl_file.put_line(i_file_handle,'--Recreating index '||idxname);
1174:
1175: -- v_initial := v_next; -- force it to uniform size
1176: -- v_initial := 1M;
1177: v_pctinc := '0';

Line 1181: utl_file.put_line(i_file_handle,'--Create index '||v_new_index);

1177: v_pctinc := '0';
1178: v_pctfree := '0';
1179:
1180: begin
1181: utl_file.put_line(i_file_handle,'--Create index '||v_new_index);
1182:
1183: if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
1184: v_sql := 'create unique index %s'||'\n'||
1185: ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n';

Line 1245: utl_file.putf(i_file_handle,v_sql,v_new_index);

1241: ' on WF_ITEMS (HA_MIGRATION_FLAG, ITEM_TYPE, ITEM_KEY)'||'\n';
1242: v_initial := 1048576; -- 1M
1243: end if;
1244:
1245: utl_file.putf(i_file_handle,v_sql,v_new_index);
1246: utl_file.fflush(i_file_handle);
1247:
1248: v_sql := ' pctfree %s'||'\n'||
1249: ' initrans %s'||'\n'||

Line 1246: utl_file.fflush(i_file_handle);

1242: v_initial := 1048576; -- 1M
1243: end if;
1244:
1245: utl_file.putf(i_file_handle,v_sql,v_new_index);
1246: utl_file.fflush(i_file_handle);
1247:
1248: v_sql := ' pctfree %s'||'\n'||
1249: ' initrans %s'||'\n'||
1250: ' tablespace %s'||'\n'||

Line 1252: utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,

1248: v_sql := ' pctfree %s'||'\n'||
1249: ' initrans %s'||'\n'||
1250: ' tablespace %s'||'\n'||
1251: ' storage (initial %s next %s'||'\n';
1252: utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,
1253: v_tablespace_name,to_char(v_initial),to_char(v_next));
1254: utl_file.fflush(i_file_handle);
1255: v_sql := ' freelists 2 freelist groups %s'||'\n'||
1256: ' pctincrease 0)'||'\n'||

Line 1254: utl_file.fflush(i_file_handle);

1250: ' tablespace %s'||'\n'||
1251: ' storage (initial %s next %s'||'\n';
1252: utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,
1253: v_tablespace_name,to_char(v_initial),to_char(v_next));
1254: utl_file.fflush(i_file_handle);
1255: v_sql := ' freelists 2 freelist groups %s'||'\n'||
1256: ' pctincrease 0)'||'\n'||
1257: ' parallel '||'\n'||
1258: ' logging;'||'\n';

Line 1260: utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);

1256: ' pctincrease 0)'||'\n'||
1257: ' parallel '||'\n'||
1258: ' logging;'||'\n';
1259:
1260: utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);
1261: utl_file.fflush(i_file_handle);
1262:
1263: exception
1264: when OTHERS then

Line 1261: utl_file.fflush(i_file_handle);

1257: ' parallel '||'\n'||
1258: ' logging;'||'\n';
1259:
1260: utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);
1261: utl_file.fflush(i_file_handle);
1262:
1263: exception
1264: when OTHERS then
1265: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 1265: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

1261: utl_file.fflush(i_file_handle);
1262:
1263: exception
1264: when OTHERS then
1265: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1266: raise;
1267: end;
1268:
1269: begin

Line 1270: utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');

1266: raise;
1267: end;
1268:
1269: begin
1270: utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');
1271: v_sql := 'alter index %s'||'\n'||
1272: ' noparallel'||'\n'||
1273: ' logging;'||'\n';
1274: utl_file.putf(i_file_handle,v_sql,v_new_index);

Line 1274: utl_file.putf(i_file_handle,v_sql,v_new_index);

1270: utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');
1271: v_sql := 'alter index %s'||'\n'||
1272: ' noparallel'||'\n'||
1273: ' logging;'||'\n';
1274: utl_file.putf(i_file_handle,v_sql,v_new_index);
1275: utl_file.fflush(i_file_handle);
1276: exception
1277: when OTHERS then
1278: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 1275: utl_file.fflush(i_file_handle);

1271: v_sql := 'alter index %s'||'\n'||
1272: ' noparallel'||'\n'||
1273: ' logging;'||'\n';
1274: utl_file.putf(i_file_handle,v_sql,v_new_index);
1275: utl_file.fflush(i_file_handle);
1276: exception
1277: when OTHERS then
1278: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1279: raise;

Line 1278: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

1274: utl_file.putf(i_file_handle,v_sql,v_new_index);
1275: utl_file.fflush(i_file_handle);
1276: exception
1277: when OTHERS then
1278: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1279: raise;
1280: end;
1281:
1282: -- gathering CBO stats

Line 1283: UTL_FILE.PUT_LINE(I_FILE_Handle,'--Gathering CBO Stats on new index '||

1279: raise;
1280: end;
1281:
1282: -- gathering CBO stats
1283: UTL_FILE.PUT_LINE(I_FILE_Handle,'--Gathering CBO Stats on new index '||
1284: v_new_index);
1285: -- begin
1286: -- apps.fnd_stats.gather_index_stats(ownname=>'APPLSYS',
1287: -- indname=>v_new_index);

Line 1293: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);

1289: -- end;
1290:
1291: -- rename orig index to old index
1292: v_old_index := 'OO'||substr(idxname,3,28);
1293: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);
1294: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1295:
1296: begin
1297: utl_file.put_line(i_file_handle,v_sql);

Line 1297: utl_file.put_line(i_file_handle,v_sql);

1293: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);
1294: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1295:
1296: begin
1297: utl_file.put_line(i_file_handle,v_sql);
1298: exception
1299: when OTHERS then
1300: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1301: raise;

Line 1300: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

1296: begin
1297: utl_file.put_line(i_file_handle,v_sql);
1298: exception
1299: when OTHERS then
1300: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1301: raise;
1302: end;
1303:
1304: -- rename new index to orig index

Line 1305: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);

1301: raise;
1302: end;
1303:
1304: -- rename new index to orig index
1305: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1306: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1307: begin
1308: utl_file.put_line(i_file_handle,v_sql);
1309: exception

Line 1308: utl_file.put_line(i_file_handle,v_sql);

1304: -- rename new index to orig index
1305: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1306: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1307: begin
1308: utl_file.put_line(i_file_handle,v_sql);
1309: exception
1310: when OTHERS then
1311: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1312: raise;

Line 1311: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

1307: begin
1308: utl_file.put_line(i_file_handle,v_sql);
1309: exception
1310: when OTHERS then
1311: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1312: raise;
1313: end;
1314:
1315: end if;

Line 1317: utl_file.fclose(i_file_handle);

1313: end;
1314:
1315: end if;
1316:
1317: utl_file.fclose(i_file_handle);
1318: exception
1319: when OTHERS then
1320: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1321: utl_file.fclose(i_file_handle);

Line 1320: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);

1316:
1317: utl_file.fclose(i_file_handle);
1318: exception
1319: when OTHERS then
1320: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1321: utl_file.fclose(i_file_handle);
1322: raise;
1323: end CreateIndex;
1324:

Line 1321: utl_file.fclose(i_file_handle);

1317: utl_file.fclose(i_file_handle);
1318: exception
1319: when OTHERS then
1320: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1321: utl_file.fclose(i_file_handle);
1322: raise;
1323: end CreateIndex;
1324:
1325: PROCEDURE Start_partition(p_tablespace in varchar2,