Stored procedure. RefCursor declaration issue

Question

I'm trying to write a stored procedure, where I use a refcursor, but when I try to run it, Oracle tells me that the refcursor is not declared

Package:

create or replace package types
as
type cursorType is ref cursor;
end;
/

Procedure:

CREATE OR REPLACE PROCEDURE p_lista_veic_aluguer (
   ESCRITORIO IN INT,
   CATEGORIA  IN CHAR,
   DATA_INI   IN DATE,
   DATA_FIM   IN DATE,
   RETVAL     IN OUT types.cursorType 
) is
BEGIN
  open retval for
    SELECT B.COD_Veiculo,B.Marca 
      FROM VEICULO B
      LEFT JOIN ALUGUER A 
        ON A.COD_VEICULO = B.COD_VEICULO
       AND (data_ini BETWEEN A.DATA_LEVANTAMENTO AND A.DATA_ENTREGA
        OR  data_fim BETWEEN A.DATA_LEVANTAMENTO AND A.DATA_ENTREGA)
     WHERE A.COD_VEICULO IS NULL 
       AND B.DATA_MANUTENCAO IS NULL
       AND B.CATEGORIA = categoria
     ORDER BY f_menor_dist(B.ESCRITORIO_ATUAL,escritorio) ASC;
END p_lista_veic_aluguer;
/

Testing :

SET DEFINE OFF;;
DECLARE
  ESCRITORIO NUMBER;
  CATEGORIA  CHAR(200);
  DATA_INI   DATE;
  DATA_FIM   DATE;
  variable RETVAL TYPES.cursorType;
BEGIN
  ESCRITORIO := 22;
  CATEGORIA  := 'A';
  DATA_INI   := '2012/11/23';
  DATA_FIM   := '2012/11/30';
  P_LISTA_VEIC_ALUGUER( ESCRITORIO => ESCRITORIO, 
                        CATEGORIA  => CATEGORIA, 
                        DATA_INI   => DATA_INI, 
                        DATA_FIM   => DATA_FIM, 
                        RETVAL     => RETVAL );
  /* Legacy output:
  DBMS_OUTPUT.PUT_LINE('RETVAL = ' || RETVAL);
  */
  print retval;
END;

Error:

Error report: ORA-06550: linha 6, coluna 19: PLS-00103: Encountered the symbol "TYPES" when expecting one of the following:

:= . ( @ % ; not null range default character The symbol ":=" was substituted for "TYPES" to continue. ORA-06550: linha 16, coluna 9: PLS-00103: Encountered the symbol "RETVAL" when expecting one of the following:

:= . ( @ % ; The symbol ":=" was substituted for "RETVAL" to continue. 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

If you are running your anonymous PL/SQL block in SQLPLUS, simply declare a refcursor variable as follows before executing your pl/sql block: variable retval refcursor; and pass it into the procedure retval => :retval. There is no need in RETVAL := :RETVAL; or :RETVAL := RETVAL;. In order to see the contents of your retval cursor, print it, using print SQLPLUS command like so: print retval; — Nick Krasnov

Avrajit Avrajit · Accepted Answer · 2013-11-27T06:55:09

SET DEFINE OFF;
variable RETVAL refcursor;
DECLARE
  ESCRITORIO NUMBER;
  CATEGORIA  CHAR(200);
  DATA_INI   DATE;
  DATA_FIM   DATE;
  BEGIN
  ESCRITORIO := 22;
  CATEGORIA  := 'A';
  DATA_INI   := '2012/11/23';
  DATA_FIM   := '2012/11/30';
  P_LISTA_VEIC_ALUGUER( ESCRITORIO => ESCRITORIO, 
                        CATEGORIA  => CATEGORIA, 
                        DATA_INI   => DATA_INI, 
                        DATA_FIM   => DATA_FIM, 
                        RETVAL     => RETVAL );
  /* Legacy output:
  DBMS_OUTPUT.PUT_LINE('RETVAL = ' || RETVAL);
  */
  print retval;
END;

Try this it will work.

Stored procedure. RefCursor declaration issue

1 Answers