Home » SQL & PL/SQL » SQL & PL/SQL » DB Link Job error (Oracle Database 10g Express Edition Release 10.2.0.1.0, UNIX)
DB Link Job error [message #395246] Tue, 31 March 2009 21:19 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi experts,

Please bear with my lengthy explanation. I'm having a problem with scheduling a job in TOAD..

First i have this DB Link:

Select * from user_db_links

DB_LINK	USERNAME	PASSWORD	HOST	CREATED

MyDBLINK.blah.COM			MyDBLINK	7/5/2008 4:07:02 PM


Select * from TABLE3@MyDBLINK;

-- Ok it returns records --

Something1 Something2
123        abc
456        def


I have a procedure (MyProc1) that calls a function (MyFunc1).. MyFunc1 has this sample codes:

CREATE OR REPLACE FUNCTION MyFunc1 (v_addr_id NUMBER)
RETURN NUMBER
IS
.
.
d_b_id  TABLE1.b_id@MyDBLINK%TYPE;
d_ga_id TABLE2.ga_id@MyDBLINK%TYPE;

Begin
.
.
IF ... THEN

          INSERT INTO TABLE3@MyDBLINK
           (something1,
            something2
            )
            VALUES
           (value1,
            value2
            );
            COMMIT;
ELSIF ... THEN
          UPDATE TABLE3@MyDBLINK
           SET ...
           .
           .
          WHERE ...
          COMMIT;

END IF;
END;


if I execute the procedure (MyProc1) manually, it runs properly and generates an output file like this:

Begin
MyProc1;
End;

-- Logs --

* FILE: File1.TXT EVENT: I A_ID: 100
* FILE: File1.TXT EVENT: I A_ID: 101
* FILE: File2.TXT EVENT: U E_A_ID: 201
* FILE: File2.TXT EVENT: I E_A_ID: 205
* FILE: File2.TXT EVENT: U E_A_ID: 205


While if I create a job in TOAD, specifying the exact time and the procedure to execute (MyProc1)..

it returns this error in my Logs:

* ROLLBACK - Message: ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MyDBLINK -1017


ORA-01017: invalid username/password; logon denied 

Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is: username/password. 

Action: Enter a valid username and password combination in the correct format.  

---

ORA-02063: preceding stringstring from stringstring 

Cause: an Oracle error was received from a remote database link. 

Action: refer to the preceding error message(s)  


select * from user_sys_privs

USERNAME	PRIVILEGE	ADMIN_OPTION

MySchema	GRANT ANY PRIVILEGE	NO
MySchema	CREATE SESSION	        NO
MySchema	UNLIMITED TABLESPACE	NO
MySchema	CREATE DATABASE LINK	NO
MySchema	UPDATE ANY TABLE	NO
MySchema	CREATE EXTERNAL JOB	NO
MySchema	CREATE TABLE	        NO
MySchema	INSERT ANY TABLE	NO
MySchema	CREATE ANY TRIGGER	NO
MySchema	CREATE ANY SYNONYM	NO
MySchema	CREATE PUBLIC SYNONYM	NO
MySchema	CREATE USER	        NO
MySchema	SELECT ANY TABLE	NO
MySchema	CREATE SYNONYM	        NO
MySchema	CREATE VIEW	        NO
MySchema	CREATE JOB	        NO
MySchema	DROP ANY TABLE	        NO
MySchema	DROP ANY SYNONYM	NO
MySchema	GRANT ANY ROLE	        NO
MySchema	ALTER USER	        NO
MySchema	DROP ANY ROLE	        NO
MySchema	EXECUTE ANY PROCEDURE	NO
MySchema	DROP PUBLIC DATABASE LINK	NO
MySchema	CREATE PUBLIC DATABASE LINK	NO
MySchema	DROP PUBLIC SYNONYM	NO
MySchema	CREATE ROLE	        NO

---

Select * from user_db_links

DB_LINK	USERNAME	PASSWORD	HOST	CREATED

MyDBLINK.blah.COM			MyDBLINK	7/5/2008 4:07:02 PM


I'm still reading more on DB LINK and Privileges, yet still to no avail.. Confused Please help.. Thanks for the time!

Regards,
Wilbert

[Updated on: Tue, 31 March 2009 21:26]

Report message to a moderator

Re: DB Link Job error [message #395247 is a reply to message #395246] Tue, 31 March 2009 21:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Decent post with the exception you neglected to actually post the code which throws the error.

Start a terminal window.
invoke sqlplus
--show creation of MyProc1 procedure & then execute it
Begin
MyProc1;
End;
exit

Now using CUT & PASTE show use the whole SQL*Plus session;
including everything you type & EXACTLY how Oracle responded.
Re: DB Link Job error [message #395248 is a reply to message #395246] Tue, 31 March 2009 21:39 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
I forgot to mention if I comment out the MyFunc1 inside my procedure MyProc1, the job runs smoothly generates logs, inserts/updates other tables except it doesn't inserts/updates to the TABLE3@MyDBLINK
table, coz i comment out MyFunc1 (which does those inserts/updates to the TABLE3@MyDBLINK)..

@BlackSwan, thanks for the reply.. Will try to post MyProc1 asap..

Regards,
Wilbert

[Updated on: Tue, 31 March 2009 21:40]

Report message to a moderator

Re: DB Link Job error [message #395250 is a reply to message #395246] Tue, 31 March 2009 22:15 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Please clarify your environment.
Provide names for all systems involved.
Enumerate which schemas reside on which system by names.
Enumerate which functions & procedures exist in which schemas on which systems.

You could have problem if/when username/passwords need to be inputted/provided at run time.
How or from where does the username/password be provided to internally scheduled job?
Previous Topic: Declaring a Variable within a Function
Next Topic: Instead of Connect by prior
Goto Forum:
  


Current Time: Sat Dec 10 18:40:03 CST 2016

Total time taken to generate the page: 0.26865 seconds