What would you learn in this session:
1.Attendance Generations (using DBlinks to get data from access DB).
2.DBLink from another DB.
3.Get data from Excel and Access DB.
http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc-windows.html
In Oracle Database 11g:
http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc-windows.html
listener:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=fb)
(ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM=dg4odbc)
)
)
Present Situation:
# listener.ora Network Configuration File: e:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = e:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:e:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = ORCL)
)
(SID_DESC=
(SID_NAME=fb)
(ORACLE_HOME=e:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-S0P4BCV)(PORT = 1521))
)
)
tnsnames.ora
fb_connection=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=noor-PC)(PORT=1521))
(CONNECT_DATA= (SID=fb))
(HS=OK)
)
copy C:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora
to C:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initfb.ora (fb is the database name.)
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = attendence
#HS_FDS_TRACE_LEVEL = off
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
set tns_admin=c:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\
CREATE OR REPLACE procedure p_gen_att (pdt date) is
cursor c is select empno from emp;
begin
for r in c loop
insert into attendence
(empno, dt, inout)
values
(r.empno, pdt, '10:00:00');
insert into attendence
(empno, dt, inout)
values
(r.empno, pdt, '18:05:00');
end loop;
end;
/
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 'fb_connection';
SELECT * FROM attendence@access_db;
Get data from external oracle Database:
DROP PUBLIC DATABASE LINK "hr_xe";
CREATE PUBLIC DATABASE LINK "hr_xe" connect to HR identified by "HR" using 'XE';
VIEW DATA FROM MICROSOFT ACCESS 2003 AT ORACLE 10G RELEASE 1
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)
)
set tns_admin=E:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
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 'fb_connection';
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;
/
