Home » SQL & PL/SQL » SQL & PL/SQL » compare two (merged)
compare two (merged) [message #230959] Fri, 13 April 2007 09:53 Go to next message
Priya_reddy
Messages: 11
Registered: April 2007
Junior Member
create or replace PROCEDURE REFRESH_REF_DATA_HLDY AS



CURSOR C1 is

SELECT * FROM HLDY

ORDER BY HLDY_ID;



CURSOR C2 is

SELECT * FROM RL_HLDY

ORDER BY HLDY_ID;





BEGIN



FOR R1 IN C1 LOOP

FOR R2 IN C2 LOOP



IF R1.HLDY_ID <> R2.HLDY_ID THEN

DBMS_OUTPUT.PUT_LINE( 'DIFFERENCES:'|| R1.HLDY_ID||'-'||R2.HLDY_ID);

END IF;



END LOOP;

END LOOP;



END;

Re: Please correct me [message #230961 is a reply to message #230959] Fri, 13 April 2007 09:56 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
DBMS_OPUTPUT is of no use in a stored procedure. You will never see any output.
Re: Please correct me [message #230963 is a reply to message #230961] Fri, 13 April 2007 10:27 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
joy_division wrote on Fri, 13 April 2007 09:56
DBMS_OPUTPUT is of no use in a stored procedure. You will never see any output.


Not totally true, you could set serveroutput on in sql*plus before running the procedure locally (great for debugging), but the procedure will not return the values back to the calling application so that it can be acted on.
Re: Please correct me [message #230967 is a reply to message #230959] Fri, 13 April 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the procedure does not do what you want. Even if you use the same table for both cursors you will have many rows displayed.

Moreover, why not using directly SQL?

Regards
Michel
Re: Please correct me [message #230972 is a reply to message #230967] Fri, 13 April 2007 10:43 Go to previous messageGo to next message
Priya_reddy
Messages: 11
Registered: April 2007
Junior Member
I have to write procedure to compare two tables a table (for example CTRY) and its target table (RL_CTRY) and update the rows when there is a difference

I write this
Please correct me

------------------------------------------------------------
create or replace PROCEDURE REFRESH_REF_DATA_HLDY AS

CURSOR C1 is

SELECT * FROM HLDY

ORDER BY HLDY_ID;

CURSOR C2 is

SELECT * FROM RL_HLDY

ORDER BY HLDY_ID;

BEGIN

FOR R1 IN C1 LOOP

FOR R2 IN C2 LOOP

IF R1.HLDY_ID <> R2.HLDY_ID THEN

DBMS_OUTPUT.PUT_LINE( 'DIFFERENCES:'|| R1.HLDY_ID||'-'||R2.HLDY_ID);

END IF;

END LOOP;

END LOOP;

END;
Re: Please correct me [message #230974 is a reply to message #230959] Fri, 13 April 2007 10:47 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Read up on the merge command, it will do what you want. See the following link.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081218
compare two [message #230997 is a reply to message #230959] Fri, 13 April 2007 11:55 Go to previous message
Priya_reddy
Messages: 11
Registered: April 2007
Junior Member
I have to write procedure to compare two tables a table(CTRY) and its target table (RL_CTRY)

1. Update the rows CTRY table which match the target table

2. rows is in table (ctry) that is not in the master table (rl_ctry), list the row in the temporary table and delete the row from the CTRY table
Previous Topic: procedure with defautl parameter values error!!!!!
Next Topic: Nested SELECT??? and MINUS/NEST - stumped!!!! (merged 2 threads)
Goto Forum:
  


Current Time: Thu Dec 08 02:36:02 CST 2016

Total time taken to generate the page: 0.09422 seconds