Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure to Inserting new rows and updating existing rows (11.2.0.3)
stored procedure to Inserting new rows and updating existing rows [message #644285] Mon, 02 November 2015 11:06 Go to next message
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 #644286 is a reply to message #644285] Mon, 02 November 2015 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use MERGE.

Re: stored procedure to Inserting new rows and updating existing rows [message #644287 is a reply to message #644285] Mon, 02 November 2015 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/3822561
Re: stored procedure to Inserting new rows and updating existing rows [message #644288 is a reply to message #644287] Mon, 02 November 2015 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Seems OP is not more able to properly post code in Oracle community than here. Laughing

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 Go to previous messageGo to next message
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

Re: stored procedure to Inserting new rows and updating existing rows [message #644290 is a reply to message #644289] Mon, 02 November 2015 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NEVER do in PL/SQL, that which can be done in plain SQL

https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606
Re: stored procedure to Inserting new rows and updating existing rows [message #644332 is a reply to message #644289] Tue, 03 November 2015 07:35 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
As suggested, you can use merge .
Less details are mentioned in the post like insert statements, constraints etc.
And at the same time extra details are mentioned than required like the view, ORGANIZATION_SIE
Re: stored procedure to Inserting new rows and updating existing rows [message #644333 is a reply to message #644289] Tue, 03 November 2015 07:39 Go to previous message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Had you tested the code before posting? seems like there are spelling mistakes (eventually syntax error).
Previous Topic: Test query for SORT (merged)
Next Topic: Complex query (merged)
Goto Forum:
  


Current Time: Mon Mar 18 21:48:45 CDT 2024