Home » SQL & PL/SQL » SQL & PL/SQL » "%NOTFOUND" behaving weird
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112446 is a reply to message #112409] |
Fri, 25 March 2005 08:52   |
developer999
Messages: 4
Registered: March 2005
|
Junior Member |
|
|
Ok here's the code :.............
The cursor looks something like this :
CURSOR c_amounts (c_cycle_date DATE, c_acc_code VARCHAR2, c_div_code VARCHAR2) IS
SELECT UV_NAV, UV_UV, UV_DIVIDEND, UV_UNITS
FROM T_LIUV_UNIT_VALUE
WHERE UV_UV_DATE = c_cycle_date
AND UV_ACC_CODE = c_acc_code
AND UV_DIV_CODE = c_div_code;
The main body code that calls the cursor looks something like this :
OPEN c_amounts(v_cycle_date, v_acc_code, v_div_code);
FETCH c_amounts INTO v_uv_nav, v_uv_uv, v_uv_dividend, v_uv_units;
v_prev_date := v_cycle_date - 1;
/* Note that this code is commented, and was put just to check if c_amounts%NOTFOUND was true
if c_amounts%NOTFOUND then
dbms_output.put_line('****#######################');
end if;
*/
WHILE c_amounts%NOTFOUND LOOP
/* Until data found search for previous dates */
BEGIN
CLOSE c_amounts;
OPEN c_amounts(v_prev_date, cur_fund_rec.c_acc_code, cur_fund_rec.c_div_code);
FETCH c_amounts INTO v_uv_nav, v_uv_uv, v_uv_dividend, v_uv_units;
v_prev_date := v_prev_date - 1;
END;
END LOOP;
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112462 is a reply to message #112446] |
Fri, 25 March 2005 11:50 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
There are various problems with trying to use cursor%notfound in the manner that you are trying to use it. A better way is to use a loop and, after fetching within the loop, exit when cursor%found. You will need to remember to close the cursor within the loop in the event that no data is found and again outside the loop in the event that data is found and the loop is exited before it would otherwise be closed within the loop. It is also a good practice to check if the cursor is open at the beginning of your code using cursor%isopen and close it if it is open. That way if you are experimenting with your code and end up killing your session before the cursor is closed or some such thing, then you can re-run it without problems. The check using cursor%isopen is done, rathen than just closing it, because if it is not open and you try to close it, then that also raises an error.
One of the reasons that you may be having a problem with an endless loop is that remember dates contain a time portion, even if only the date portion is displayed due to the date format. So, if you do not include trunc when comparing dates, then your dates may never match, and it will keep subtracting one from the date forever. It is also a good idea to include an additional exit condition, such as when it reaches some date like Jan 01, 1901, where you know you will not have any more data.
Please see the demonstration using the suggested code below, in which I have applied the changes mentioned above and done two executions, the first of which finds the data with the first matching date and the second of which exits after not finding any data.
-- table and data for testing:
scott@ORA92> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
2 /
Session altered.
scott@ORA92> SELECT * FROM t_liuv_unit_value
2 /
UV_UV_DATE UV_ACC_CODE UV_DIV_CODE UV_NAV UV_UV UV_DIVIDEND UV_UNITS
-------------------- ----------- ----------- ---------- ---------- ----------- ----------
22-MAR-2005 08:34:14 uvacccode uvdivcode 1 2 3 4
21-MAR-2005 08:34:14 uvacccode uvdivcode 5 6 7 8
-- procedure:
scott@ORA92> CREATE OR REPLACE PROCEDURE your_procedure
2 (p_cycle_date IN DATE,
3 p_acc_code IN VARCHAR2,
4 p_div_code IN VARCHAR2)
5 AS
6 v_cycle_date DATE := TRUNC (p_cycle_date);
7 v_acc_code t_liuv_unit_value.uv_acc_code%TYPE := p_acc_code;
8 v_div_code t_liuv_unit_value.uv_div_code%TYPE := p_div_code;
9 v_uv_nav t_liuv_unit_value.uv_nav%TYPE;
10 v_uv_uv t_liuv_unit_value.uv_uv%TYPE;
11 v_uv_dividend t_liuv_unit_value.uv_dividend%TYPE;
12 v_uv_units t_liuv_unit_value.uv_units%TYPE;
13 CURSOR c_amounts
14 (c_cycle_date DATE,
15 c_acc_code VARCHAR2,
16 c_div_code VARCHAR2)
17 IS
18 SELECT UV_NAV, UV_UV, UV_DIVIDEND, UV_UNITS
19 FROM T_LIUV_UNIT_VALUE
20 WHERE TRUNC (UV_UV_DATE) = c_cycle_date
21 AND UV_ACC_CODE = c_acc_code
22 AND UV_DIV_CODE = c_div_code;
23 BEGIN
24 IF c_amounts%ISOPEN
25 THEN CLOSE c_amounts;
26 END IF;
27 LOOP
28 OPEN c_amounts (v_cycle_date, v_acc_code, v_div_code);
29 FETCH c_amounts INTO v_uv_nav, v_uv_uv, v_uv_dividend, v_uv_units;
30 EXIT WHEN c_amounts%FOUND OR v_cycle_date < TO_DATE ('01-JAN-1901', 'DD-MON-YYYY');
31 v_cycle_date := v_cycle_date - 1;
32 CLOSE c_amounts;
33 END LOOP;
34 CLOSE c_amounts;
35 DBMS_OUTPUT.PUT_LINE ('v_cycle_date: ' || v_cycle_date);
36 DBMS_OUTPUT.PUT_LINE ('v_uv_nav: ' || v_uv_nav);
37 DBMS_OUTPUT.PUT_LINE ('v_uv_uv: ' || v_uv_uv);
38 DBMS_OUTPUT.PUT_LINE ('v_uv_dividend: ' || v_uv_dividend);
39 DBMS_OUTPUT.PUT_LINE ('v_uv_units: ' || v_uv_units);
40 END your_procedure;
41 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
-- executions:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXECUTE your_procedure (SYSDATE, 'uvacccode', 'uvdivcode')
v_cycle_date: 22-MAR-2005 00:00:00
v_uv_nav: 1
v_uv_uv: 2
v_uv_dividend: 3
v_uv_units: 4
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE your_procedure (SYSDATE - 10, 'uvacccode', 'uvdivcode')
v_cycle_date: 31-DEC-1900 00:00:00
v_uv_nav:
v_uv_uv:
v_uv_dividend:
v_uv_units:
PL/SQL procedure successfully completed.
scott@ORA92>
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112465 is a reply to message #112446] |
Fri, 25 March 2005 12:13 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I should also point out that there are much simpler ways to do the whole thing, such as using a cursor for loop with an implicit cursor, that eliminates the need for declaring variables to fetch into, opening the cursor, fetching from the cursor, exiting the loop, and closing the cursor. Within the query you can also select the max date value, so that you can eliminate the need for incrementing a date variable and so forth. Please see the demonstration below.
-- procedure:
scott@ORA92> CREATE OR REPLACE PROCEDURE your_procedure
2 (p_cycle_date IN DATE,
3 p_acc_code IN VARCHAR2,
4 p_div_code IN VARCHAR2)
5 AS
6 BEGIN
7 FOR rec IN
8 (SELECT UV_NAV, UV_UV, UV_DIVIDEND, UV_UNITS, uv_uv_date
9 FROM T_LIUV_UNIT_VALUE
10 WHERE UV_UV_DATE =
11 (SELECT MAX (uv_uv_date)
12 FROM t_liuv_unit_value
13 WHERE TRUNC (uv_uv_date) <= p_cycle_date
14 AND uv_acc_code = p_acc_code
15 AND uv_div_code = p_div_code)
16 AND UV_ACC_CODE = p_acc_code
17 AND UV_DIV_CODE = p_div_code)
18 LOOP
19 DBMS_OUTPUT.PUT_LINE ('uv_uv_date: ' || rec.uv_uv_date);
20 DBMS_OUTPUT.PUT_LINE ('uv_nav: ' || rec.uv_nav);
21 DBMS_OUTPUT.PUT_LINE ('uv_uv: ' || rec.uv_uv);
22 DBMS_OUTPUT.PUT_LINE ('uv_dividend: ' || rec.uv_dividend);
23 DBMS_OUTPUT.PUT_LINE ('uv_units: ' || rec.uv_units);
24 END LOOP;
25 END your_procedure;
26 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
-- executions:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXECUTE your_procedure (SYSDATE, 'uvacccode', 'uvdivcode')
uv_uv_date: 22-MAR-2005 09:06:12
uv_nav: 1
uv_uv: 2
uv_dividend: 3
uv_units: 4
PL/SQL procedure successfully completed.
scott@ORA92> EXECUTE your_procedure (SYSDATE - 10, 'uvacccode', 'uvdivcode')
PL/SQL procedure successfully completed.
scott@ORA92>
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112470 is a reply to message #112465] |
Fri, 25 March 2005 13:45 |
developer999
Messages: 4
Registered: March 2005
|
Junior Member |
|
|
Thanks a zillion for the wonderful reply.
Now that you mentioned about implicit cursors, actually I was trying with that too.
Of course my query was not as good as yours.
When my cursor went into infinite loop, this is what I was trying to do :
v_prev_date := v_cycle_date;
LOOP
/* Until data found search for previous dates */
BEGIN
SELECT UV_NAV, UV_UV, UV_DIVIDEND, UV_UNITS
INTO v_uv_nav, v_uv_uv, v_uv_dividend, v_uv_units
FROM T_LIUV_UNIT_VALUE
WHERE UV_UV_DATE = v_prev_date
AND UV_ACC_CODE = cur_fund_rec.c_acc_code
AND UV_DIV_CODE = cur_fund_rec.c_div_code
AND UV_BASIS_POINT_TOTAL = 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_no_data := TRUE;
END;
IF v_no_data THEN
v_prev_date := v_prev_date - 1;
ELSE
EXIT;
END IF;
END LOOP;
And this was also going in indeifnite loop 
I'll be now trying with your stuff.
Thanks again  )
Tell me one thing, which approach is the bettre out of the two you mentioned.
I mean performance wise, implicit cursors will be good or the explicit ones?
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112477 is a reply to message #112470] |
Fri, 25 March 2005 15:01 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In general, implicit cursors are slightly faster, but the difference is extremely slight. However, as you can see, implicit cursors are a whole lot less code to write and maintain. There are some specific cases where you need an explicit cursor or it might be faster, like perhaps bulk collect, but in general, if you can use an implicit cursor, rather than an explicit one, do so. If you search for implicit and explicit on Oracle master guru Tom Kyte's site http://asktom.oracle.com you will find lots of information. Another Oracle guru, Steven Feuerestein, once claimed that explicit cursors were faster, but I believe that Tom Kyte has since disproven that, and Steven Feurestein has more or less rescinded his claim. However, it had already been printed in his books, quoted in other books, and placed on exams and self-study guides, so there are still a lot of people out there who have the wrong information from such outdated materials and are continuing to propagate it to new material.
|
|
|
|
| Re: "%NOTFOUND" behaving weird [message #112486 is a reply to message #112477] |
Fri, 25 March 2005 17:19 |
developer999
Messages: 4
Registered: March 2005
|
Junior Member |
|
|
Thanks once again!!
I tried with your example and it is working fine.
I really appreciate the way you went ahead and created table and actually wrote a procedure to show your example working.
I'm a newbie here...
And I didn't know where to look for information when I got stuck.
I'll be visiting this site often henceforth.
|
|
|
|
Goto Forum:
Current Time: Fri May 16 02:39:57 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
