Unused cursor hint

[cassiusdrow]

In PL/SQL Developer version 7.0.3.1123, a cursor in the package body will get a "hint: cursor .. is declared but never used in .." warning when the cursor is also defined in the package specification. It should not give this warning since it may be used outside the package.

Example:

CREATE OR REPLACE PACKAGE test
IS
   TYPE t_user IS RECORD (username VARCHAR(30));
   CURSOR csr_users RETURN t_user;
END ;
/
CREATE OR REPLACE PACKAGE BODY test
IS
   CURSOR csr_users RETURN t_user IS SELECT t.username FROM sys.all_users t;
END test;
/

Compilation errors for PACKAGE HCRS.TEST

Error: Hint: Cursor 'csr_users' is declared but never used in 'test'
Line: 3
Text: CURSOR csr_users RETURN t_user IS SELECT t.username FROM sys.all_users t;

 

[Marco Kalter]

The cursor in the package body is never used though.

 

[cassiusdrow]

Since the cursor is also declared in the package specification, it is probably used from outside the package, so the hint may mislead someone that the cursor isn't used at all and could be removed.

A function or procedure declared in both the package body and specification that is not also referenced in the package body does not cause a similar hint:

CREATE OR REPLACE PACKAGE test
IS
    PROCEDURE test;
END test;
/
CREATE OR REPLACE PACKAGE BODY test
IS
    PROCEDURE test
    IS
    BEGIN
       NULL;
    END;
END test;
/

 

[Marco van der Linden]

Apples and pears if you ask me. A cursor definition is a 'variable' whereas a procedure is a program unit. I think that hinting at a cursor variable not being used is correct behaviour. If you would like to prevent someone from deleting the cursor definition there are several ways to go about.

The most obvious and easy way is commenting:

create or replace package my_package
as
  -- This cursor is used outside the scope of the package
  cursor
    my_cursor is select * from dual;
end my_package;

A somewhat more elaborate, but in my opinion 'better' way, is to use a ref cursor and a 'getter' function:

create or replace package my_package
as
  --
  type
    my_ref_cursor is ref cursor;
  --
  function get_my_cursor
    return my_ref_cursor;
  --
end my_package;
/
create or replace package body my_package
as
  --
  function get_my_cursor
    return my_ref_cursor
  is
    local_ref_cursor  my_ref_cursor;
  begin
    --
    open local_ref_cursor for 'select * from dual';
    --
    return local_ref_cursor;
    --
  end get_my_cursor;
  --
end my_package;

The calling code would then look like:

procedure my_proc
is
  proc_ref_cursor  my_package.my_ref_cursor;
begin
  proc_ref_cursor := my_package.get_my_cursor;
  --
    for i in proc_ref_cursor
    etc...
end my_proc;

And even better I think is to use a strongly typed ref cursor:

create or replace my_package
as
  type
    my_rec is record(dummy  varchar2(1);
  type
    my_ref_cursor is ref cursor return my_rec;
  etc....
end my_package;

Just my two cents....

 

[cassiusdrow]

Originally posted by Marco van der Linden:
Apples and pears if you ask me. A cursor definition is a 'variable' whereas a procedure is a program unit.

I don't agree. A cursor definition is more akin to a procedure or function definition because the cursor can be fully defined in the package body with only the cursor header exposed in the package specification. A variable either exists as an internal package variable in the package body that cannot be directly touched outside the package, or it is a package global variable exposed to the outside world in the package specification.

Originally posted by Marco van der Linden:
A somewhat more elaborate, but in my opinion 'better' way, is to use a ref cursor and a 'getter' function

Ref cursors introduce library cache latching problems for cursors that must be called repeatedly. The example you give using dynamic SQL could also flood the SQL area if called repeatedly.

I have a situation where I have three queries that drive a process in my application, each query is called once producing anywhere from 0 to 600K rows. Many of those records require one or more of another set of five queries to be called for each row. Originally, these five queries were using ref cursors as you describe, but it caused horrible library cache latch contention. I have reimplemented them as I described in my first post, which removed the library cache latch contention, but now PL/SQL developer's hints that I am not using them in the package body when I have clearly exposed them for use by the outside world which, in my opinion, renders the "use in package body" check irrelevant. The three functions for the ref cursors are not used in the package body but PL/SQL Developer rightly does not warn me about this because they are exposed in the package specification.

At a minimum I would ask that an option be created to control the hinting in this situation.