Import and export implementations between Oracle and Access tables

Question: How to implement import and export between Oracle and Access tables in form programs.

Answer to the question:

Preparatory work:

1. Install OCA. Run the developer installation disk, select the custom installation, and select the Oracle Open Client Adapter for ODBC installation.

2. Add DSN to the data source (ODBC). Control Panel-> Administrative Tools-> Data Source (ODBC), select User DSN to add the files for access that you want to manipulate. In the advanced option, fill in the "Login name" and "password" (important to use in the program).

Here is a practical example to illustrate:

Assume that both Oracle and access have a student table with the same fields (name char, age number (2)), and the "Data source name" In preparation 2 is test, and both the login name and password are user.

The following are the procedure exported from Oracle to access:

The following is a reference fragment:
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 following is the procedure that is exported from access to Oracles:
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 (' Data import to Oracle 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;

Note: Exec_sql. Only the following three types of variables are bound in bind_variable: Number,date,varchar2. For "Yes/No" Boolean variables in Access, you can use 1 and 0来 of the number type. If you have spaces in the table name or field name in Access, you can include the table name or field name in double quotes when you write the SQL statement, such as: In this case, if the table name in Access is student detail, the field names are student name and student age, respectively. , the SQL statement that inserts the data is: INSERT into "Student detail" ("Student Name", "Student Age") VALUES (: 1,:2).