ORA-04052: error occurred when looking up remote object

I am getting error when I user DB_LINK

0/0      ORA-04052: error occurred when looking up remote object
         SCOTT.PASS_REP@TOM.target.COM
         ORA-00604: error occurred at recursive SQL level 1
         ORA-28000: the account is locked
         ORA-02063: preceding line from TOM.TARGET.COM

Any help

Thanks
CREATE OR REPLACE PROCEDURE SCOTT.change_pass (
   p_username     VARCHAR2,
   p_password     VARCHAR2,
   old_password   VARCHAR2
)
IS
   return_value   BOOLEAN;
   v_password     VARCHAR2 (30);
   v_host         VARCHAR2 (30) := SUBSTR (SYS_CONTEXT ('USERENV', 'SERVER_HOST'), 1, 3);
   v_sqlerrm      VARCHAR2 (32000);
   v_dblink       VARCHAR2 (30);
BEGIN
   EXECUTE IMMEDIATE    'ALTER USER '
                     || p_username
                     || ' identified by '
                     || p_password
                     || ' replace '
                     || old_password;
 
   SELECT PASSWORD
     INTO v_password
     FROM dba_users
    WHERE username = p_username;
 
   IF v_host = 'san'
   THEN
 
         INSERT INTO SCOTT.pass_rep@sandi.target.com
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, NULL
                  );   
   
   ELSE
      INSERT INTO SCOTT.pass_rep@tom.target.com
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, NULL
                  );   
   END IF;
   
   COMMIT;
--  RETURN (NULL);
EXCEPTION
   WHEN OTHERS
   THEN
      v_sqlerrm := SQLERRM;
 
      INSERT INTO SCOTT.pass_rep
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, v_sqlerrm
                  );
 
      COMMIT;
END;
/
 
ERROR
=====
 
0/0      ORA-04052: error occurred when looking up remote object
         SCOTT.PASS_REP@TOM.target.COM
         ORA-00604: error occurred at recursive SQL level 1
         ORA-28000: the account is locked
         ORA-02063: preceding line from TOM.TARGET.COM

Open in new window

basiranaAsked:
Who is Participating?
 
Shaju KumbalathConnect With a Mentor Deputy General Manager - ITCommented:
login to remote db
alter user   SCOTT  account unlock;
0
 
johnsoneSenior Oracle DBACommented:
I would guess that this is your real error:

ORA-28000: the account is locked
0
 
basiranaAuthor Commented:
yes thats a real error when I try to compile
0
 
basiranaAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.