Home » SQL & PL/SQL » SQL & PL/SQL » cursor reference out of scope
| cursor reference out of scope [message #8691] |
Thu, 18 September 2003 15:22  |
Dan Wood
Messages: 11
Registered: January 2003
|
Junior Member |
|
|
Here's what I'm trying to do:
From a raw data table containing multiple observation times throughout each day, I want to summarize into a summary table just that one observation which represents the highest maxox reading for that day, including the time of that observation.
A simple select seems to insist on including the time in the group clause, which defeats the purpose.
I have since tried to build a cursor which contains the records sorted by maxox descending, with the thought that I can simply write out to the summary table when I hit the transition between clli, port_id, and period.
Unfortunately, when I execute the script, I get the following error, which makes no sense to me:
SQL> @new_load_link_sum
link_comp_cursor.time);
*
ERROR at line 39:
ORA-06550: line 39, column 17:
PLS-00225: subprogram or cursor 'LINK_COMP_CURSOR' reference is out of scope
ORA-06550: line 39, column 34:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 32, column 7:
PL/SQL: SQL Statement ignored
Here's the script - It's only my 2nd cursor and the first time I've ever tried something like this, so I know it's not pretty - any help would be greatly appreciated:
DECLARE
CURSOR link_comp_cursor IS
select
clld,
port_id,
to_date(datestmp,'YY-MM-DD') as period,
DECODE(SIGN(NVL(link_speed,76000)-76000),-1,round(greatest(tran+utran,rec+urec)/326000,1),1,round(greatest(tran+utran,rec+urec)/5076000,1),0,round(greatest(tran+utran,rec+urec)/326000,1)) as maxox,
time
from link_comp
where datestmp = to_char(sysdate-1,'YY-MM-DD')
order by clld, port_id, period, maxox desc;
link_comp_row link_comp_cursor%ROWTYPE;
v_clld_a link_comp.clld%TYPE;
v_clld_b link_comp.clld%TYPE := '';
v_port_a link_comp.port_id%TYPE;
v_port_b link_comp.port_id%TYPE := '';
v_period_a date;
v_period_b date := to_date('01-JAN-01');
BEGIN
OPEN link_comp_cursor;
LOOP
FETCH link_comp_cursor INTO link_comp_row;
EXIT WHEN link_comp_cursor%NOTFOUND;
v_clld_a := link_comp_row.clld;
v_port_a := link_comp_row.port_id;
v_period_a := link_comp_row.period;
IF (v_clld_a = v_clld_b) and
(v_port_a = v_port_b) and
(v_period_a = v_period_b) THEN
NULL;
ELSE
BEGIN
INSERT INTO NEW_LINK_SUM (CLLD, LSSN, LINK_NUM, MAXOX, PORT_ID, PERIOD, PEAK)
VALUES (link_comp_cursor.clld,
' ',
' ',
link_comp_cursor.maxox,
link_comp_cursor.port_id,
link_comp_cursor.period,
link_comp_cursor.time);
v_clld_b := v_clld_a;
v_port_b := v_port_a;
v_period_b := v_period_a;
END;
END IF;
END LOOP;
CLOSE link_comp_cursor;
END;
/
Unfortunately, when I preview this, all my indents are lost, but I hope it's still readable.
|
|
|
|
| Re: cursor reference out of scope [message #8692 is a reply to message #8691] |
Thu, 18 September 2003 16:04  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Dan, you can definitely do this in a single statement - no need for PL/SQL. Here is a simplified version of your observations table where we will identify the highest maxox reading (as if I understand what maxox is!) per day:
sql>select * from observe order by observe_time;
OBSERVE_TIME MAXOX COMMENTS
--------------------- --------- ----------
09/17/2003 06:02:00am 20 d
09/17/2003 11:37:04am 15 e
09/17/2003 03:13:23pm 5 f
09/18/2003 07:24:00am 1 a
09/18/2003 10:24:00am 3 b
09/18/2003 02:07:00pm 2 c
6 rows selected.
sql>select trunc(observe_time), max(maxox)
2 from observe
3 group by trunc(observe_time);
TRUNC(OBSERVE_TIME) MAX(MAXOX)
--------------------- ----------
09/17/2003 12:00:00am 20
09/18/2003 12:00:00am 3
2 rows selected.
sql>select *
2 from observe
3 where (trunc(observe_time), maxox) in
4 (select trunc(observe_time), max(maxox)
5 from observe
6 group by trunc(observe_time));
OBSERVE_TIME MAXOX COMMENTS
--------------------- --------- ----------
09/17/2003 06:02:00am 20 d
09/18/2003 10:24:00am 3 b
2 rows selected.
Here's another approach using an analytic function (RANK):
sql>select observe_time, maxox, comments,
2 dense_rank() over (partition by trunc(observe_time) order by maxox desc) rank
3 from observe;
OBSERVE_TIME MAXOX COMMENTS RANK
--------------------- --------- ---------- ---------
09/17/2003 06:02:00am 20 d 1
09/17/2003 11:37:04am 15 e 2
09/17/2003 03:13:23pm 5 f 3
09/18/2003 10:24:00am 3 b 1
09/18/2003 02:07:00pm 2 c 2
09/18/2003 07:24:00am 1 a 3
6 rows selected.
sql>select observe_time, maxox, comments
2 from (select observe_time, maxox, comments,
3 dense_rank() over (partition by trunc(observe_time) order by maxox desc) rank
4 from observe)
5 where rank = 1;
OBSERVE_TIME MAXOX COMMENTS
--------------------- --------- ----------
09/17/2003 06:02:00am 20 d
09/18/2003 10:24:00am 3 b
2 rows selected.
The analytic function approach will outperform other solutions in many cases.
|
|
|
|
| Re: cursor reference out of scope [message #8708 is a reply to message #8692] |
Fri, 19 September 2003 09:37 |
Dan Wood
Messages: 11
Registered: January 2003
|
Junior Member |
|
|
|
Thanks, Todd! I am wholly unfamiliar with the dense_rank and partition functions, but it is obvious from your superbly-illustrated response that this is definitely the more elegant approach! THANKS for taking the time to offer your help and expertise.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 03:49:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
