Create Oracle ODBC

example using Access instead of MS SQL Server.

NOTE: No guarantees whatsoever. This example just shows how to connect to an Access database from within Forms. I strongly suggest that you do this from the database and not from forms directly. But for the sake of the example, here goes:

Environment:
•WIndows XP

•Oracle 10g XE

•MS Access 2003

•Forms 10g


STEP I:
Create a simple access database. In this example, it is "C:\orafaq\Orafaq.mdb". This database contains a single table, called "emp" with the columns "ename" and "empno".

STEP II:
In the ODBC Data source administrator, you need to add a System DSN pointing to this database. So you select "MS Access Driver" and add the name "OrafAccess" in the data source name. Make sure you've added the correct .mdb file (orafaq.mdb)

STEP III:
Now we set up the HS init file: it's name is fixed (init.ora). It contains only two lines of code:

$ORACLE_HOME/hs/admin/initOrafAccess.ora:

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = OraFaccess
HS_FDS_TRACE_LEVEL = 0


STEP IV:
Next, I changed the listener.ora of my XE database:
$ORACLE_HOME/network/admin/listener.ora:
added in SID_LIST:

    (SID_DESC =
         (SID_NAME = OrafAccess)
         (ORACLE_HOME = )
         (program = hsodbc)
        )

STEP V:
$ORACLE_HOME/network/admin/tnsnames.ora:
The setup of the HSODBC is done, and we need to make it accessible for our Oracle users. Like any other database there has to be an entry in the TNSNAMES.ORA file:

ORAFACCESS =
   (description =
    (address = (protocol=tcp)(host=localhost)(port=1521))
     (connect_data = (sid=orafaccess))
     (hs=ok)
   )

STEP VI:
bounce the listener. You can do this befor step V, but I just did it in this order.

STEP VII:
SQL*Plus:


SQL> create database link orafaccess using 'ORAFACCESS';

Database link created.

SQL> desc emp@orafaccess
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 empno                                              NUMBER(10)
 ename                                              VARCHAR2(50)

SQL> create synonym access_emp for emp@orafaccess;

Synonym created.

SQL> desc access_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 empno                                              NUMBER(10)
 ename                                              VARCHAR2(50)

SQL>

STEP VIII:
In Forms we make a basic form:
- data block wizard
- table Access_emp
- key mode UPDATEABLE (rowid won't work)
- empno = primary key
- layout wizard
- tabular design

I added double quotes (") around the column names in the property palette. I also added the following triggers:
ON-COMMIT:

BEGIN
   COMMIT_FORM;
END;
ON-LOCK:

BEGIN
   NULL;
END;
ON-DELETE:

BEGIN
   DELETE_RECORD;
END;I'm not 100% convinced that the ON-DELETE trigger is necessary but it didn't hurt. The ON-LOCK trigger and ON-COMMIT triggers were necessary due to differences in Access locking mechanism. I can't tell whether it will work like that in MS SQL Server...

Test the form. Enjoy!