Oracle-11g
必須聲明標識符“DBMS_CDC_PUBLISH.CREATE_CHANGE_SET”
我正在使用 oracle 11g express edition,在嘗試創建更改集時出現以下錯誤。
錯誤報告:
ORA-06550: line 2, column 3: PLS-00201: identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
我知道 CDC 在更高版本的 oracle 中被取消支持,但我認為它在這裡受到支持。
有人可以幫忙嗎?
我試圖明確地授予
SYSTEM
使用者權限grant execute on DBMS_CDC_PUBLISH to SYSTEM;
但我明白了
ORA-04042: procedure, function, package, or package body does not exist
驗證包
要驗證您確實在 Oracle 中有所需的包,請執行以下語句:
set pages 50 set lines 230 column owner format a20 column object_name format a30 SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE WHERE Owner IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') AND Object_Type IN ('PACKAGE') AND Object_Name like 'DBMS_CDC%' ORDER BY Owner, Object_Type, Object_Name
這應該返回如下內容:
OWNER OBJECT_TYPE OBJECT_NAME -------------------- ----------------------- ------------------------------ SYS PACKAGE DBMS_CDC_DPUTIL SYS PACKAGE DBMS_CDC_EXPDP SYS PACKAGE DBMS_CDC_EXPVDP SYS PACKAGE DBMS_CDC_IMPDP SYS PACKAGE DBMS_CDC_IMPDPV SYS PACKAGE DBMS_CDC_IPUBLISH SYS PACKAGE DBMS_CDC_ISUBSCRIBE SYS PACKAGE DBMS_CDC_PUBLISH SYS PACKAGE DBMS_CDC_SUBSCRIBE SYS PACKAGE DBMS_CDC_SYS_IPUBLISH SYS PACKAGE DBMS_CDC_UTILITY 11 rows selected.
封裝參數
如果一切順利,您可以執行以下語句來確定所需的輸入
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET
:set pages 50 set lines 250 column object_info format a200 select ao.object_type ||' '|| ao.owner ||'.'|| ao.object_name || case when ap.procedure_name is not null then '.'|| ap.procedure_name || case when count(aa.argument_name) > 0 then '('|| listagg(aa.argument_name, ',') within group (order by position) ||')' end end as object_info from all_objects ao left join all_procedures ap on ap.owner = ao.owner and ap.object_name = ao.object_name and ap.object_id = ao.object_id left join all_arguments aa on aa.owner = ap.owner and aa.object_name = ap.procedure_name and aa.object_id = aa.object_id and aa.subprogram_id = ap.subprogram_id where ao.owner in ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER', 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM') and ao.object_type in ('PACKAGE', 'TYPE') and ao.object_name like 'DBMS_CDC_PUBLISH%' and ap.procedure_name like 'CREATE_CHANGE_SET%' group by ao.object_type, ao.owner, ao.object_name, ap.procedure_name, ao.object_id, ap.subprogram_id order by ao.owner, ao.object_type, ao.object_name, ap.procedure_name;
這應該返回:
OBJECT_INFO -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PACKAGE SYS.DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(CHANGE_SET_NAME,DESCRIPTION,CHANGE_SOURCE_NAME,STOP_ON_DDL,BEGIN_DATE,END_DATE) 1 row selected.
問題
如果您沒有得到結果,那麼您可能正在執行一個不支持該軟體包的 Oracle 版本,或者您沒有足夠的權限。
CDC 版本支持
…在 Oracle 標準版中提供了同步模式:
變更數據擷取與已安裝的適當 Oracle 驅動程序一起打包,您可以使用這些驅動程序實現非同步或同步數據擷取。更改數據擷取的同步模式包含在標準版中,但非同步模式需要您擁有企業版。
參考資料
- 如何獲取包含所有 dba 包描述的列表(堆棧溢出)
- Oracle 提供的 PL/SQL 包和類型簡介 (Oracle | Docs)
- DBMS_CDC_PUBLISH(甲骨文|文件)
- 變更數據擷取(Oracle | Docs)
相同的答案:
問題是您使用的是 Express Edition,它不包含 Change Data Capture 功能。
https://docs.oracle.com/cd/E17781_01/license.112/e18068/toc.htm#XELIC101