Home » SQL & PL/SQL » SQL & PL/SQL » ROW COUNTER for update, insert and reject... :(
icon9.gif  ROW COUNTER for update, insert and reject... :( [message #299410] Mon, 11 February 2008 23:02 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi there..

I am tired of repairing my script... Sad

create or replace procedure P_CABINET_D_SIDE_PAIR_UPDATE as

cursor A is
SELECT
TO_NUMBER(FRAME_APPEARANCES.FRAA_POSITION)  DSIDE_PAIR_NUM ,
   -- dwhdv.NIS_CABINET_D_SIDE_PAIR.TO_GET_CABINET_FROM_FRM_APPR(FRAME_APPEARANCES.FRAA_ID) CABINET_IDENT_CODE ,
FRAC_LOCN_TTNAME CABINET_IDENT_CODE,
FRAME_APPEARANCES.FRAA_STATUS INVENT_STATUS_CODE    ,
dwhdv.NIS_CABINET_D_SIDE_PAIR.AUTHORIZATION_CODE(FRAC_LOCN_TTNAME)  AUTHORIZATION_CODE,
CASE
 WHEN FRAME_APPEARANCES.FRAA_USAGE='VOICE' THEN
'B'
 END LOADED_PAIR_IND
FROM
FRAME_CONTAINERS,
FRAME_APPEARANCES ,
FRAME_UNITS
WHERE
(
    FRAME_CONTAINERS.FRAC_ID = FRAME_UNITS.FRAU_FRAC_ID
AND FRAME_UNITS.FRAU_ID = FRAME_APPEARANCES.FRAA_FRAU_ID
)
AND
 FRAME_UNITS.FRAU_NAME= 'DSIDE'	
AND FRAU_DIRECTION = 'OUT'	
 AND FRAA_SIDE='FRONT'
AND FRAC_LOCN_TTNAME='KLC_001';
/* SELECT FRAME_APPEARANCES.FRAA_POSITION DSIDE_PAIR_NUM ,
LOCATIONS.LOCN_TTNAME CABINET_IDENT_CODE   ,
FRAME_APPEARANCES.FRAA_STATUS INVENT_STATUS_CODE ,
dwhdv.NIS_CABINET_D_SIDE_PAIR.AUTHORIZATION_CODE(FRAC_LOCN_TTNAME) AUTHORIZATION_CODE,
case
when FRAA_STATUS like 'VOICE'  then 'Y'
else
'N'
end LOADED_PAIR_IND
FROM EQUIPMENT, LOCATIONS , FRAME_CONTAINERS , FRAME_UNITS , FRAME_APPEARANCES
WHERE  (FRAME_UNITS.FRAU_ID = FRAME_APPEARANCES.FRAA_FRAU_ID  AND
FRAME_CONTAINERS.FRAC_ID = FRAME_UNITS.FRAU_FRAC_ID  AND
LOCATIONS.LOCN_TTNAME = FRAME_CONTAINERS.FRAC_LOCN_TTNAME AND
EQUIPMENT.EQUP_LOCN_TTNAME= LOCATIONS.LOCN_TTNAME); */

AREC A%rowtype;

row_update number(10):=0;
row_insert number(10):=0;
row_reject number(10):=0;
i number := 0;
j number := 0;
v_ErrorCode number;
v_ErrorText varchar2(200);

begin


  open A;

  DBMS_OUTPUT.PUT_LINE('Start_Time1: '||to_char(sysdate, 'DD-MON-YY HH:MI:SS'));

  loop
  fetch A into AREC;
  exit when A%NOTFOUND;

	begin
	
	update CABINET_D_SIDE_PAIR set
	DSIDE_PAIR_NUM = AREC.DSIDE_PAIR_NUM,
	CABINET_IDENT_CODE = AREC.CABINET_IDENT_CODE,
	INVENT_STATUS_CODE = AREC.INVENT_STATUS_CODE,
	AUTHORIZATION_CODE = AREC.AUTHORIZATION_CODE,
	LOADED_PAIR_IND = AREC.LOADED_PAIR_IND,
	loading_date = sysdate,
	source = 'NIS_TEST'
	WHERE
	DSIDE_PAIR_NUM = AREC.DSIDE_PAIR_NUM and CABINET_IDENT_CODE = AREC.CABINET_IDENT_CODE;
	
	row_update := row_update+1;
	
	if sql%notfound then
	
	P_CABINET_D_SIDE_PAIR_INSERT (AREC.DSIDE_PAIR_NUM, AREC.CABINET_IDENT_CODE, AREC.INVENT_STATUS_CODE,
	AREC.AUTHORIZATION_CODE, AREC.LOADED_PAIR_IND);
	
	row_insert := row_insert+1;
	
	end if;
		

	exception when others then
	
	row_reject:=row_reject+1;
	v_ErrorCode :=SQLCODE;
	v_ErrorText :=SUBSTR(SQLERRM,1,200);
	DBMS_OUTPUT.PUT_LINE('Rejected: '||  AREC.DSIDE_PAIR_NUM);
	
	end;
	
	END LOOP;

   close A;
   
   		if i>500 then
		commit;
		i:=0;
		end if;

 	DBMS_OUTPUT.PUT_LINE('End_Time1: '||to_char(sysdate, 'DD-MON-YY HH:MI:SS'));
     dbms_output.put_line(chr(0));
	 dbms_output.put_line('UPDATE for CABINET_D_SIDE_PAIR');
     dbms_output.put_line('-------------------------------------------------');
     dbms_output.put_line(chr(0));
     DBMS_OUTPUT.PUT_LINE (row_update ||' rows have been updated!');
	 DBMS_OUTPUT.PUT_LINE (row_insert ||' rows have been inserted!');
	 DBMS_OUTPUT.PUT_LINE (row_reject ||' rows have been rejected!');
	 dbms_output.put_line(chr(0));
	
   commit;

DBMS_OUTPUT.PUT_LINE('End_Time1: '||to_char(sysdate, 'DD-MON-YY HH:MI:SS'));

end P_CABINET_D_SIDE_PAIR_UPDATE;

This is the main procedure that will be executed - P_CABINET_D_SIDE_PAIR_UPDATE.

If the data is already there, an update will be issued. If not, it will call another procedure to insert - P_CABINET_D_SIDE_PAIR_INSERT

create or replace procedure P_CABINET_D_SIDE_PAIR_INSERT
(AREC_DSIDE_PAIR_NUM CABINET_D_SIDE_PAIR.DSIDE_PAIR_NUM%type, AREC_CABINET_IDENT_CODE
CABINET_D_SIDE_PAIR.CABINET_IDENT_CODE%type, AREC_INVENT_STATUS_CODE 
CABINET_D_SIDE_PAIR.INVENT_STATUS_CODE%type, AREC_AUTHORIZATION_CODE 
CABINET_D_SIDE_PAIR.AUTHORIZATION_CODE%type, AREC_LOADED_PAIR_IND CABINET_D_SIDE_PAIR.LOADED_PAIR_IND%type)
AS
PRAGMA AUTONOMOUS_TRANSACTION;

begin
	
	  insert into CABINET_D_SIDE_PAIR
	  (DSIDE_PAIR_NUM, CABINET_IDENT_CODE, INVENT_STATUS_CODE, AUTHORIZATION_CODE, LOADED_PAIR_IND,
	  loading_date, source)
	  values
	  (AREC_DSIDE_PAIR_NUM, AREC_CABINET_IDENT_CODE, AREC_INVENT_STATUS_CODE, AREC_AUTHORIZATION_CODE, 
	  AREC_LOADED_PAIR_IND, sysdate, 'NIS_TEST');
	  
	  commit;
	    	
END P_CABINET_D_SIDE_PAIR_INSERT;


My problem is that.. How do I set the counter properly so that I will know exactly how many rows is being updated and how many is being inserted. As well as number of rejected rows.

Please help me.. I am totally befuddled.. Confused

Thank you very much.

[Updated on: Mon, 11 February 2008 23:04]

Report message to a moderator

Re: ROW COUNTER for update, insert and reject... :( [message #299411 is a reply to message #299410] Mon, 11 February 2008 23:06 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
In a more simpler structure...
create or replace procedure P_CABINET_D_SIDE_PAIR_UPDATE as

cursor A is
SELECT
statement;

AREC A%rowtype;

row_update number(10):=0;
row_insert number(10):=0;
row_reject number(10):=0;
v_ErrorCode number;
v_ErrorText varchar2(200);

begin


  open A;

  loop
  fetch A into AREC;
  exit when A%NOTFOUND;

	begin
	
	update statement 
	WHERE
	DSIDE_PAIR_NUM = AREC.DSIDE_PAIR_NUM and CABINET_IDENT_CODE = AREC.CABINET_IDENT_CODE;
	
	row_update := row_update+1;
	
	if sql%notfound then
	
	P_CABINET_D_SIDE_PAIR_INSERT (para1, para2, ....);
	
	row_insert := row_insert+1;
	
	end if;
		

	exception when others then
	
	row_reject:=row_reject+1;
	v_ErrorCode :=SQLCODE;
	v_ErrorText :=SUBSTR(SQLERRM,1,200);
	DBMS_OUTPUT.PUT_LINE('Rejected: '||  AREC.DSIDE_PAIR_NUM);
	
	end;
	
	END LOOP;

   close A;
   
   		if i>500 then
		commit;
		i:=0;
		end if;


     dbms_output.put_line(chr(0));
	 dbms_output.put_line('UPDATE for CABINET_D_SIDE_PAIR');
     dbms_output.put_line('-------------------------------------------------');
     dbms_output.put_line(chr(0));
     DBMS_OUTPUT.PUT_LINE (row_update ||' rows have been updated!');
	 DBMS_OUTPUT.PUT_LINE (row_insert ||' rows have been inserted!');
	 DBMS_OUTPUT.PUT_LINE (row_reject ||' rows have been rejected!');
	 dbms_output.put_line(chr(0));
	
   commit;


end P_CABINET_D_SIDE_PAIR_UPDATE;

[Updated on: Mon, 11 February 2008 23:07]

Report message to a moderator

Re: ROW COUNTER for update, insert and reject... :( [message #299425 is a reply to message #299411] Tue, 12 February 2008 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What's with the conditional commit??
The next step is an unconditional commit!

You could probably do this in a single merge, but that would not give you the number of inserts/updates. Why do you need those anyway?

[Updated on: Tue, 12 February 2008 00:44]

Report message to a moderator

Re: ROW COUNTER for update, insert and reject... :( [message #299435 is a reply to message #299425] Tue, 12 February 2008 01:03 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Frank wrote on Tue, 12 February 2008 14:42
What's with the conditional commit??
The next step is an unconditional commit!


You could probably do this in a single merge, but that would not give you the number of inserts/updates. Why do you need those anyway?

Thanks Frank.

But I'm sorry I didn't get it (the bold part). What do you mean?

I need to know the number of updates/inserts so that I could track down the records. Indirectly, it could tell me whether the script is working as expected or not.

Thank you.
Re: ROW COUNTER for update, insert and reject... :( [message #299438 is a reply to message #299435] Tue, 12 February 2008 01:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
About the conditional commit: what happens (for the whole script) if i < 500?
Previous Topic: converting char type to date type?
Next Topic: Looking for guidlines
Goto Forum:
  


Current Time: Fri Dec 09 06:19:00 CST 2016

Total time taken to generate the page: 0.18735 seconds