Verifying Packages
To verify that you indeed do have the required packages in Oracle run the following statement:
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
This should return something like this:
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.
Package Parameters
If all is well, then you can run the following statement to determine the required inputs for 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;
This should return:
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.
Isues
If you don't get a result, then you are possibly running a version of Oracle that doesn't support the package or you don't have sufficient privileges.
CDC Edition Support
...in synchronous mode is available in Oracle Standard Edition:
Change Data Capture comes packaged with the appropriate Oracle drivers already installed with which you can implement either asynchronous or synchronous data capture. The synchronous mode of Change Data Capture is included with the Standard Edition, but the asynchronous mode requires you have the Enterprise Edition.
Reference Material
