Re: Cursor Question

From: paul cluiss <paul_cluiss_at_intervoice.com>
Date: Tue, 16 Nov 1999 09:37:02 -0600
Message-ID: <47C89F3E39AC041C.5A6BC05DABB94CB5.95B633FD1C437EE5@lp.airnews.net>

Arthur,

Why don't you use a ref cursor? Here's a package that does what I think you want:

CREATE OR REPLACE PACKAGE your_package
AS
  TYPE curtype IS REF CURSOR RETURN all_users%ROWTYPE;   FUNCTION open_cursor(
    theDate IN DATE := NULL
  ) RETURN curtype;
END your_package;
/

show err;

CREATE OR REPLACE PACKAGE BODY your_package AS
FUNCTION open_cursor (

    theDate IN DATE
) RETURN curtype
IS

    theCursor curtype;
BEGIN
    OPEN theCursor FOR

        SELECT
            username,
            user_id,
            NVL(theDate, SYSDATE)
        FROM
            all_users;

    RETURN theCursor;
END open_cursor;
END your_package;
/

show err;

set serveroutput ON;

DECLARE
-- myCursor your_package.curtype :=
-- your_package.open_cursor();

    myCursor your_package.curtype :=

        your_package.open_cursor(TO_DATE('12/25/1999', 'MM/DD/YYYY'));     myRow all_users%ROWTYPE;
BEGIN
    LOOP

        EXIT WHEN myCursor%NOTFOUND;
        FETCH myCursor INTO myRow;
        DBMS_OUTPUT.PUT_LINE(myRow.username || ' : ' ||
                             myRow.user_id || ' : ' ||
                             TO_CHAR(myRow.created, 'MM/DD/YYYY'));

    END LOOP;     IF myCursor%ISOPEN
    THEN
        CLOSE myCursor;
    END IF;
END;
/

The first call to open_cursor lets the date defaut to null. The second one supplies a date.
Hope this helps.

Paul Cluiss
Dallas, Texas

amerar_at_ci.chi.il.us wrote:

  Hello,

  I have a question about cursors. I want to define a cursor based upon   parameters passed into my PL/SQL program.

  I want to look at the parameters the user passes in. If they are valid   dates, I want to use those dates in my WHERE caluse. Otherwise, I want   to use SYSDATE.

  How can I code a 'conditional' define for a CURSOR since it is done in   the declaration section??

  Thanks,

  Arthur
  amerar_at_ci.chi.il.us

  PS. Please cc a copy to e-mail.

  Sent via Deja.com http://www.deja.com/   Before you buy. Received on Tue Nov 16 1999 - 09:37:02 CST