Import and export between Oracle and Access tables

Question: How to import and export data between Oracle and Access tables in FORM programs.

Answer:

Preparations:

1. Install OCA. Run the Developer's installation disk, select Custom installation, and select Oracle Open Client Adapter for ODBC for installation.

2. Add a DSN to the data source (ODBC. Control Panel-> Administrative Tools-> data source (ODBC), select "User DSN", and add the Access file to be operated. In the "advanced" option, fill in "Login Name" and "password" (very important, used in the program ).

The following is an example:

Suppose there is a student table in Oracle and Access. The fields in the table are the same (name char (10), age number (2). In preparation 2, the "Data Source name" is test, both the "Login Name" and "password" are user.

Procedure exported from Oracle to Access is as follows:

The following is a reference clip: PROCEDURE oracle_to_access IS Connection_id EXEC_ SQL .ConnType; Action_cursor EXEC_ SQL .CursType; Ignore PLS_INTEGER; T_name student. name % type; T_age student. age % type; Cursor temp_cursor is select * from student; BEGIN Connection_id: = EXEC_ SQL .OPEN_CONNECTION ('user/user @ odbc: test '); Action_cursor: = EXEC_ SQL .OPEN_CURSOR (connection_id ); EXEC_ SQL .PARSE (connection_id, action_cursor, 'delete * from student '); Ignore: = EXEC_ SQL .EXECUTE (connection_id, action_cursor ); EXEC_ SQL .CLOSE_CURSOR (connection_id, action_cursor ); Open temp_cursor; Export_count: = 0; Action_cursor: = EXEC_ SQL .OPEN_CURSOR (connection_id ); EXEC_ SQL .PARSE (connection_id, action_cursor ,' Insert into student (name, age) values (: 1,: 2 )'); Loop Fetch temp_cursor into t_name, t_age; Exit when temp_cursor % notfound; EXEC_ SQL .BIND_VARIABLE (connection_id, action_cursor, ': 1', t_name ); EXEC_ SQL .BIND_VARIABLE (connection_id, action_cursor, ': 2', t_age ); Ignore: = EXEC_ SQL .EXECUTE (connection_id, action_cursor ); End loop; Close temp_cursor; EXEC_ SQL .PARSE (connection_id, action_cursor, 'commit '); Ignore: = EXEC_ SQL .EXECUTE (connection_id, action_cursor ); EXEC_ SQL .CLOSE_CURSOR (connection_id, action_cursor ); EXEC_ SQL .CLOSE_CONNECTION (connection_id ); EXCEPTION WHEN EXEC_ SQL .PACKAGE_ERROR THEN IF EXEC_ SQL .LAST_ERROR_CODE (connection_id )! = 0 THEN Message ('data export to ACCESS failed: '| TO_CHAR (EXEC_ SQL .LAST_ERROR_CODE (connection_id) | ':' | EXEC_ SQL .LAST_ERROR_MESG (connection_id )); End if; IF EXEC_ SQL .IS_CONNECTED (connection_id) THEN IF EXEC_ SQL .IS_OPEN (connection_id, action_cursor) THEN EXEC_ SQL .CLOSE_CURSOR (connection_id, action_cursor ); End if; EXEC_ SQL .CLOSE_CONNECTION (connection_id ); End if; END; The procedure exported from Access to doneles is as follows: PROCEDURE Access_to_oracle IS Connection_id EXEC_ SQL .ConnType; Action_cursor EXEC_ SQL .CursType; Ignore PLS_INTEGER; T_name student. name % type; T_age student. age % type; BEGIN Connection_id: = EXEC_ SQL .OPEN_CONNECTION ('user/user @ odbc: test '); Action_cursor: = EXEC_ SQL .OPEN_CURSOR (connection_id ); Delete from student; EXEC_ SQL .PARSE (connection_id, action_cursor, 'select name, age from student '); Ignore: = EXEC_ SQL .EXECUTE (connection_id, action_cursor ); Exec_ SQL .define_column (connection_id, action_cursor, 1, t_name, 10 ); Exec_ SQL .define_column (connection_id, action_cursor, 2, t_age ); Ignore: = EXEC_ SQL .EXECUTE (connection_id, action_cursor ); While (exec_ SQL .fetch_rows (connection_id, action_cursor)> 0) Loop Exec_ SQL .column_value (connection_id, action_cursor, 1, t_name ); Exec_ SQL .column_value (connection_id, action_cursor, 2, t_age ); Insert into test (name, age) values (t_name, t_age ); End loop; Commit; EXEC_ SQL .CLOSE_CURSOR (connection_id, action_cursor ); EXEC_ SQL .CLOSE_CONNECTION (connection_id ); EXCEPTION WHEN EXEC_ SQL .PACKAGE_ERROR THEN IF EXEC_ SQL .LAST_ERROR_CODE (connection_id )! = 0 THEN Message ('failed to import data to ORACLE: '| TO_CHAR (EXEC_ SQL .LAST_ERROR_CODE (connection_id) | ':' | EXEC_ SQL .LAST_ERROR_MESG (connection_id )); End if; IF EXEC_ SQL .IS_CONNECTED (connection_id) THEN IF EXEC_ SQL .IS_OPEN (connection_id, action_cursor) THEN EXEC_ SQL .CLOSE_CURSOR (connection_id, action_cursor ); End if; EXEC_ SQL .CLOSE_CONNECTION (connection_id ); End if; END;

Note: variables bound to EXEC_ SQL .BIND_VARIABLE can only be of the following types: NUMBER, DATE, VARCHAR2. For the Boolean variable "yes/no" in Access, it can be expressed by 1 and 0 of the NUMBER type. If the table name or field name in Access contains spaces, you can use double quotation marks to include the table name or field name when writing an SQL statement. For example, if the table name in Access is student detail, the field names are student name and student age respectively. The SQL statement for inserting data is: insert into "student detail" ("student name", "student age") values (: 1 ,: 2 ).