問題的提出如何在FORM的程序中實現Oracle與Access表之間的導入和導出
問題的解答
准備工作
安裝OCA運行Developer的安裝盤選擇自定義安裝選擇Oracle Open Client Adapter for ODBC安裝
在數據源(ODBC)中添加DSN控制面板>管理工具>數據源(ODBC)選擇用戶DSN添加要進行操作的Access的文件在高級選項裡填上登錄名稱和密碼(很重要在程序中會用到)
下面以實際例子來說明
假設在Oracle中和Access中都有一個student表表中字段相同(name char() age number())在准備工作中的數據源名為test登錄名稱和密碼都為user
下面為從Oracle導出到Access的procedure
以下是引用片段
PROCEDURE oracle_to_access IS
connection_id EXEC_SQLConnType;
action_cursor EXEC_SQLCursType;
ignore PLS_INTEGER;
t_name studentname%type;
t_age studentage%type;
cursor temp_cursor is select * from student;
BEGIN
connection_id:= EXEC_SQLOPEN_CONNECTION(user/user@odbc:test);
action_cursor := EXEC_SQLOPEN_CURSOR(connection_id);
EXEC_SQLPARSE(connection_id action_cursordelete * from student);
ignore := EXEC_SQLEXECUTE(connection_id action_cursor);
EXEC_SQLCLOSE_CURSOR(connection_idaction_cursor);
open temp_cursor;
export_count := ;
action_cursor := EXEC_SQLOPEN_CURSOR(connection_id);
EXEC_SQLPARSE(connection_id action_cursorINSERT INTO student(nameage) values(::));
loop
fetch temp_cursor into t_namet_age;
exit when temp_cursor%notfound;
EXEC_SQLBIND_VARIABLE(connection_idaction_cursor : t_name);
EXEC_SQLBIND_VARIABLE(connection_idaction_cursor : t_age);
ignore := EXEC_SQLEXECUTE(connection_id action_cursor);
end loop;
close temp_cursor;
EXEC_SQLPARSE(connection_id action_cursorcommit);
ignore := EXEC_SQLEXECUTE(connection_idaction_cursor);
EXEC_SQLCLOSE_CURSOR(connection_idaction_cursor);
EXEC_SQLCLOSE_CONNECTION(connection_id);
EXCEPTION
WHEN EXEC_SQLPACKAGE_ERROR THEN
IF EXEC_SQLLAST_ERROR_CODE(connection_id) != THEN
message(數據導出至ACCESS失敗: || TO_CHAR(EXEC_SQLLAST_ERROR_CODE(connection_id)) || : || EXEC_SQLLAST_ERROR_MESG(connection_id));
END IF;
IF EXEC_SQLIS_CONNECTED(connection_id) THEN
IF EXEC_SQLIS_OPEN(connection_idaction_cursor) THEN
EXEC_SQLCLOSE_CURSOR(connection_idaction_cursor);
END IF;
EXEC_SQLCLOSE_CONNECTION(connection_id);
END IF;
END;
下面為從Access導出到Oracles的procedure
PROCEDURE Access_to_oracle IS
connection_id EXEC_SQLConnType;
action_cursor EXEC_SQLCursType;
ignore PLS_INTEGER;
t_name studentname%type;
t_age studentage%type;
BEGIN
connection_id := EXEC_SQLOPEN_CONNECTION(user/user@odbc:test);
action_cursor := EXEC_SQLOPEN_CURSOR(connection_id);
delete from student;
EXEC_SQLPARSE(connection_id action_cursorselect nameage from student);
ignore := EXEC_SQLEXECUTE(connection_id action_cursor);
exec_sqldefine_column(connection_idaction_cursort_name);
exec_sqldefine_column(connection_idaction_cursort_age);
ignore := EXEC_SQLEXECUTE(connection_id action_cursor);
while(exec_sqlfetch_rows(connection_idaction_cursor)>)
loop
lumn_value(connection_idaction_cursort_name);
lumn_value(connection_idaction_cursort_age);
insert into test(nameage) values(t_namet_age);
end loop;
commit;
EXEC_SQLCLOSE_CURSOR(connection_idaction_cursor);
EXEC_SQLCLOSE_CONNECTION(connection_id);
EXCEPTION
WHEN EXEC_SQLPACKAGE_ERROR THEN
IF EXEC_SQLLAST_ERROR_CODE(connection_id) != THEN
message(數據導入至ORACLE失敗: || TO_CHAR(EXEC_SQLLAST_ERROR_CODE(connection_id)) || : || EXEC_SQLLAST_ERROR_MESG(connection_id));
END IF;
IF EXEC_SQLIS_CONNECTED(connection_id) THEN
IF EXEC_SQLIS_OPEN(connection_idaction_cursor) THEN
EXEC_SQLCLOSE_CURSOR(connection_idaction_cursor);
END IF;
EXEC_SQLCLOSE_CONNECTION(connection_id);
END IF;
END;
注意EXEC_SQLBIND_VARIABLE中綁定的變量只能是以下三種類型NUMBERDATEVARCHAR對於Access中的是/否的布爾型變量可以用NUMBER類型的和來表示如果Access中的表名或者字段名中有空格在寫SQL語句的時候可以用雙引號把表名或者字段名包括起來如本例中如果Access中表名為student detail字段名分別為student name和student age那插入數據的SQL語句為insert into student detail(student namestudent age) values(::)
From:http://tw.wingwit.com/Article/program/Oracle/201311/17037.html
