Newbie - Setting up jobs across link

[atcolyandro]

I have a technical question.

I would like to learn how to set up a weekly scheduled job to run over a link from one database to another. I'm using Oracle 9.2.0.7 on both servers using PL/SQL Developer (see configuration below)

I don't know how to do this, since I've never set up a job before, nor a job over a link.

I tried to set this up on my own, but I've never done it before and I think I'm having issues with doing this over a link.

Here is the information:

1. I make a database link from database A to database B using

create database link ........ yada yada yada ...............................

2. Then I want to set up a weekly, early Monday morning job on PL/SQL developer using the job scheduler.

When I do this manually on database A using PL/SQL Developer it runs fine. When I set up a job using the job scheduler wizard in PL/SQL developer nothing happens.

I just cut and paste my manual query into the job scheduler, set up the times, but yet doesn't run.

Is there some issue that I need to take into account since this is linked databases?

I would appreciate any help.

Thanks, Anthony

PL/SQL Developer
Version 7.0.1.1066 (MBCS)
01.0957 - Unlimited user license
Windows XP version 6.0 (build 2600) Service Pack 2

Physical memory : 2,097,152 kB (2,004,124 available)
Paging file : 4,194,304 kB (4,194,304 available)
Virtual memory : 2,097,024 kB (1,982,628 available)

Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Plug-Ins

Homes
OraHome81 (C:\Oracle\Ora81)

DLLs
C:\Oracle\Ora81\bin\oci.dll

TNS File
.............

Using
Home: OraHome81
DLL: C:\Oracle\Ora81\bin\oci.dll
OCI: Version 8.1

Character size: 1 byte(s)

 

[Marco Kalter]

I'm not sure what goes wrong, but if you use PL/SQL Developer's Job Editor, you can press the "View SQL" button to verify what is executed. Maybe you can compare this to your manual job creation script to verify things.

 

[atcolyandro]

Thanks for the reply. I've tried this, and while there are some obvious differences, it doesn't explain the issue. Any other ideas?

 

[Marco Kalter]

If you run the viewed SQL manually, does it also lead to the "nothing happens" situation you mentioned?

 

[atcolyandro]

No, it runs just fine. That's the odd part. It runs fine when I open a SQL window and then execute the code, but when it is in Job mode, nothing happens.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the debugsql parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.

 

[atcolyandro]

Thanks, I'll work on it and get back to you with the results.