7 Replies Latest reply: Dec 19, 2011 3:43 PM by 895836 RSS

    error remote table does not contain a primary key

    895836
      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"
        • 1. Re: error remote table does not contain a primary key
          895836
          Note: I go to sql server and verify that primary key is creates
          • 2. Re: error remote table does not contain a primary key
            895836
            I need your help cos I have project on this
            please help me
            • 3. Re: error remote table does not contain a primary key
              Vishalp-Oracle
              Looks like in your MS sql db dbo.regions table doesnt have primary key or the primary key constraint is not enabled.
              • 4. Re: error remote table does not contain a primary key
                895836
                this is the command that I used to create table on Microsoft sql server
                CREATE TABLE regions (a INTEGER primary key)
                • 5. Re: error remote table does not contain a primary key
                  895836
                  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
                  Code: [Select all] [Show/ hide]

                  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;
                  • 6. Re: error remote table does not contain a primary key
                    Vishalp-Oracle
                    Don't you have source and destination table structure same?
                    • 7. Re: error remote table does not contain a primary key
                      895836
                      I use this command to create the two tables

                      CREATE TABLE regions (a INTEGER primary key)