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

Posted on 2004-04-29
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 50 total points
ID: 10974818
MEMO ought to be converted to CLOB/BLOB column.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 78
Oracle DATE Column Space 11 80
Determine Who is Runnig my Bash Shell Script 4 82
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 69
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

832 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