PL/SQL problem 1

[TravisLee]

Does anyone know why this query successfully returns the correct data in
SQL Plus, but when I attempt to compile it in a function it blows up on the
order by?


1 select dst_count, ip_dst
2 from (select count(ip_dst) as dst_count,ip_dst
3 from iphdr
4 group by ip_dst
5 order by dst_count desc)
6* where rownum=1
SQL> /

DST_COUNT IP_DST
---------- ----------
442 1064387365



Function


FUNCTION COUNTS
RETURN CSAT_EVENT.ObjectRC IS

resultset CSAT_EVENT.ObjectRC;


v_dstip_count number(12);
v_dstip number(15);

BEGIN


select dst_count, ip_dst into v_dstip_count, v_dstip
from (select count(ip_dst) as dst_count,ip_dst
from iphdr
group by ip_dst
order by dst_count desc)
where rownum=1;


OPEN resultset FOR
select v_dstip_count,
v_dstip
from dual;

RETURN(resultset);

END COUNTS;

 

[THoey]

Can you tell us what there error is that you are getting? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

This sounds like the same issue that came up last year. Oracle 8i added the capability to use an "order by" clause in a subquery, but pl/sql didn't support this functionality until 8.1.6. For details, including a possible work around, see thread186-34165.

 

[sem]

Another possible cause is in the lack of explicit privileges on one of the referenced tables.

 

[TravisLee]

Thanks eveyone. The 8.1.5 bug was it.