Accessing .MDB(Access) file in Oracle Forms 6i

Here is Note:115540.1 from MetaLink: How to Copy Data from ODBC Datasource to Oracle using Forms

This article describes how to copy data from an ODBC datasource such as Access into a table in an Oracle database using Oracle Forms. The method uses the client-side EXEC_SQL package to open multiple connections and copy the data.

The article is intended for Forms programmers who may be new to ODBC.
The code provided is only for demonstration of a general principle - in  practice additional code appropriate to the application would be required to  improve error-handling and robustness.

INTRODUCTION
It is sometimes necessary to be able to copy data between non-Oracle datasources and Oracle databases (and vice versa) without dumping the data out to a flat file and using tools such as SQLLoader to reload the data.
The use of the EXEC_SQL package from a client tool (eg Forms, Reports, Procedure Builder) with the Open Client Adapter (OCA) for ODBC allows  multiple connections to both Oracle and non-Oracle databases to be opened simultaneously.
Developer 6.0.x has the EXEC_SQL package built in. In earlier releases of Developer the functionality was provided by a PL/SQL library (.pll file).
This note assumes you are using Developer 6.0.x
EXEC_SQL is similar in many respects to the server-side DBMS_SQL package, however see the Procedure Builder online help for further information on the differences between these packages.

BACKGROUND
You have a table called emp in an Access 97 database which has columns empno, ename and deptno and you wish to copy the data from this table into a table  in your Oracle database. For simplicity we will assume that the table in the Oracle database is also  called emp which also has columns empno, ename and deptno.
The sample code provided shows how to do this, however it makes no attempt to  check for the failure of the insert into the Oracle table (eg if primary/ foreign key constraints are violated). Additional code would be required to  perform these checks.

SOLUTION
1. Add the ODBC datasource for your Access database.
       In the Control Panel choose the ODBC Data Sources icon and click on the Add button.
       Choose the MS Access driver and click on the Finish button.
       In the window that now appears type in the name of the datasource (eg empaccess), a description and choose the Access .mdb file of your database by clicking on the Select button.

2. Build the Form.
      Before doing this make sure you have installed Oracle Open Client Adapter for ODBC - this is available from the Developer CD.
      Create a control block with four text items of datatype Char.
      The items are called:
          source_db - which holds the connect string for the Access database
         source_tab - the name of the Access table to be copied
          dest_db - the connect string for the Oracle database
          dest_tab - the name of the Oracle table into which data will be copied
     Set the Required property of all four text items to Yes
     Create a button item called copy in this control block.
      Place the following code in an ON-LOGON trigger at Form level: NULL;

     Place the following code in a WHEN-BUTTON-PRESSED trigger on the copy button:
declare
       empno              NUMBER;
       ename               VARCHAR2(10);
       deptno               NUMBER;
       source_connid      EXEC_SQL.ConnType;
       destination_connid EXEC_SQL.ConnType;
       source_cursor      EXEC_SQL.CursType;
       destination_cursor EXEC_SQL.CursType;
       dummy               PLS_INTEGER;
 begin
 -- open the connections
      source_connid := EXEC_SQL.OPEN_CONNECTION(:source_db);
       destination_connid := EXEC_SQL.OPEN_CONNECTION(:dest_db);
-- prepare the cursor to select from source database
        source_cursor := EXEC_SQL.OPEN_CURSOR(source_connid);
       EXEC_SQL.PARSE(source_connid, source_cursor,'SELECT empno, ename,deptno FROM ' ||:source_tab);       EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor,1,empno);
       EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor,2,ename,10);
       EXEC_SQL.DEFINE_COLUMN(source_connid, source_cursor,3,deptno);
       dummy := EXEC_SQL.EXECUTE(source_connid,source_cursor);
-- prepare the cursor to insert into destination database
        destination_cursor := EXEC_SQL.OPEN_CURSOR(destination_connid);
       EXEC_SQL.PARSE(destination_connid,destination_cursor,'INSERT INTO ' ||:dest_tab ||'(empno,ename,deptno) VALUES (:empno,:ename,:deptno)');
       LOOP
-- fetch rows from source database
              IF EXEC_SQL.FETCH_ROWS(source_connid,source_cursor) > 0 THEN
-- get column values for this row
                  EXEC_SQL.COLUMN_VALUE(source_connid,source_cursor,1, empno);
                   EXEC_SQL.COLUMN_VALUE(source_connid,source_cursor,2, ename);
                   EXEC_SQL.COLUMN_VALUE(source_connid,source_cursor,3, deptno);
-- bind the column values into the cursor that inserts into destination database
                    EXEC_SQL.BIND_VARIABLE(destination_connid,destination_cursor, ':empno', empno);
                   EXEC_SQL.BIND_VARIABLE(destination_connid,destination_cursor, ':ename', ename);
                   EXEC_SQL.BIND_VARIABLE(destination_connid,destination_cursor, ':deptno', deptno);
                   dummy := EXEC_SQL.EXECUTE(destination_connid,destination_cursor);
             ELSE
                   EXIT;
             END IF;
       END LOOP;
-- commit transaction in destination database
        EXEC_SQL.PARSE(destination_connid,destination_cursor,'commit');
       dummy := EXEC_SQL.EXECUTE(destination_connid,destination_cursor);
-- close cursors and connections
        EXEC_SQL.CLOSE_CURSOR(destination_connid,destination_cursor);
       EXEC_SQL.CLOSE_CURSOR(source_connid, source_cursor);
        EXEC_SQL.CLOSE_CONNECTION(destination_connid);
       EXEC_SQL.CLOSE_CONNECTION(source_connid);
        MESSAGE('Done!');
       EXCEPTION

-- handle errors raised by the exec_sql package
              WHEN EXEC_SQL.PACKAGE_ERROR THEN
               IF EXEC_SQL.LAST_ERROR_CODE(source_connid) != 0 THEN
                     TEXT_IO.PUT_LINE('ERROR (source: ' ||
                       TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(source_connid))|| '): ' ||EXEC_SQL.LAST_ERROR_MESG(source_connid));
               END IF;

               IF EXEC_SQL.LAST_ERROR_CODE(destination_connid) != 0 THEN
                     TEXT_IO.PUT_LINE('ERROR (destination: ' ||
                      TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(destination_connid))|| '): ' ||EXEC_SQL.LAST_ERROR_MESG(destination_connid));
               END IF;

               IF EXEC_SQL.IS_CONNECTED(destination_connid) THEN
                     IF EXEC_SQL.IS_OPEN(destination_connid,destination_cursor) THEN
                           EXEC_SQL.CLOSE_CURSOR(destination_connid,destination_cursor);
                     END IF;
                     EXEC_SQL.CLOSE_CONNECTION(destination_connid);
               END IF;

               IF EXEC_SQL.IS_CONNECTED(source_connid) THEN
                     IF EXEC_SQL.IS_OPEN(source_connid,source_cursor) THEN
                           EXEC_SQL.CLOSE_CURSOR(source_connid,source_cursor);
                     END IF;
                     EXEC_SQL.CLOSE_CONNECTION(source_connid);
               END IF;
       END;
     Compile, save and generate the form.

3. Run the Form
       In the Source_Db item enter the connection string for the Access database eg /@odbc:empaccess (assuming there is no username/password on the Access database and empaccess is the name of the datasource defined in the Control Panel - in general the format of the connection string is
       username/password@odbc:datasource)
       In the Source_Tab item enter the name of the Access table to be copied eg emp
       In the Dest_Db item enter the connection string for the Oracle database to which the data is to be copied eg scott/tiger@mydb where mydb is defined as a valid connection in your tnsnames.ora
       In the Dest_tab item enter the name of the table to which the data will be copied eg emp
       Now press the copy button.
      The data should now be copied from the emp table in the Access database to the emp table in the Oracle database.

RELATED DOCUMENTS - Online Help for Procedure Builder

I hope this will help you.