Home » SQL & PL/SQL » Client Tools » DB Link TOAD Job error (Oracle Database 10g Express Edition Release 10.2.0.1.0, UNIX)
DB Link TOAD Job error [message #398020] Tue, 14 April 2009 22:00 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
/*** NOTE ***/
Your session has expired. Please re-submit the form. Sorry for the inconvenience.

-- Refreshed the client tools thread to check if it was still created but its not, so reposting.. hope it wont create a duplicate thread...

/*** END ***/

Hi Experts,

* Since my problem is with TOAD I'm reposting this to a more appropriate thread 'client tools'.. Though our DBA is working on this and I'm still waiting for a feedback, a case of permission issues perhaps? here it goes...

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

-- TOAD v 8.6.0.38 --

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 think the problem is not with my procedure but with the function that calls a DB LINK.

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, well its obviously because i commented out MyFunc1 (which does those inserts/updates to the TABLE3@MyDBLINK)..

Please help.. Thanks for the time!

Regards,
Wilbert
Re: DB Link TOAD Job error [message #398021 is a reply to message #398020] Tue, 14 April 2009 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Privileges acquired via ROLE, do not apply within named PL/SQL procedures.

An alternative which is less complicated from a permission standpoint,
is to create a PL/SQL procedure in the remote database owned by the same schema as owns the table against which the INSERT acts.

Essentially you pull the data into the desired table as opposed to pushing the data from the remote database.

[Updated on: Tue, 14 April 2009 22:22]

Report message to a moderator

Re: DB Link TOAD Job error [message #398273 is a reply to message #398021] Wed, 15 April 2009 20:55 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi BlackSwan,

Thanks for the suggestion. It will work that way. Its just that my main table and look-up tables are on different databases and I have to do an Insert or Update on them. Will try to do it the way you suggested. And then there is also the ROLE issue, as you pointed out. Thanks.

Regards,
Wilbert
Re: DB Link TOAD Job error [message #398274 is a reply to message #398020] Wed, 15 April 2009 21:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Your environment is "complicated".
The solution depends upon the details.
Which schema owns tables & procedures on NODE1?
Which schema owns tables & procedures on NODE2?
Again, privileges acquired via ROLE do not apply to SQL within PL/SQL named procedures.
It can be made to work, but exactly what needs to change is TBD.


SELECT USER, COUNT(*) FROM USER_OBJECTS;
SELECT USER, COUNT(*) FROM USER_OBJECTS@MyDBLINK;

Use CUT & PASTE to post SQL & results.

[Updated on: Wed, 15 April 2009 21:18]

Report message to a moderator

Re: DB Link TOAD Job error [message #398276 is a reply to message #398274] Wed, 15 April 2009 21:58 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi BlackSwan,

Thanks for the reply.

> Which schema owns tables & procedures on NODE1?
> Which schema owns tables & procedures on NODE2?

What do you exactly mean by Node?

Procedure MyProc1 and function MyFunc1 were made using the scheme MySchema. MySchema also has my Main table and other look-up table, except for the look-up tabale that uses my DBLink TABLE1@MyDBLINK, TABLE2@MyDBLINK, TABLE3@MyDBLINK.

SELECT USER, COUNT(*) FROM USER_OBJECTS;

USER      COUNT(*)

MySchema  4560

SELECT USER, COUNT(*) FROM USER_OBJECTS@MyDBLINK;

USER      COUNT(*)

MySchema  1225


For now, I dont know much on being DBA (I'm a jr. developer here) and dont know if this info will help...

desc TABLE3@MyDBLINK;

-- Grants --

GRANTED TO: MySchema_READ
PRIVILEDGE: SELECT
COLUMN:
GRANT OPTION?: N
GRANTOR: MySchema


Regards,
Wilbert
Re: DB Link TOAD Job error [message #398280 is a reply to message #398020] Wed, 15 April 2009 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>What do you exactly mean by Node?
NODE is the same as system or host name.
Relatively speaking you have a local system & a remote system.
The problem is knowing your basis of observation.
Please name your systems & always use their names when discussing them.


>ORA-01017: invalid username/password; logon denied
When was the last time the code worked without error?
What changed between then & now?

Personally I don't use TOAD, so it would help if you could use SQL*Plus while troubleshooting this.

Can you post the procedure which generates the error & identify the line which causes the error?
Re: DB Link TOAD Job error [message #398327 is a reply to message #398280] Thu, 16 April 2009 00:59 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi BlackSwan,

Thanks again for the reply.

> When was the last time the code worked without error?
> What changed between then & now?

I built and tested my procedure (MyProc1) in our dev environment using manual execution..

Begin
MyProc1;
End;

Now its ready for production, i tried to create and schedule a job in TOAD and even trying it with dbms_job.submit, i kept encountering that error.

However after creating a job, a job is created then if i DONT wait for it to execute automatically, executing it by force execution, right click > execute job, the job executes properly. Scheduling a job and waiting for it to execute automatically it flags that error in my transaction logs.

> Can you post the procedure which generates the error & identify the line which causes the error?

My procedure just does verification of a code from a file is present to my look up tables then its valid, if not it writes the error in my error logs else in my good logs. To read the files I used an external table.

Essentially the last Exception prompts the error, rollbacks the transaction and overwrites and writes to my good and error logs, erasing previous transactions made..


CREATE or replace PROCEDURE MyProc1 IS 
.
.
BEGIN
.
.
-- Java program inserts the files found in my directory to a table.. A cursor stores the filenames in an array (va_filename)
.
.
.
FOR i 1 .. v_array_filename.count LOOP

-- External table reads the files --

EXECUTE IMMEDIATE 'ALTER TABLE EXTRNL_TAB LOCATION ('''||va_filename(i)||''')';

-- Validations with exception handling and error logging
-- if the code is valid then update or insert to my main table
-- then write/append to my good trnasactions logging.--
.
.
-- For an Insert to  to my main table MyFunc1 function is called, return 0 if data is found
-- else return 1 is NO_DATA_FOUND exception encountered in my function then write/append to my error logs stating
-- that no association for that row in MyFunc1 then proceeds

END LOOP;
.
.
 EXCEPTION when OTHERS then  
  ROLLBACK;
  ErrorFile := utl_file.fopen('MASTER_ADD_ORA', v_outfilename,'W', 32767);
  GoodFile  := utl_file.fopen('MASTER_ADD_ORA', v_outfilename2,'W', 32767);
  utl_file.put(ErrorFile,'* ROLLBACK - Message: '||SQLERRM||' '||SQLCODE);
  utl_file.put(GoodFile,'* ROLLBACK - Message: '||SQLERRM||' '||SQLCODE);
utl_file.fflush(ErrorFile);                                                    utl_file.fclose(ErrorFile);
utl_file.fflush(GoodFile);                                                     
utl_file.fclose(GoodFile);

END;


I hope I answered your inquiry...

Regards,
Wilbert

[Updated on: Thu, 16 April 2009 01:02]

Report message to a moderator

Previous Topic: PCTFREE too low for a table
Next Topic: HOST Command
Goto Forum:
  


Current Time: Tue Dec 06 08:16:56 CST 2016

Total time taken to generate the page: 0.08046 seconds