stored procedure to Inserting new rows and updating existing rows [message #644285] |
Mon, 02 November 2015 11:06 |
|
amy.wilson
Messages: 39 Registered: September 2015 Location: Www
|
Member |
|
|
Hi All,
I have tables as follows.
CREATE TABLE ACC
(ID NOT NULL VARCHAR2(21),
OWNER_ID VARCHAR2(21) ,
S_KEY__C VARCHAR2(15) ,
ACC_NUMBER VARCHAR2(50),
NAME VARCHAR2(250),
STATUS VARCHAR2(30) ,
CREATE_DT TIMESTAMP(6),
UPDT_DT TIMESTAMP(6)
)
alter table ACC add primark key(ID);
Organization :
Create table Organization
(
ROW_ID NOT NULL VARCHAR2(15),
POSTN_ID VARCHAR2(15) ,
LOC VARCHAR2(50) ,
NAME NOT NULL VARCHAR2(100),
STAT_CD VARCHAR2(30) ,
TYPE_CD VARCHAR2(30),
SF_ACCOUNTID VARCHAR2(21)
);
there is a trigger on ACC table
Trigger:
create or replace TRIGGER ACC_INS_UPD_TRG
BEFORE INSERT OR UPDATE ON ACC
FOR EACH ROW
BEGIN
IF INSERTING THEN
:new.create_DT := sysdate;
END IF;
IF UPDATING THEN
:new.updt_dt := sysdate;
END IF;
END;
View as follows.
CREATE OR REPLACE FORCE VIEW ACC_VW ("ID", "OWNER_ID", "S_KEY__C", "ACC_NUMBER", "NAME", "STATUSC") AS
select distinct dense_rank() over (order by row_id) as ID ,
null as owner_id,
s.row_id as s_key__c,
s.loc as acc_number,
s.name as name,
s.STAT_CD as STATUS__C
from siebel.ORGANIZATION_SIE@SIEBEL_READ_ONLY.COM s;
siebel.ORGANIZATION_SIE table structure is as follows.
create tale siebel.ORGANIZATION_SIE
(
ROW_ID NOT NULL VARCHAR2(15) ,
POSTN_ID VARCHAR2(15) ,
LOC VARCHAR2(50) ,
NAME NOT NULL VARCHAR2(100),
STAT_CD VARCHAR2(30) ,
OU_TYPE_CD VARCHAR2(30),
SF_ACCOUNTID VARCHAR2(21)
);
Now the requirement is to Insert data from ACC to ORGANIZATION by comparing ACC.S_KEY__C = ORGANIZATION.ROW_ID
I would like to write a stored procedure to Inserting new rows and updating existing rows from ACC to ORGANIZATION.
Please advise.
Thanks,
CM: fixed the code tags, end code tag should be [/code]
[Updated on: Tue, 03 November 2015 03:16] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: stored procedure to Inserting new rows and updating existing rows [message #644289 is a reply to message #644286] |
Mon, 02 November 2015 12:19 |
|
amy.wilson
Messages: 39 Registered: September 2015 Location: Www
|
Member |
|
|
I am not gettting how to declare a cursor to process all rows and how can we fulfill the required thing. But have written sampleprocedure. Could you please look into it and advise
create or replace procedure acc_org_proc
/*cursor c1 is
select * from acc;
rec c1%rowtype;
begin
open c1
loop
fetch c1 into rec;*/
begin
merge into organization rs
using acc a
on (a.s_key__c = rs.row_id)
when matched then update set
rs.name = a.name,
rs.stat_cd = a.status__c
rs.sf_accountid = a.accnumber
wben not matched then
insert values(id,name,stat_cd,sf_accountid)
values(a.id,a.status__c,a.accnumber);
exception
when no_data_found then
dbms_output.put_line('No data existed');
end;
am sure.. this onw won't work.. Could you please advise . WHich should compare row by row ad then it has to update the existing rows and insert new values.
Thanks.
[Updated on: Mon, 02 November 2015 12:20] Report message to a moderator
|
|
|
|
|
|