Home » SQL & PL/SQL » SQL & PL/SQL » return to For loop (Oracle 9)
return to For loop [message #376329] Wed, 17 December 2008 00:45 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
BEGIN
 	 SELECT count(PASO) INTO PASO_COUNT FROM NPPI_PURGADO_P;
	 FOR COUNTER IN 1..PASO_COUNT
 	 LOOP
	       if (condition)
	        SELECT REGISTROS_INICIALES INTO INICIAL_COUNT FROM NPPI_PURGADO_S  
WHERE PASO = COUNTER AND FECHA_INICIO IN 
(SELECT MIN(FECHA_INICIO) FROM NPPI_PURGADO_S) ;
   	 	        IF INICIAL_COUNT = NULL THEN
   	        		EXIT;
   		       ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       END IF;
   	     ELSE

			SELECT REGISTROS_INICIALES INTO INICIAL_COUNT 
FROM NPPI_PURGADO_S 
WHERE PASO = COUNTER AND FECHA_INICIO > FECHA_INICIO_GET;
   			  IF INICIAL_COUNT = NULL THEN
				EXIT;
			ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       	END IF;
	   END IF;
     END LOOP;
exception block
end;


After
IF INICIAL_COUNT = NULL THEN
, I want to again reach the for loop after incrementing count. But not able to do so, I think its justing taking 1 and after that its not iterating.

Suggest something, please tell me if i need to give more inputs here.

Thanks
Shaksing

[Updated on: Wed, 17 December 2008 00:47] by Moderator

Report message to a moderator

Re: return to For loop [message #376331 is a reply to message #376329] Wed, 17 December 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 character width. Use the "Preview Message" button to verify.

Are you aware that between "SELECT count(PASO)..." and "FOR..." the count may have changed?

Quote:
I want to again reach the for loop after incrementing count

You do it unless you exit.

Regards
Michel
Re: return to For loop [message #376334 is a reply to message #376329] Wed, 17 December 2008 00:59 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi Michel
I didnot got that what you just said select count(paso).
Here paso is 20 and that i will put in paso_count.

What can i do to iterate the loop here.Please tell me the changes to be made.
Re: return to For loop [message #376337 is a reply to message #376329] Wed, 17 December 2008 01:11 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi shaksing,

You condition may be restricting the loop to continue.

SELECT registros_iniciales
        INTO inicial_count
        FROM nppi_purgado_s
       WHERE paso = counter AND fecha_inicio IN (SELECT MIN (fecha_inicio)
                                                   FROM nppi_purgado_s);

or 

SELECT registros_iniciales
        INTO inicial_count
        FROM nppi_purgado_s
       WHERE paso = counter AND fecha_inicio > fecha_inicio_get


here registros_iniciales must be null. Please check the condition again.

Thanks
Trivendra
Re: return to For loop [message #376341 is a reply to message #376329] Wed, 17 December 2008 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure that the "SELECT ... INTO inicial_count" returns something (i.e. NULL, at least)? Is it possible that it will, actually, return nothing? If so, you'll run into the NO-DATA-FOUND exception. Do you want to handle it within the loop, or is the "main" EXCEPTION handler section enough?
Re: return to For loop [message #376342 is a reply to message #376334] Wed, 17 December 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I didnot got that what you just said select count(paso).

After you count, then the count may change that is you loop on a wrong count (that you stored inside paso_count).

Quote:
What can i do to iterate the loop here

There is nothing to do, you loop... unless you explicitly (see EXIT statement) or implicitly (get an exception) exit.

Regards
Michel

[Updated on: Wed, 17 December 2008 01:21]

Report message to a moderator

Re: return to For loop [message #376345 is a reply to message #376329] Wed, 17 December 2008 01:27 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah count(paso) returns which i came in paso_count,
I have checked by putting a
DBMS_OUTPUT.PUT_LINE(pas_count);
and it gives 20.

Yes i kept
EXCEPTION

   
   WHEN NO_DATA_FOUND
   THEN
   raise_application_error (-20001,' NO HAY DATOS PARA PASAR A HISTORICO');
Re: return to For loop [message #376349 is a reply to message #376329] Wed, 17 December 2008 01:37 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Here you condition for count = 1 should be returning NULL values in inicial_count.


DECLARE
  inicial_count   VARCHAR2 (10);
BEGIN
  SELECT registros_iniciales
    INTO inicial_count
    FROM nppi_purgado_s
   WHERE paso = 1 AND fecha_inicio IN (SELECT MIN (fecha_inicio)
                                               FROM nppi_purgado_s);

  IF inicial_count IS NULL
  THEN
    raise_application_error (-20009, 'inicial_count is null');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    raise_application_error (-20019, 'NO data found');
END;


Thanks
Trivendra
Re: return to For loop [message #376371 is a reply to message #376331] Wed, 17 December 2008 02:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Michel sir is saying that
Quote:
between "SELECT count(PASO)..." and "FOR..." the count may have changed?



but there is not even a single statement between them.
so how could it possible?
so there isnt any chance of changing the value of PASO_COUNT

Please justify.
Re: return to For loop [message #376374 is a reply to message #376329] Wed, 17 December 2008 03:02 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi Michel,

I have checked paso_count that gives me 20.
Also i have a exception for NO_DATA_FOUND.I think its just checking for paso_count=1, where it doesn't get data when executing the line
SELECT registros_iniciales
    INTO inicial_count
    FROM nppi_purgado_s
   WHERE paso = 1 AND fecha_inicio IN (SELECT MIN (fecha_inicio)
                                               FROM nppi_purgado_s);


so it goes to NO_DATA_FOUND block. Now i want hear to start the loop with paso_count =2 , that is what i am not able to iterarte here.

Please provide some inputs.



Re: return to For loop [message #376375 is a reply to message #376371] Wed, 17 December 2008 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Databases generally have more than one user.
It is entirely possible for the number of records that match the count condition to change the instant the query counting them has been executed.

Michel is entirely correct.

As to the problem at hand:

There is no way (unles you're using 11g) to restart the loop in the way that you want.

What you need to do is to catch the NO_DATA_FOUND exception inside the loop. That way after you handle the exception, the loop will continue to process:

FOR i in 1..20 LOOP
  BEGIN
    <some code here>
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      <handle exception>
  END;
END LOOP;
Re: return to For loop [message #376376 is a reply to message #376329] Wed, 17 December 2008 03:10 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Put Begin and End in FOR loop

FOR COUNTER IN 1..PASO_COUNT
 	 LOOP
	 begin 
	       if (condition)
	        SELECT REGISTROS_INICIALES INTO INICIAL_COUNT FROM NPPI_PURGADO_S  
WHERE PASO = COUNTER AND FECHA_INICIO IN 
(SELECT MIN(FECHA_INICIO) FROM NPPI_PURGADO_S) ;
   	 	        IF INICIAL_COUNT = NULL THEN
   	        		EXIT;
   		       ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       END IF;
   	     ELSE

			SELECT REGISTROS_INICIALES INTO INICIAL_COUNT 
FROM NPPI_PURGADO_S 
WHERE PASO = COUNTER AND FECHA_INICIO > FECHA_INICIO_GET;
   			  IF INICIAL_COUNT = NULL THEN
				EXIT;
			ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       	END IF;
	   END IF;
exception 
when no_data_found then 
null;
end ;
end loop;


Thanks
Trivendra

[Updated on: Wed, 17 December 2008 03:14]

Report message to a moderator

Re: return to For loop [message #376387 is a reply to message #376329] Wed, 17 December 2008 03:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
IF INICIAL_COUNT = NULL THEN
  EXIT;
ELSE
  --something
END IF;

Just two comments on the above code. As the first condition is never TRUE, the EXIT never happens. As EXIT statement breaks out of a loop and you do not want this, I do not know why you use it. Fortunately for you, it never happens.

Use IS (NOT) NULL to check for NULL (which is probably not correct in this case, as NO_DATA_FOUND exception is raised when no rows are selected). CONTINUE was introduced to in 11g to continue in loop; in earlier versions you shall adjust the code (e.g. change the previous code part to
IF INICIAL_COUNT IS NOT NULL THEN
  --something
END IF;
, introduce labels, ...).
Re: return to For loop [message #376389 is a reply to message #376329] Wed, 17 December 2008 03:32 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Perfect !!!!!!
Re: return to For loop [message #376398 is a reply to message #376371] Wed, 17 December 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
delna.sexy wrote on Wed, 17 December 2008 09:45
Michel sir is saying that
Quote:
between "SELECT count(PASO)..." and "FOR..." the count may have changed?



but there is not even a single statement between them.
so how could it possible?
so there isnt any chance of changing the value of PASO_COUNT

Please justify.

I didn't say the value inside the variable changes but the count itself changes, the count inside the database. So the procedure may work on a count that is wrong.

Regards
Michel

Re: return to For loop [message #376456 is a reply to message #376398] Wed, 17 December 2008 07:08 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok sir
Now I got it.
Thanks.

regards,
Delna
Re: return to For loop [message #376458 is a reply to message #376329] Wed, 17 December 2008 07:15 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi flyboy

i tried labels like that.
BEGIN
 	 SELECT count(PASO) INTO PASO_COUNT FROM NPPI_PURGADO_P;
	<<OUTERLOOP>> 
FOR COUNTER IN 1..PASO_COUNT
 	 LOOP
	       if (condition)
	        SELECT REGISTROS_INICIALES INTO INICIAL_COUNT FROM NPPI_PURGADO_S  
WHERE PASO = COUNTER AND FECHA_INICIO IN 
(SELECT MIN(FECHA_INICIO) FROM NPPI_PURGADO_S) ;
   	 	        EXIT OUTERLOOP WHEN INICIAL_COUNT <= 0 ;
   		       ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       END IF;
   	     ELSE

			SELECT REGISTROS_INICIALES INTO INICIAL_COUNT 
FROM NPPI_PURGADO_S 
WHERE PASO = COUNTER AND FECHA_INICIO > FECHA_INICIO_GET;

EXIT OUTERLOOP WHEN INICIAL_COUNT <= 0 ;
			ELSE
				UPDATE NPPI_PURGADO_S SET (parameters)........
     		       	END IF;
	   END IF;
     END LOOP;
exception block
end loop outerloop;
end;


But still its not working. Its saying Outerloop is not expected ..

Can we trace it through a nested exception?

Actually
SELECT REGISTROS_INICIALES INTO INICIAL_COUNT 
FROM NPPI_PURGADO_S 
WHERE PASO = COUNTER AND FECHA_INICIO > FECHA_INICIO_GET;


is going to exception block of no data found , and i want to start again with count incremented.
Re: return to For loop [message #376467 is a reply to message #376329] Wed, 17 December 2008 07:54 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
But still its not working. Its saying Outerloop is not expected ..

The label name is missing after END LOOP, see the example in the end of http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems19.htm#33989.
Also there is no need of ELSE/END IF construction.
As EXIT statement statement breaks out of a loop (the same as uncaught exception behaves), I wonder why you use it. Maybe I was not clear as I meant GOTO statement to the label placed before the first (or behind the last) command inside the LOOP.
Quote:
is going to exception block of no data found , and i want to start again with count incremented.

Catch the NO_DATA_FOUND exception inside the LOOP, as suggested by JRowbottom.
Previous Topic: Better way of checking existence of record in query/table
Next Topic: sql problem (merged)
Goto Forum:
  


Current Time: Sun Dec 04 13:06:20 CST 2016

Total time taken to generate the page: 0.26385 seconds