Home » SQL & PL/SQL » SQL & PL/SQL » Help with a indifinate loop
Help with a indifinate loop [message #36975] Tue, 08 January 2002 11:08 Go to next message
Lance Pris
Messages: 40
Registered: January 2002
Member
The following stored procedure is going into an infinite loop with out modifying the anything.

Could someone please try to see what I am doing wrong? I have been working at this for a while now and cannot figure out the problem.

CREATE OR REPLACE procedure Primus_Dump
AS
solution primus_temp_dump.solution_id%type; /* Or something
with the same column type */
Cursor Primus_solution is
Select Solution_Id AS SOLUTION /* Delete as solution */
from Primus_Temp_Dump;
Begin
Open Primus_solution;
loop /* Add next three lines */
fetch primus_solution into solution;
exit when primus_solution%NOTFOUND;
Update Primus_Temp_Dump set owner =(
Select c.pc_name
from pt_solution a, pt_solution_prop b,
pt_member c
where b.pc_owner = c.pc_secure_id
and b.pc$sol_secure_id = a.pc_secure_id
and a.pc_solution_id = solution)
where solution_id = solution ;
Print :solution;****************
End LOOP;
Close Primus_solution;
END;
/
Re: Help with a indifinate loop [message #36976 is a reply to message #36975] Tue, 08 January 2002 12:35 Go to previous message
kriser
Messages: 11
Registered: October 2001
Junior Member
Lance

This appears to be what you are trying to do. If it's not, hopefully you can get some ideas from this.
You will probably want to start only having the dbms_output.put_line (print) inside the loop to see if the results of the query are what you are looking for before using the Update statement. I hope this helps.

Try something like this:

CREATE OR REPLACE
PROCEDURE Primus_Dump
AS

CURSOR Primus_solution
IS

SELECT c.pc_name
FROM pt_solution a, pt_solution_prop b, pt_member c
WHERE b.pc_owner = c.pc_secure_id
AND b.pc$sol_secure_id = a.pc_secure_id
AND and a.pc_solution_id IN (Select Solution_ID from Temp_Dump);

-------
Solution Primus_solution%rowtype; /*set variable equal to cursor type*/

BEGIN

FOR Solution IN Primus_solution /*this will automatically handle the open, fetch, and close for you*/
LOOP

UPDATE Primus_Temp_Dump
SET owner = Solution.pc_name;

dbms_output.put_line (solution); /* make sure dbms_output is turned on. If you have a large amount of data being printed you may have to increase the buffer size. */

END LOOP;

END;
/

Hope this helps,
Kyle
Previous Topic: Using a text file in PlSql
Next Topic: outout a SQL Statment to the screen
Goto Forum:
  


Current Time: Sun May 31 17:18:49 CDT 2020