| return to For loop [message #376329] |
Wed, 17 December 2008 00:45  |
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
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 #376334 is a reply to message #376329] |
Wed, 17 December 2008 00:59  |
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 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 #376342 is a reply to message #376334] |
Wed, 17 December 2008 01:21 |
 |
Michel Cadot
Messages: 68645
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 |
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);
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 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 #376374 is a reply to message #376329] |
Wed, 17 December 2008 03:02 |
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 |
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 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 |
flyboy
Messages: 1903
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; |
|
|
|
|
|
|
|
|
|
| Re: return to For loop [message #376458 is a reply to message #376329] |
Wed, 17 December 2008 07:15 |
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 |
flyboy
Messages: 1903
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
