ref cursor

create or replace package Debug_PKG is
  -- Public type declarations
  type rLine is record (line varchar2(255));
  type tLines_Output is table of rLine index by binary_integer;
  type cLines_Output is ref cursor return rLine;
  
  -- Public constant declarations

  -- Public variable declarations
  Lines1 DBMS_OUTPUT.chararr;

  -- Public function and procedure declarations
  function GetLine(i integer) return varchar2;

  procedure Get_OutPutLines(
    Lines in out cLINES_OUTPUT, 
    NumLines out integer);
  pragma restrict_references (GetLine, WNPS,WNDS);
end Debug_PKG;

create or replace package body Debug_PKG is
  function GetLine(i integer) return varchar2 is
  begin
    Return(Lines1(i));
  end;

  procedure Get_OutPutLines(
    Lines in out cLINES_OUTPUT, 
    NumLines out integer) 
    is
    i integer;
  begin
    NUMLINES := GetParamByName('DEBUG_OUTPUT',p_IdDep,p_IdUs);
    DBMS_OUTPUT.GET_LINES(Lines1,NUMLINES);
    open Lines for select substr(GetLine(rownum),1,255) 
        from orders where rownum <= Numlines;
  end;
end Debug_PKG;

create or replace procedure Get_OutPutLines(
  Lines out Debug_PKG.cLINES_OUTPUT, 
  NumLines out integer) 
  is
begin
  Debug_PKG.GET_OUTPUTLINES(Lines,NumLines);
end;