We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a private database link without credentials to pull data from the second database into the first.
We migrated the job to DBMS_SCHEDULER, but the job fails with the following error:
ORA-04052: error occurred when looking up remote object PATIENT.PTTRANS@BILL10G ORA-00604: error occurred at recursive SQL level 2 ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from BILL10G
I haven't found definitive information, but the things I have read seem to indicate that since scheduler jobs do not have a login, they cannot use database links that use passed credentials. That would make sense since I get the same error whether the job is owned by SYSTEM or by the user that normally runs the block (and still can when logged in).
Can anyone provide further explanation of this situation or more importantly explain ways to work around this issue? I can think of several, but I'm not sure I like any of them.