Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_Scheduler (10g)
DBMS_Scheduler [message #288517] Mon, 17 December 2007 22:15 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I am using DBMS_Scheduler to insert data from one database (A) to another database(B).
I have created an job in A and written a insert statement in this.
I have created a Public Database Link to point to B.
But when I am running the Job,the data is not getting populated in database B although the Job runs successfully.
There is no error in log table also.
Can anyone help me on this ?

Regards
Re: DBMS_Scheduler [message #288525 is a reply to message #288517] Mon, 17 December 2007 22:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>But when I am running the Job,the data is not getting >populated in database B although the Job runs successfully.
Did you check your database link is working.
first run your job manually and see what happen.
Re: DBMS_Scheduler [message #288546 is a reply to message #288517] Tue, 18 December 2007 00:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add debug logging so you can follow the process.
Write logmessages to a table by using an autonomous transaction to keep track of what happens in your procedure.
And post your code; how can we help you if we don't know what you do?
Re: DBMS_Scheduler [message #288553 is a reply to message #288546] Tue, 18 December 2007 00:28 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks Taj.
My dblink is working fine and i am able to insert data into the table using this directly.

Frank,here is the code I am using .Its a very simple code.I am using DBMS_SCHEDULER for the first time and probably missing some minute thing.

Quote:


DBMS_SCHEDULER.create_job
(job_name => 'INSERT_DATA',
repeat_interval => 'FREQ=MINUTELY',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
BEGIN
insert into test_scheduler@dblink1 values(''Vishal'',to_date(''20071218'',''YYYYMMDD''));
commit;
END;',
enabled => TRUE,
comments => 'Insert data '
);




waiting for your reply.

regards
Re: DBMS_Scheduler [message #288879 is a reply to message #288553] Wed, 19 December 2007 00:22 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
I am still not able to do this.
Can anybody help me on my problem ?

regards
Re: DBMS_Scheduler [message #288911 is a reply to message #288879] Wed, 19 December 2007 00:57 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
just check if this works for you.

dbms_scheduler.create_job
(
	job_name=>'insert_data1',
	job_type=>'PLSQL_BLOCK',
	job_action=>'begin 
			insert into test_scheduler@dblink1 
			values(''Vishal'',to_date(''20071218'',''yyyymmdd''));
			commit;
	             end;',
	start_date=>systimestamp,
	repeat_interval=>'FREQ=MINUTELY;',
	enabled=>true,
	comments=>'insert data'
);


regards,
Re: DBMS_Scheduler [message #288929 is a reply to message #288517] Wed, 19 December 2007 01:31 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks dhananjay for your reply.
Its not inserting any data still although the job is running without any error.
i have already checked all the three tables namely dba_scheduler_jobs,dba_scheduler_job_log and DBA_SCHEDULER_JOB_RUN_DETAILS and these tables are also showing that job worked perfectly.

regards
Re: DBMS_Scheduler [message #288943 is a reply to message #288929] Wed, 19 December 2007 01:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you sure you check the correct table? Did you check the inserts over the dblink or from within the remote database?
Re: DBMS_Scheduler [message #288950 is a reply to message #288517] Wed, 19 December 2007 02:17 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Frank,
I have done it both ways and its working fine.
I have even done insertion using only the insert statement via dblink and its working fine.

regards
Re: DBMS_Scheduler [message #288957 is a reply to message #288950] Wed, 19 December 2007 02:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I meant, how do you check whether the insert succeeded. Do you do that from the remote database.
In short, what I mean to say is make sure you run your check on the correct database/schema/table.
This may sound obvious but it happens only too often that it turns out the dblink points to another db/schema combination than expected.
Re: DBMS_Scheduler [message #289043 is a reply to message #288517] Wed, 19 December 2007 06:02 Go to previous message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks Frank and sorry for the delay.
I have tested from both remote and personal system.
Also,the database link is properly configured and is pointing to the proper schema and table.
Interestingly,DBMS_JOB works perfectly in this scenario.

I have no idea what is happening

regards
Previous Topic: selecting only duplicate records
Next Topic: REMOVING LAST 4 CHARACTERS FROM STRING( IMPORTANT )
Goto Forum:
  


Current Time: Sun Dec 04 02:23:20 CST 2016

Total time taken to generate the page: 0.12664 seconds