DML in Access DB by Oracle RDBMS (Oracle 10g in Windows 2003)

VIEW DATA FROM MICROSOFT ACCESS 2003 AT ORACLE 10G RELEASE 1 (WIN 2003) :

STEP 1:

CREATE A MICROSOFT ACCESS TABLE : my_access_tab

STEP 2:

CREATE AN ODBC LOCAL SYSTEM DSN AND SELECT ACCESS *.MDB FILE PREVIOUSLY CREATED AND
GIVE USER NAME AND PASSWORD IN ADVANCED TAB.


STEP 3:

INSTALL ORACLE 10G RELEASE 1 IN WINDOWS 2003.


STEP 4:


GO TO: E:\oracle\product\10.1.0\db_1\NETWORK\ADMIN

CONFIGURE LISTENER.ORA AS BELOW:

# listener.ora Network Configuration File: E:\oracle\product\10.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = u03444)(PORT = 1521))
      )
    )
  )


SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = hsodbc)
   (ORACLE_HOME = E:\oracle\product\10.1.0\db_1)
   (PROGRAM = hsodbc)
  )
)


NOW STOP AND START LISTENER SERVICE:

LSNRCTL STOP

LSNRCTL START

STEP 5:

CONFIGURE ORACLE HS (HETEREGENEOUS SERVICE) AT : E:\oracle\product\10.1.0\db_1\hs\admin\inithsodbc.ora

AS:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = odbc1
HS_FDS_TRACE_LEVEL = off


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>


STEP 6:

GO TO : E:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\tnsnames.ora

add :

access_db.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = u03444)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SID = hsodbc)
    )
    (HS = OK)
  )


Step 7:


connect to any user and create DATABASE LINK:

DROP PUBLIC DATABASE LINK "access_db";

CREATE PUBLIC DATABASE LINK "access_db" connect to TEST identified by "TEST" using 'access_db.world';

SELECT * FROM my_access_tab@access_db;


create or replace view v_access as SELECT * FROM my_access_tab@access_db;


CREATE OR REPLACE TRIGGER TRIG_ACCESS_DML_AFTER
INSTEAD OF INSERT
ON V_ACCESS FOR EACH ROW
DECLARE
  V_ALTTYPE CHAR(1);
BEGIN
    V_ALTTYPE:='I';
    INSERT INTO PADMA.T_ACCESS
    VALUES (:NEW.ID,:NEW.FIELD1,:NEW.FIELD2);
END;
/