Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2004-04-29
Medium Priority
Last Modified: 2007-11-27
Hello, how to access .mdb files in Developer 6i? I want to export data from Access(.mdb file format) to Oralce Database via Forms 6i.

Thanks in Advance.
Question by:ksrtc
  • 3
  • 2
LVL 22

Expert Comment

by:Helena Marková
ID: 10947375
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.

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.

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.

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:
       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;
 -- 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)');
-- 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);
             END IF;
       END LOOP;
-- commit transaction in destination database
       dummy := EXEC_SQL.EXECUTE(destination_connid,destination_cursor);
-- close cursors and connections
       EXEC_SQL.CLOSE_CURSOR(source_connid, source_cursor);

-- handle errors raised by the exec_sql package
               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
                     END IF;
               END IF;

               IF EXEC_SQL.IS_CONNECTED(source_connid) THEN
                     IF EXEC_SQL.IS_OPEN(source_connid,source_cursor) THEN
                     END IF;
               END IF;
     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
       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.

Author Comment

ID: 10947557
thanks for the info.
I created ODBC conenction etc.
I am specific about .MDB files through forms during run time?

Can you make more focus on accessing the .MDB files through froms during runtime.

Thanks in Advance.

LVL 22

Expert Comment

by:Helena Marková
ID: 10947987
I am sorry I cannot do more. I have not used MDB files in Forms...

Author Comment

ID: 10958880
Hello Henka,
Your solution is working fine.
But I have some data with MEMO datatype in MS Access Database. How can I read the Data Memo field and prot the data to Oracle.
Thanks in Advace.
LVL 22

Accepted Solution

Helena Marková earned 200 total points
ID: 10974818
MEMO ought to be converted to CLOB/BLOB column.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question