Scheduling jobs

From: <todd_at_cardinalgroup.com>
Date: 2000/05/15
Message-ID: <8fpp2n$45a$1@nnrp1.deja.com>#1/1

I am trying to schedule a job that will rebuild indexes every 15 minutes or so. It appears that it is failing when I look at user_jobs for this scheduled job. I am able to run the job using execute dbms_job.run (job_num) but when I job is run normally, it fails. I can also run the stored procedure without incident. Any help would be much appreciated.

Here is a snippet of the code...
create or replace procedure rebuildIndexes is

	l_stmt varchar2(4000);
	v_JobNum NUMBER;

begin

        insert into intermedia_rebuild(time_stamp,textField) values
(sysdate, 'START');

	DBMS_OUTPUT.PUT_LINE ('BUILDING AB TITLE');
	begin
		l_stmt := 'ALTER INDEX   indx_ab_title  REBUILD online
PARAMETERS(''sync'')';
		EXECUTE IMMEDIATE l_stmt;
	end;

	DBMS_OUTPUT.PUT_LINE ('BUILDING CONTENT');
	insert into intermedia_rebuild(time_stamp,textField) values

(sysdate, 'TITLE');

	begin
		l_stmt := 'ALTER INDEX   indx_ab_content  REBUILD
online PARAMETERS(''sync'')';
		EXECUTE IMMEDIATE l_stmt;
	end;

	DBMS_OUTPUT.PUT_LINE ('BUILDING SHERLOCK');
	insert into intermedia_rebuild(time_stamp,textField) values

(sysdate, 'CONTENT');

	begin
		l_stmt := 'ALTER INDEX   indx_sherlock  REBUILD online
PARAMETERS(''sync'')';
		EXECUTE IMMEDIATE l_stmt;
	end;
	DBMS_OUTPUT.PUT_LINE ('BUILDING SOURCE');
	insert into intermedia_rebuild(time_stamp,textField) values


(sysdate, 'SHERLOCK');

	begin
		l_stmt := 'ALTER INDEX   indx_source  REBUILD online
PARAMETERS(''sync'')';
		EXECUTE IMMEDIATE l_stmt;
	end;

	insert into intermedia_rebuild(time_stamp,textField) values

(sysdate, 'SOURCE');

        commit;
end;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 15 2000 - 00:00:00 CDT