MS Access連結Oracle DB error: "保留錯誤 (-7732);這個錯誤並無訊息"

為便利資料上傳, 要以Access連結Oracle, 裝好instant client 11, 測試正常.

但實際要連DB時, 出現error: "保留錯誤 (-7732);這個錯誤並無訊息".

搜尋網路, 沒有發現什麼可用的訊息. 但以excel透過ODBC, 則是可正常擷取Oracle DB資料.

試了更改Access的一些設定, 都沒有作用.

想著可能與版本有關, 後來有人試出結果, client原先用11.01有異常, 但改用12.01就正常了.

System:
  Windows 7 專業版 SP1 (64位元)
  Access 2010 (64位元)
  Oracle Client 12.01

為了版本問題浪費一個下午...  Orz...


附註: TNSNAME問題
若名稱上有.world(例如R12.world), 在TNS Service Name欄位只會看到R12, 測試連線會說名稱無法識別, 把tnsname.ora中的.world字樣移除就好了.

Recompiling Invalid Schema Objects

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner || 
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner || 
                             ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;
/

REF: http://oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php 

Oracle: Recompile Invalid Objects

invalid.sql

set heading off;
set feedback off;
set echo off;
set lines 999;


spool run_invalid.sql

select 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects
where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql


REF: http://www.dba-oracle.com/t_recompile_recompliling_invalid_objects.htm