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 1006: utl_file.put_line(i_file_handle,'--Index '||idxname||

1002: -- now check if we need to proceed
1003: if (partition_index) then
1004: if (v_idxpartitioned = 'YES') then
1005: -- already partitioned. Just exit.
1006: utl_file.put_line(i_file_handle,'--Index '||idxname||
1007: ' already partitioned.');
1008: utl_file.fflush(i_file_handle);
1009: utl_file.fclose(i_file_handle);
1010: return;

Line 1008: utl_file.fflush(i_file_handle);

1004: if (v_idxpartitioned = 'YES') then
1005: -- already partitioned. Just exit.
1006: utl_file.put_line(i_file_handle,'--Index '||idxname||
1007: ' already partitioned.');
1008: utl_file.fflush(i_file_handle);
1009: utl_file.fclose(i_file_handle);
1010: return;
1011: elsif (v_tblpartitioned = 'YES') then
1012: -- first drop the index

Line 1009: utl_file.fclose(i_file_handle);

1005: -- already partitioned. Just exit.
1006: utl_file.put_line(i_file_handle,'--Index '||idxname||
1007: ' already partitioned.');
1008: utl_file.fflush(i_file_handle);
1009: utl_file.fclose(i_file_handle);
1010: return;
1011: elsif (v_tblpartitioned = 'YES') then
1012: -- first drop the index
1013: drop_index := true;

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

1011: elsif (v_tblpartitioned = 'YES') then
1012: -- first drop the index
1013: drop_index := true;
1014: begin
1015: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1016: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1017: utl_file.putf(i_file_handle,' '||'\n');
1018: exception
1019: when OTHERS then

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

1012: -- first drop the index
1013: drop_index := true;
1014: begin
1015: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1016: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1017: utl_file.putf(i_file_handle,' '||'\n');
1018: exception
1019: when OTHERS then
1020: utl_file.put_line(i_file_handle,'--Error in SQL: '||

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

1013: drop_index := true;
1014: begin
1015: utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1016: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1017: utl_file.putf(i_file_handle,' '||'\n');
1018: exception
1019: when OTHERS then
1020: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1021: substr('drop index '||idxname,1,200));

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

1016: utl_file.put_line(i_file_handle,'Drop index '||idxname);
1017: utl_file.putf(i_file_handle,' '||'\n');
1018: exception
1019: when OTHERS then
1020: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1021: substr('drop index '||idxname,1,200));
1022: utl_file.fclose(i_file_handle);
1023: raise;
1024: end;

Line 1022: utl_file.fclose(i_file_handle);

1018: exception
1019: when OTHERS then
1020: utl_file.put_line(i_file_handle,'--Error in SQL: '||
1021: substr('drop index '||idxname,1,200));
1022: utl_file.fclose(i_file_handle);
1023: raise;
1024: end;
1025:
1026: end if;

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

1027: else
1028: if (v_tblpartitioned = 'YES') then
1029: -- Detected table partitioned meant this script had already been run.
1030: -- Just exit.
1031: utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');
1032: utl_file.fclose(i_file_handle);
1033: return;
1034: end if;
1035: end if;

Line 1032: utl_file.fclose(i_file_handle);

1028: if (v_tblpartitioned = 'YES') then
1029: -- Detected table partitioned meant this script had already been run.
1030: -- Just exit.
1031: utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');
1032: utl_file.fclose(i_file_handle);
1033: return;
1034: end if;
1035: end if;
1036:

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

1034: end if;
1035: end if;
1036:
1037: begin
1038: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1039: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1040: to_char(v_next),to_char(v_pctinc));
1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);

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

1035: end if;
1036:
1037: begin
1038: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1039: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1040: to_char(v_next),to_char(v_pctinc));
1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);
1043: exception

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

1037: begin
1038: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1039: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1040: to_char(v_next),to_char(v_pctinc));
1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);
1043: exception
1044: when UTL_FILE.WRITE_ERROR then
1045: utl_file.put_line(i_file_handle,'--Error writing sql '||

Line 1042: utl_file.fflush(i_file_handle);

1038: utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1039: utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1040: to_char(v_next),to_char(v_pctinc));
1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);
1043: exception
1044: when UTL_FILE.WRITE_ERROR then
1045: utl_file.put_line(i_file_handle,'--Error writing sql '||
1046: substr(v_sql,1,220)||'to file');

Line 1044: when UTL_FILE.WRITE_ERROR then

1040: to_char(v_next),to_char(v_pctinc));
1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);
1043: exception
1044: when UTL_FILE.WRITE_ERROR then
1045: utl_file.put_line(i_file_handle,'--Error writing sql '||
1046: substr(v_sql,1,220)||'to file');
1047: utl_file.fclose(i_file_handle);
1048: raise;

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

1041: utl_file.putf(i_file_handle,'\n');
1042: utl_file.fflush(i_file_handle);
1043: exception
1044: when UTL_FILE.WRITE_ERROR then
1045: utl_file.put_line(i_file_handle,'--Error writing sql '||
1046: substr(v_sql,1,220)||'to file');
1047: utl_file.fclose(i_file_handle);
1048: raise;
1049: when UTL_FILE.INVALID_FILEHANDLE then

Line 1047: utl_file.fclose(i_file_handle);

1043: exception
1044: when UTL_FILE.WRITE_ERROR then
1045: utl_file.put_line(i_file_handle,'--Error writing sql '||
1046: substr(v_sql,1,220)||'to file');
1047: utl_file.fclose(i_file_handle);
1048: raise;
1049: when UTL_FILE.INVALID_FILEHANDLE then
1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);

Line 1049: when UTL_FILE.INVALID_FILEHANDLE then

1045: utl_file.put_line(i_file_handle,'--Error writing sql '||
1046: substr(v_sql,1,220)||'to file');
1047: utl_file.fclose(i_file_handle);
1048: raise;
1049: when UTL_FILE.INVALID_FILEHANDLE then
1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then

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

1046: substr(v_sql,1,220)||'to file');
1047: utl_file.fclose(i_file_handle);
1048: raise;
1049: when UTL_FILE.INVALID_FILEHANDLE then
1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then
1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1051: utl_file.fclose(i_file_handle);

1047: utl_file.fclose(i_file_handle);
1048: raise;
1049: when UTL_FILE.INVALID_FILEHANDLE then
1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then
1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055: utl_file.fclose(i_file_handle);

Line 1053: when UTL_FILE.INVALID_OPERATION then

1049: when UTL_FILE.INVALID_FILEHANDLE then
1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then
1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055: utl_file.fclose(i_file_handle);
1056: raise;
1057: when OTHERS then

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

1050: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then
1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055: utl_file.fclose(i_file_handle);
1056: raise;
1057: when OTHERS then
1058: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 1055: utl_file.fclose(i_file_handle);

1051: utl_file.fclose(i_file_handle);
1052: raise;
1053: when UTL_FILE.INVALID_OPERATION then
1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055: utl_file.fclose(i_file_handle);
1056: raise;
1057: when OTHERS then
1058: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1059: utl_file.fclose(i_file_handle);

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

1054: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055: utl_file.fclose(i_file_handle);
1056: raise;
1057: when OTHERS then
1058: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1059: utl_file.fclose(i_file_handle);
1060: raise;
1061: end;
1062:

Line 1059: utl_file.fclose(i_file_handle);

1055: utl_file.fclose(i_file_handle);
1056: raise;
1057: when OTHERS then
1058: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1059: utl_file.fclose(i_file_handle);
1060: raise;
1061: end;
1062:
1063: -- Rename only if we did not drop index before

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

1063: -- Rename only if we did not drop index before
1064: if (not drop_index) then
1065: -- rename orig index to old index
1066: v_old_index := substr(v_prefix||idxname, 1, 30);
1067: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||
1068: v_old_index);
1069: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1070: begin
1071: utl_file.put_line(i_file_handle,'--Execute the statement :');

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

1067: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||
1068: v_old_index);
1069: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1070: begin
1071: utl_file.put_line(i_file_handle,'--Execute the statement :');
1072: utl_file.put_line(i_file_handle,v_sql);
1073: utl_file.putf(i_file_handle,'\n');
1074: exception
1075: when UTL_FILE.WRITE_ERROR then

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

1068: v_old_index);
1069: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1070: begin
1071: utl_file.put_line(i_file_handle,'--Execute the statement :');
1072: utl_file.put_line(i_file_handle,v_sql);
1073: utl_file.putf(i_file_handle,'\n');
1074: exception
1075: when UTL_FILE.WRITE_ERROR then
1076: utl_file.put_line(i_file_handle,'--Error writing sql '||

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

1069: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1070: begin
1071: utl_file.put_line(i_file_handle,'--Execute the statement :');
1072: utl_file.put_line(i_file_handle,v_sql);
1073: utl_file.putf(i_file_handle,'\n');
1074: exception
1075: when UTL_FILE.WRITE_ERROR then
1076: utl_file.put_line(i_file_handle,'--Error writing sql '||
1077: substr(v_sql,1,220)||'to file');

Line 1075: when UTL_FILE.WRITE_ERROR then

1071: utl_file.put_line(i_file_handle,'--Execute the statement :');
1072: utl_file.put_line(i_file_handle,v_sql);
1073: utl_file.putf(i_file_handle,'\n');
1074: exception
1075: when UTL_FILE.WRITE_ERROR then
1076: utl_file.put_line(i_file_handle,'--Error writing sql '||
1077: substr(v_sql,1,220)||'to file');
1078: utl_file.fclose(i_file_handle);
1079: raise;

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

1072: utl_file.put_line(i_file_handle,v_sql);
1073: utl_file.putf(i_file_handle,'\n');
1074: exception
1075: when UTL_FILE.WRITE_ERROR then
1076: utl_file.put_line(i_file_handle,'--Error writing sql '||
1077: substr(v_sql,1,220)||'to file');
1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: when UTL_FILE.INVALID_FILEHANDLE then

Line 1078: utl_file.fclose(i_file_handle);

1074: exception
1075: when UTL_FILE.WRITE_ERROR then
1076: utl_file.put_line(i_file_handle,'--Error writing sql '||
1077: substr(v_sql,1,220)||'to file');
1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: when UTL_FILE.INVALID_FILEHANDLE then
1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);

Line 1080: when UTL_FILE.INVALID_FILEHANDLE then

1076: utl_file.put_line(i_file_handle,'--Error writing sql '||
1077: substr(v_sql,1,220)||'to file');
1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: when UTL_FILE.INVALID_FILEHANDLE then
1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then

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

1077: substr(v_sql,1,220)||'to file');
1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: when UTL_FILE.INVALID_FILEHANDLE then
1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then
1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1082: utl_file.fclose(i_file_handle);

1078: utl_file.fclose(i_file_handle);
1079: raise;
1080: when UTL_FILE.INVALID_FILEHANDLE then
1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then
1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086: utl_file.fclose(i_file_handle);

Line 1084: when UTL_FILE.INVALID_OPERATION then

1080: when UTL_FILE.INVALID_FILEHANDLE then
1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then
1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086: utl_file.fclose(i_file_handle);
1087: raise;
1088: when others then

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

1081: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then
1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086: utl_file.fclose(i_file_handle);
1087: raise;
1088: when others then
1089: utl_file.fclose(i_file_handle);

Line 1086: utl_file.fclose(i_file_handle);

1082: utl_file.fclose(i_file_handle);
1083: raise;
1084: when UTL_FILE.INVALID_OPERATION then
1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086: utl_file.fclose(i_file_handle);
1087: raise;
1088: when others then
1089: utl_file.fclose(i_file_handle);
1090: raise;

Line 1089: utl_file.fclose(i_file_handle);

1085: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086: utl_file.fclose(i_file_handle);
1087: raise;
1088: when others then
1089: utl_file.fclose(i_file_handle);
1090: raise;
1091: end;
1092: end if;
1093:

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

1091: end;
1092: end if;
1093:
1094: -- rename new index to orig index
1095: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1096: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1097: begin
1098: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099: utl_file.put_line(i_file_handle,v_sql);

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

1094: -- rename new index to orig index
1095: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1096: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1097: begin
1098: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099: utl_file.put_line(i_file_handle,v_sql);
1100: utl_file.putf(i_file_handle,' '||'\n');
1101: exception
1102: when UTL_FILE.WRITE_ERROR then

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

1095: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1096: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1097: begin
1098: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099: utl_file.put_line(i_file_handle,v_sql);
1100: utl_file.putf(i_file_handle,' '||'\n');
1101: exception
1102: when UTL_FILE.WRITE_ERROR then
1103: utl_file.put_line(i_file_handle,'--Error writing sql '||

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

1096: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1097: begin
1098: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099: utl_file.put_line(i_file_handle,v_sql);
1100: utl_file.putf(i_file_handle,' '||'\n');
1101: exception
1102: when UTL_FILE.WRITE_ERROR then
1103: utl_file.put_line(i_file_handle,'--Error writing sql '||
1104: substr(v_sql,1,220)||'to file');

Line 1102: when UTL_FILE.WRITE_ERROR then

1098: utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099: utl_file.put_line(i_file_handle,v_sql);
1100: utl_file.putf(i_file_handle,' '||'\n');
1101: exception
1102: when UTL_FILE.WRITE_ERROR then
1103: utl_file.put_line(i_file_handle,'--Error writing sql '||
1104: substr(v_sql,1,220)||'to file');
1105: utl_file.fclose(i_file_handle);
1106: raise;

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

1099: utl_file.put_line(i_file_handle,v_sql);
1100: utl_file.putf(i_file_handle,' '||'\n');
1101: exception
1102: when UTL_FILE.WRITE_ERROR then
1103: utl_file.put_line(i_file_handle,'--Error writing sql '||
1104: substr(v_sql,1,220)||'to file');
1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: when UTL_FILE.INVALID_FILEHANDLE then

Line 1105: utl_file.fclose(i_file_handle);

1101: exception
1102: when UTL_FILE.WRITE_ERROR then
1103: utl_file.put_line(i_file_handle,'--Error writing sql '||
1104: substr(v_sql,1,220)||'to file');
1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: when UTL_FILE.INVALID_FILEHANDLE then
1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);

Line 1107: when UTL_FILE.INVALID_FILEHANDLE then

1103: utl_file.put_line(i_file_handle,'--Error writing sql '||
1104: substr(v_sql,1,220)||'to file');
1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: when UTL_FILE.INVALID_FILEHANDLE then
1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then

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

1104: substr(v_sql,1,220)||'to file');
1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: when UTL_FILE.INVALID_FILEHANDLE then
1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then
1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');

Line 1109: utl_file.fclose(i_file_handle);

1105: utl_file.fclose(i_file_handle);
1106: raise;
1107: when UTL_FILE.INVALID_FILEHANDLE then
1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then
1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113: utl_file.fclose(i_file_handle);

Line 1111: when UTL_FILE.INVALID_OPERATION then

1107: when UTL_FILE.INVALID_FILEHANDLE then
1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then
1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113: utl_file.fclose(i_file_handle);
1114: raise;
1115: when others then

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

1108: utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then
1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113: utl_file.fclose(i_file_handle);
1114: raise;
1115: when others then
1116: utl_file.fclose(i_file_handle);

Line 1113: utl_file.fclose(i_file_handle);

1109: utl_file.fclose(i_file_handle);
1110: raise;
1111: when UTL_FILE.INVALID_OPERATION then
1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113: utl_file.fclose(i_file_handle);
1114: raise;
1115: when others then
1116: utl_file.fclose(i_file_handle);
1117: raise;

Line 1116: utl_file.fclose(i_file_handle);

1112: utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113: utl_file.fclose(i_file_handle);
1114: raise;
1115: when others then
1116: utl_file.fclose(i_file_handle);
1117: raise;
1118: end;
1119:
1120: else

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

1125: FROM V$PARAMETER
1126: WHERE NAME = 'cpu_count';
1127: exception
1128: when NO_DATA_FOUND then
1129: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1130: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1131: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1132: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1133: raise_application_error(-20001,'Missing view V$PARAMETER');

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

1126: WHERE NAME = 'cpu_count';
1127: exception
1128: when NO_DATA_FOUND then
1129: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1130: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1131: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1132: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1133: raise_application_error(-20001,'Missing view V$PARAMETER');
1134: when OTHERS then

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

1127: exception
1128: when NO_DATA_FOUND then
1129: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1130: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1131: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1132: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1133: raise_application_error(-20001,'Missing view V$PARAMETER');
1134: when OTHERS then
1135: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

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

1128: when NO_DATA_FOUND then
1129: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1130: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1131: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1132: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1133: raise_application_error(-20001,'Missing view V$PARAMETER');
1134: when OTHERS then
1135: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1136: raise;

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

1131: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1132: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1133: raise_application_error(-20001,'Missing view V$PARAMETER');
1134: when OTHERS then
1135: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1136: raise;
1137: end;
1138:
1139: begin

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

1142: FROM V$PARAMETER
1143: WHERE NAME IN ('parallel_max_servers','cpu_count');
1144: exception
1145: when NO_DATA_FOUND then
1146: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1147: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1148: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1149:
1150: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');

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

1143: WHERE NAME IN ('parallel_max_servers','cpu_count');
1144: exception
1145: when NO_DATA_FOUND then
1146: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1147: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1148: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1149:
1150: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1151: raise_application_error(-20001,'Missing view V$PARAMETER');

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

1144: exception
1145: when NO_DATA_FOUND then
1146: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1147: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1148: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1149:
1150: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1151: raise_application_error(-20001,'Missing view V$PARAMETER');
1152:

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

1146: utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1147: utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1148: utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1149:
1150: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1151: raise_application_error(-20001,'Missing view V$PARAMETER');
1152:
1153: when OTHERS then
1154: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');

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

1150: utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1151: raise_application_error(-20001,'Missing view V$PARAMETER');
1152:
1153: when OTHERS then
1154: utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1155: raise;
1156: end;
1157:
1158: if (tblspcname is not null) then

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

1169: where exists (select 1
1170: from USER_OBJECTS
1171: where OBJECT_NAME = v_new_index
1172: );
1173: utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||v_new_index);
1174: raise_application_error(-20012, v_new_index||' already exists.');
1175: exception
1176: when NO_DATA_FOUND then
1177: null;

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

1175: exception
1176: when NO_DATA_FOUND then
1177: null;
1178: when OTHERS then
1179: utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
1180: raise;
1181: end;
1182:
1183:

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

1180: raise;
1181: end;
1182:
1183:
1184: utl_file.put_line(i_file_handle,'--Recreating index '||idxname);
1185:
1186: -- v_initial := v_next; -- force it to uniform size
1187: -- v_initial := 1M;
1188: v_pctinc := '0';

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

1188: v_pctinc := '0';
1189: v_pctfree := '0';
1190:
1191: begin
1192: utl_file.put_line(i_file_handle,'--Create index '||v_new_index);
1193:
1194: if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
1195: v_sql := 'create unique index %s'||'\n'||
1196: ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n';

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

1260: ' on WF_ITEMS (HA_MIGRATION_FLAG, ITEM_TYPE, ITEM_KEY)'||'\n';
1261: v_initial := 1048576; -- 1M
1262: end if;
1263:
1264: utl_file.putf(i_file_handle,v_sql,v_new_index);
1265: utl_file.fflush(i_file_handle);
1266:
1267: v_sql := ' pctfree %s'||'\n'||
1268: ' initrans %s'||'\n'||

Line 1265: utl_file.fflush(i_file_handle);

1261: v_initial := 1048576; -- 1M
1262: end if;
1263:
1264: utl_file.putf(i_file_handle,v_sql,v_new_index);
1265: utl_file.fflush(i_file_handle);
1266:
1267: v_sql := ' pctfree %s'||'\n'||
1268: ' initrans %s'||'\n'||
1269: ' tablespace %s'||'\n'||

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

1267: v_sql := ' pctfree %s'||'\n'||
1268: ' initrans %s'||'\n'||
1269: ' tablespace %s'||'\n'||
1270: ' storage (initial %s next %s'||'\n';
1271: utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,
1272: v_tablespace_name,to_char(v_initial),to_char(v_next));
1273: utl_file.fflush(i_file_handle);
1274: v_sql := ' freelists 2 freelist groups %s'||'\n'||
1275: ' pctincrease 0)'||'\n'||

Line 1273: utl_file.fflush(i_file_handle);

1269: ' tablespace %s'||'\n'||
1270: ' storage (initial %s next %s'||'\n';
1271: utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,
1272: v_tablespace_name,to_char(v_initial),to_char(v_next));
1273: utl_file.fflush(i_file_handle);
1274: v_sql := ' freelists 2 freelist groups %s'||'\n'||
1275: ' pctincrease 0)'||'\n'||
1276: ' parallel '||'\n'||
1277: ' logging;'||'\n';

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

1275: ' pctincrease 0)'||'\n'||
1276: ' parallel '||'\n'||
1277: ' logging;'||'\n';
1278:
1279: utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);
1280: utl_file.fflush(i_file_handle);
1281:
1282: exception
1283: when OTHERS then

Line 1280: utl_file.fflush(i_file_handle);

1276: ' parallel '||'\n'||
1277: ' logging;'||'\n';
1278:
1279: utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);
1280: utl_file.fflush(i_file_handle);
1281:
1282: exception
1283: when OTHERS then
1284: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

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

1280: utl_file.fflush(i_file_handle);
1281:
1282: exception
1283: when OTHERS then
1284: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1285: raise;
1286: end;
1287:
1288: begin

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

1285: raise;
1286: end;
1287:
1288: begin
1289: utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');
1290: v_sql := 'alter index %s'||'\n'||
1291: ' noparallel'||'\n'||
1292: ' logging;'||'\n';
1293: utl_file.putf(i_file_handle,v_sql,v_new_index);

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

1289: utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');
1290: v_sql := 'alter index %s'||'\n'||
1291: ' noparallel'||'\n'||
1292: ' logging;'||'\n';
1293: utl_file.putf(i_file_handle,v_sql,v_new_index);
1294: utl_file.fflush(i_file_handle);
1295: exception
1296: when OTHERS then
1297: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));

Line 1294: utl_file.fflush(i_file_handle);

1290: v_sql := 'alter index %s'||'\n'||
1291: ' noparallel'||'\n'||
1292: ' logging;'||'\n';
1293: utl_file.putf(i_file_handle,v_sql,v_new_index);
1294: utl_file.fflush(i_file_handle);
1295: exception
1296: when OTHERS then
1297: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1298: raise;

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

1293: utl_file.putf(i_file_handle,v_sql,v_new_index);
1294: utl_file.fflush(i_file_handle);
1295: exception
1296: when OTHERS then
1297: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1298: raise;
1299: end;
1300:
1301: -- gathering CBO stats

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

1298: raise;
1299: end;
1300:
1301: -- gathering CBO stats
1302: UTL_FILE.PUT_LINE(I_FILE_Handle,'--Gathering CBO Stats on new index '||
1303: v_new_index);
1304: -- begin
1305: -- apps.fnd_stats.gather_index_stats(ownname=>'APPLSYS',
1306: -- indname=>v_new_index);

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

1308: -- end;
1309:
1310: -- rename orig index to old index
1311: v_old_index := 'OO'||substr(idxname,3,28);
1312: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);
1313: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1314:
1315: begin
1316: utl_file.put_line(i_file_handle,v_sql);

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

1312: utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);
1313: v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1314:
1315: begin
1316: utl_file.put_line(i_file_handle,v_sql);
1317: exception
1318: when OTHERS then
1319: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1320: raise;

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

1315: begin
1316: utl_file.put_line(i_file_handle,v_sql);
1317: exception
1318: when OTHERS then
1319: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1320: raise;
1321: end;
1322:
1323: -- rename new index to orig index

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

1320: raise;
1321: end;
1322:
1323: -- rename new index to orig index
1324: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1325: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1326: begin
1327: utl_file.put_line(i_file_handle,v_sql);
1328: exception

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

1323: -- rename new index to orig index
1324: utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1325: v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1326: begin
1327: utl_file.put_line(i_file_handle,v_sql);
1328: exception
1329: when OTHERS then
1330: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1331: raise;

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

1326: begin
1327: utl_file.put_line(i_file_handle,v_sql);
1328: exception
1329: when OTHERS then
1330: utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1331: raise;
1332: end;
1333:
1334: end if;

Line 1336: utl_file.fclose(i_file_handle);

1332: end;
1333:
1334: end if;
1335:
1336: utl_file.fclose(i_file_handle);
1337: exception
1338: when OTHERS then
1339: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1340: utl_file.fclose(i_file_handle);

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

1335:
1336: utl_file.fclose(i_file_handle);
1337: exception
1338: when OTHERS then
1339: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1340: utl_file.fclose(i_file_handle);
1341: raise;
1342: end CreateIndex;
1343:

Line 1340: utl_file.fclose(i_file_handle);

1336: utl_file.fclose(i_file_handle);
1337: exception
1338: when OTHERS then
1339: utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1340: utl_file.fclose(i_file_handle);
1341: raise;
1342: end CreateIndex;
1343:
1344: PROCEDURE Start_partition(p_tablespace in varchar2,