Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '12 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 '12 at 14:21
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.