Cursors 
Author Message
 Cursors

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

PS.  Please cc a copy to e-mail.

Sent via Deja.com http://www.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

You can declare a Cursor with a parameter. i.e.

Procedure .... ( date_parameter DATE ) IS

CURSOR Cur_Sample( your_date DATE ) IS
SELECT Col1,.... FROM The_Table WHERE
date_column = your_date

 ld_date DATE;

begin

   IF date_parameter is valid THEN
       ld_date := date_parameter;
   ELSE
       ld_date := SYSDATE;
   END IF;

   OPEN Cur_Sample ( ld_date );
   FETCH ....
   CLOSE ....

END;


Quote:

> 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

> PS.  Please cc a copy to e-mail.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 Cursors
A simple way to do this would be to pass the date values into your cursor
when you open it:

CURSOR c1 (v_dateval DATE) IS
SELECT *
FROM mytab
WHERE datecol = v_dateval;

BEGIN
     IF valid parameter date THEN
          OPEN c1(p_date);
    ELSE
          OPEN c1(SYSDATE);
   END IF;
END;

A Crowther

Quote:

> 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

> PS.  Please cc a copy to e-mail.

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 Cursors

EXAMPLE:

cursor c1 (inp_date DATE, inp_ok VARCHAR2)
  select  ...
  from    ...
  where   date_field = decode(inp_ok, 'Y', inp_date, SYSDATE);

Evaluate dates, if OK pass 'Y' to the cursor with the date else pass 'N'

Quote:

> 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

> PS.  Please cc a copy to e-mail.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

  rstapley.vcf
< 1K Download


Wed, 18 Jun 1902 08:00:00 GMT
 Cursors
Your can use declared variables in your cursor declarations .
Just make sure the varibles have valid vales before you open the cursor.
Quote:
>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??



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Cursor Cursor Cursor

2. Could not complete cursor operation, because the table schema changed after cursor was declared

3. cursor without cursors

4. Could not complete cursor operation, because the table schema changed after cursor was declared

5. Need Help Please , Invalid Cursor Position Error and Cursor Not Open Error

6. Should I use ODBC cursors or Server cursors?

7. declare curW cursor for EXEC spMySproc - Trying to assign sp resultset to cursor variable

8. client side cursor vs. server side cursor

9. Why declare cursor and open cursor both cost same amount of time

10. Detect if Cursor Opened and Detect if Cursor Allocated

11. Cursor in Trigger - with cursor not open when running second time in the same SQL session


 
Powered by phpBB® Forum Software