5

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.

Improve this question
2
  • 2
    not seen this one myself Leigh, most dblinks I've seen rely on hard coded passwords (which have their own issues).
    – Niall Litchfield
    Feb 8, 2012 at 13:46
  • @Niall Thanks for the comment. I've used both, but this is the first time with dbms_scheduler.
    – Leigh Riffel
    Feb 8, 2012 at 14:21

1 Answer 1

Reset to default
2

The only way to get around this was to use credentials in the database links definition. Not nice if you ask me but it is what I found. This was in 10g and 11, did not test with 12c.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.