Oracle与Access表之间的导入和导出实现
问题的提出：如何在FORM的程序中实现Oracle与Access表之间的导入和导出。 问题的解答：准备工作：
1.安装OCA。运行Developer的安装盘，选择自定义安装，选择Oracle Open Client Adapter for ODBC安装。
2.在数据源(ODBC)中添加DSN。控制面板->管理工具->数据源(ODBC)，选择“用户DSN”，添加要进行操作的Access的文件。在“高级”选项里，填上“登录名称”和“密码”(很重要，在程序中会用到)。
下面以实际例子来说明：
假设在Oracle中和Access中都有一个student表，表中字段相同(name char(10) ,age number(2))，在准备工作2中的“数据源名”为test，“登录名称”和“密码”都为user。
下面为从Oracle导出到Access的procedure：
<div style="padding-right: 5.4pt; padding-left: 5.4pt; background: #e6e6e6 0% 50%; padding-bottom: 4px; width: 95%; padding-top: 4px;">http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifPROCEDURE oracle_to_access IS
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif connection_id EXEC_SQL.ConnType;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif action_cursor EXEC_SQL.CursType;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore PLS_INTEGER;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif t_name student.name%type;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif t_age student.age%type;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif cursor temp_cursor is select * from student;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif BEGIN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif connection_id:= EXEC_SQL.OPEN_CONNECTION('user/user@odbc:test');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif action_cursor := EXEC_SQL.OPEN_CURSOR(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.PARSE(connection_id, action_cursor,'delete * from student');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore := EXEC_SQL.EXECUTE(connection_id, action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CURSOR(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif open temp_cursor;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif export_count := 0;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif action_cursor := EXEC_SQL.OPEN_CURSOR(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.PARSE(connection_id, action_cursor,'INSERT INTO student(name,age) values(:1,:2)');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif loop
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif fetch temp_cursor into t_name,t_age;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif exit when temp_cursor%notfound;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.BIND_VARIABLE(connection_id,action_cursor, ':1', t_name);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.BIND_VARIABLE(connection_id,action_cursor, ':2', t_age);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore := EXEC_SQL.EXECUTE(connection_id, action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif end loop;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif close temp_cursor;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.PARSE(connection_id, action_cursor,'commit');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore := EXEC_SQL.EXECUTE(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CURSOR(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CONNECTION(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXCEPTION
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif WHEN EXEC_SQL.PACKAGE_ERROR THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.LAST_ERROR_CODE(connection_id) != 0 THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif message('数据导出至ACCESS失败: ' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE (connection_id)) || ': ' || EXEC_SQL.LAST_ERROR_MESG(connection_id));
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.IS_CONNECTED(connection_id) THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.IS_OPEN(connection_id,action_cursor) THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CURSOR(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CONNECTION(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 下面为从Access导出到Oracles的procedure：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif PROCEDURE Access_to_oracle IS
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif connection_id EXEC_SQL.ConnType;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif action_cursor EXEC_SQL.CursType;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore PLS_INTEGER;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif t_name student.name%type;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif t_age student.age%type;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif BEGIN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif connection_id := EXEC_SQL.OPEN_CONNECTION('user/user@odbc:test');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif action_cursor := EXEC_SQL.OPEN_CURSOR(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif delete from student;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.PARSE(connection_id, action_cursor,'select name,age from student');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore := EXEC_SQL.EXECUTE(connection_id, action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif exec_sql.define_column(connection_id,action_cursor,1,t_name,10);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif exec_sql.define_column(connection_id,action_cursor,2,t_age);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ignore := EXEC_SQL.EXECUTE(connection_id, action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif while(exec_sql.fetch_rows(connection_id,action_cursor)>0)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif loop
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif exec_sql.column_value(connection_id,action_cursor,1,t_name);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif exec_sql.column_value(connection_id,action_cursor,2,t_age);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif insert into test(name,age) values(t_name,t_age);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif end loop;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif commit;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CURSOR(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CONNECTION(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXCEPTION
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif WHEN EXEC_SQL.PACKAGE_ERROR THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.LAST_ERROR_CODE(connection_id) != 0 THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif message('数据导入至ORACLE失败: ' || TO_CHAR(EXEC_SQL.LAST_ERROR_CODE (connection_id)) || ': ' || EXEC_SQL.LAST_ERROR_MESG(connection_id));
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.IS_CONNECTED(connection_id) THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif IF EXEC_SQL.IS_OPEN(connection_id,action_cursor) THEN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CURSOR(connection_id,action_cursor);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif EXEC_SQL.CLOSE_CONNECTION(connection_id);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END IF;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif END;
页:
[1]