Import and export between Oracle and access tables in Form

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, "Data Source Name" is test, "Login Name" and "password" are both user. Procedure: 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, Acti On_cursor, 'delete * from student '); ignore: = exec_ SQL .execute (connection_id, action_cursor); disconnect (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 % notfoun D; disconnect (connection_id, action_cursor, ': 1', t_name); disconnect (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); disconnect (connection_id, action_cursor); then E_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 response (connection_id) then if exec_ SQL .is_open (connection_id, action_cursor) Then response (connection_id, action_cursor); end if; exec_ SQL .close_connection (connection_id); end if; end; procedure: procedur E 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 on_id; delete from student; exec_ SQL .parse (connection_id, action_cursor, 'select name, age from student '); ignore: = exec_ SQL. Execute (connection_id, action_cursor); disconnect (connection_id, action_cursor, 1, t_name, 10); disconnect (connection_id, action_cursor, 2, t_age); ignore: = exec_ SQL .execute (connection_id, action_cursor); While (exec_ SQL .fetch_rows on_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 ('data import to Oracle failed: '| to_char (exec_ SQL .last_error_code (connection_id) |': '| exec_ SQL .last_error_mesg (connection_id ));
End if; if then (connection_id) then if exec_ SQL .is_open (connection_id, action_cursor) then (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 ).