Solved

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

Posted on 2004-04-29
7
2,332 Views
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.
Regards,
KSRTC
0
Comment
Question by:ksrtc
  • 3
  • 2
7 Comments
 
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.

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.
0
 
LVL 1

Author Comment

by:ksrtc
ID: 10947557
Hello,
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.


0
 
LVL 22

Expert Comment

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

Author Comment

by:ksrtc
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.
Regards
0
 
LVL 22

Accepted Solution

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

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.

Join & Write a Comment

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…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now