Oracle Synonym

Following query is to automatically update table data in one database based on changes in another database.

sql>create public synonym AMM_PRODUCTS_PHARMA for ALAB.AMM_PRODUCTS_PHARMA;

sql>create or replace
TRIGGER AMM_PRODUCTS_TRIGGER
after insert on APHARMA.AMM_PRODUCTS for each row
begin
insert into AMM_PRODUCTS_PHARMA
(PRD_ID,
PRD_GENERIC_NAME,
PRD_SHORT_DESC,
PRD_DESC
) values
(:new.PRD_ID,
:new.PRD_GENERIC_NAME,
:new.PRD_SHORT_DESC,
:new.PRD_DESC
);
end;

sql>create public synonym AMT_PROD_STOCK_DETAILS_PHARMA for ALAB.AMT_PROD_STOCK_DETAILS_PHARMA;

sql>create or replace trigger amm_prod_stock_details_trigger
after insert  on APHARMA.AMT_PROD_STOCK_DETAILS for each row
begin
 insert into AMT_PROD_STOCK_DETAILS_PHARMA
 (PRS_SEQ_NO,
PRS_DOC_DATE,
PRS_PER_ID,
PRS_CPY_ID
) values
(:new.PRS_SEQ_NO,
:new.PRS_DOC_DATE,
:new.PRS_PER_ID,
:new.PRS_CPY_ID
);
end;

-- By Mr. Nitesh Ghosalkar

Entering sequence no in column of table already having data in SQL

  alter table AMM_PROD_DRUG_LICENCE add pdl_seq_no NUMBER(19,0);

update AMM_PROD_DRUG_LICENCE set pdl_Seq_no=1;

CREATE SEQUENCE pdl_sequence_no_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 50;

UPDATE AMM_PROD_DRUG_LICENCE
   SET pdl_seq_no = pdl_sequence_no_seq.nextval;