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

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;

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

* 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

SELECT USER, COUNT(*) FROM USER_OBJECTS;

USER      COUNT(*)

MySchema  4560

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

USER      COUNT(*)

MySchema  1225

desc TABLE3@MyDBLINK;

-- Grants --

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

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;