I came across a scenario where a cursor was declared in the package specification which then used in Package Body.
Create or replace package pkg_legacy
as
cursor c1 is select * from heavy_table;
end pkg_legacy;
create or replace PACKAGE BODY pkg_legacy
as
procedure prc
is
begin
for rec in c1 loop
do_something;
end loop;
end prc;
end pkg_legacy;
DBMS_HPROF show that Function
| Type | Function elapsed time | SqlID | SQL Text |
|---|
| package body | 100000 | fhskmsnj5v4t8 | select * from heavy_table |
| package Spec | 5000 | fhskmsnj5v4t8 | aselect * from heavy_tableble |
Does the cursor declaration really consume resources? Is there a way to reduce resource consumption?
Thanks,
Prashant