2 Replies Latest reply: Mar 2, 2014 1:45 AM by hitgon RSS

issue in setup of Streams (DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE) to replicate data changes in table

b1b06270-18dd-4134-bb87-6dbcc3c3628e Newbie
Currently Being Moderated

Hi Team,

 

i am facig an issue in setting up of change replications in emp table.

 

My requirement is made generic and is as below :

 

desc scott.emp

Name     Null Type        

-------- ---- ------------

EMPNO         NUMBER(4)   

ENAME         VARCHAR2(10)

JOB           VARCHAR2(9) 

MGR           NUMBER(4)   

HIREDATE      DATE        

SAL           NUMBER(7,2) 

COMM          number(7,2) 

DEPTNO        NUMBER(2) 

 

Whenever a change happened in JOB or MGR in emp table, i need to log the changes to emp_v2 table.

For that am using below Streams API.

 

BEGIN

  DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE(

    CHANGE_TABLE_NAME       => 'scott.emp_v2',

    SOURCE_TABLE_NAME       => 'scott.emp',

    COLUMN_TYPE_LIST        => 'mgr number(4),JOB VARCHAR2(9)',

    extra_column_list        => 'command_type,value_type,commit_scn',

    capture_values           => '*',

    KEEP_CHANGE_COLUMNS_ONLY => false);

END;

 

The error am getting is -

Error report:

ORA-44004: invalid qualified SQL name

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 4891

ORA-06512: at line 2

44004. 0000 -  "invalid qualified SQL name"

*Document: Yes

*Cause:    The input parameter string was not a valid qualified SQL name.

*Action:   Check with the DBMS_ASSERT spec to verify that the parameter

           string is a valid qualified SQL name.

 

Could anyone please provide any inputs on getting this fixed.

 

Regards,

Swaroop.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points