| compare two (merged) [message #230959] |
Fri, 13 April 2007 09:53  |
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 #230963 is a reply to message #230961] |
Fri, 13 April 2007 10:27  |
Bill B
Messages: 1971
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 #230972 is a reply to message #230967] |
Fri, 13 April 2007 10:43 |
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;
|
|
|
|
|
|
| compare two [message #230997 is a reply to message #230959] |
Fri, 13 April 2007 11:55 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
