Solved

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

Posted on 2004-04-29
7
2,423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 86
oracle date format checking 7 50
Select the 2 most recent visit dates 5 29
Oracle cluster . 1 22
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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