Home » Server Options » Streams & AQ » error remote table does not contain a primary key (oracle db 11g r2 - windows7)
icon9.gif  error remote table does not contain a primary key [message #535869] Fri, 16 December 2011 17:05 Go to next message
forsubhi
Messages: 5
Registered: December 2011
Junior Member
I make replication between oracle and Microsoft sql using getway for sql but when I insert to table in oracle I get this error in apply process

information :

ASD.SUBHI.COM is database link from oracle to microsoft sql

"DBO"."REGIONS" name of the table

CREATE TABLE regions (a INTEGER primary key) command to create table

error

ORA-26711: remote table does not contain a primary key constraint ORA-26712: remote object is "DBO"."REGIONS"@"ASD.SUBHI.COM"

Note: I go to sql server and verify that primary key is creates



if you need more details please help me
I am very very need the answer .
Re: error remote table does not contain a primary key [message #535877 is a reply to message #535869] Sat, 17 December 2011 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-26711: remote table does not contain a primary key constraint
 *Cause:  The master table for remote apply does not constain a primary key
          constraint or the primary key constraint has been disabled.
 *Action: Create a primary key constraint on the master table or enable the
          existing constraint.


Use SQL*Plus and copy and paste:
desc "DBO"."REGIONS"@"ASD.SUBHI.COM"

Also post the statement that failed.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: error remote table does not contain a primary key [message #535926 is a reply to message #535877] Sat, 17 December 2011 11:03 Go to previous messageGo to next message
forsubhi
Messages: 5
Registered: December 2011
Junior Member
SQL> desc DBO.REGIONS@ASD.SUBHI.COM
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 a                                         NOT NULL NUMBER(10)

SQL> insert into dbo.regions values(77);

1 row created.

SQL> commit;

Commit complete.


oracle version 11.2.0.1.0 windows 7

if you need more information please tell me
  • Attachment: error lcr.png
    (Size: 211.36KB, Downloaded 1447 times)
Re: error remote table does not contain a primary key [message #535994 is a reply to message #535869] Sun, 18 December 2011 16:37 Go to previous messageGo to next message
forsubhi
Messages: 5
Registered: December 2011
Junior Member
I need quick answer if possible

[Updated on: Sun, 18 December 2011 16:37]

Report message to a moderator

Re: error remote table does not contain a primary key [message #535996 is a reply to message #535994] Sun, 18 December 2011 16:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>insert into dbo.regions values(77);
Above proves nothing since INSERT is against local DB & not the remote (Microsoft) DB that throws error.

We can not fix any problem that resides in MS SQL_Server.
Re: error remote table does not contain a primary key [message #535997 is a reply to message #535996] Sun, 18 December 2011 17:58 Go to previous message
forsubhi
Messages: 5
Registered: December 2011
Junior Member
I mean by the error that when I insert value to oracle db then capture process capture changes and apply process send changes through getway to Microsoft sql database the error tell me that I didn't add primary key but I add it because I use
CREATE TABLE regions (a INTEGER primary key)

to create the table in Microsoft sql
and these are the complete command that I use to make the replication
CREATE PUBLIC DATABASE LINK aaa.subhi.com CONNECT TO
"sa" IDENTIFIED BY "asd" USING 'dg4msql';

conn strmadmin/123;
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

DECLARE 
 ret INTEGER; 
BEGIN 
ret := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@aaa.subhi.com ( 
  'drop TABLE "HR"."jobs1"'); 
END; 
/ 
COMMIT;


conn strmadmin/123;

DECLARE 
 ret INTEGER; 
BEGIN 
ret := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@aaa.subhi.com ( 
  'CREATE TABLE "HR"."jobs1" ( "JOB_ID" VARCHAR(10), "JOB_TITLE" VARCHAR(35) CONSTRAINT "JOB_TITLE_NN1" NOT NULL , "MIN_SALARY" integer, "MAX_SALARY" integer,CONSTRAINT "JOB_ID_PK1" PRIMARY KEY ("JOB_ID"))'); 
END; 
/ 
COMMIT; 

conn hr/hr

CREATE TABLE "HR"."jobs1" ( "JOB_ID" VARCHAR(10), "JOB_TITLE" VARCHAR(35) CONSTRAINT "JOB_TITLE_NN1" NOT NULL , "MIN_SALARY" integer, "MAX_SALARY" integer,CONSTRAINT "JOB_ID_PK1" PRIMARY KEY ("JOB_ID"))


conn strmadmin/123;

BEGIN
  DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
    table_name           => 'hr.jobs1',
    supplemental_logging => 'keys');
END;
/


DECLARE
  iscn  NUMBER;   
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
    source_object_name    => 'hr.jobs1',
    source_database_name  => 'orcl3',
    instantiation_scn     => iscn,
    apply_database_link   => 'aaa.subhi.com');
END;
/



BEGIN
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
    schema_name    => 'hr',   
    streams_type   => 'capture',
    streams_name   => 'capture_sql',
    queue_name     => 'strmadmin.streams_queue',
    include_dml    => TRUE, 
    include_ddl    => TRUE,
    inclusion_rule => TRUE);
END;
/



BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name          => 'strmadmin.streams_queue',
    apply_name          => 'apply_sql',
    apply_database_link => 'aaa.subhi.com',
    apply_captured      => TRUE);
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'hr.jobs1',
    streams_type    => 'apply', 
    streams_name    => 'apply_sql',
    queue_name      => 'strmadmin.streams_queue',
    include_dml     => TRUE,
    include_ddl     => FALSE,
    source_database => 'orcl3',
    inclusion_rule  => TRUE);
END;
/




BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'apply_sql', 
    parameter   => 'disable_on_error', 
    value       => 'N');
END;
/
 
BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'apply_sql');
END;
/


BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name  => 'capture_sql');
END;


Previous Topic: Streams -- Joining mulitple tables and replicating to a single table
Next Topic: ORA-26753: Mismatched columns found in 'DBO.TEST'
Goto Forum:
  


Current Time: Fri Mar 29 02:32:51 CDT 2024