Home » SQL & PL/SQL » SQL & PL/SQL » Cursor problem
Cursor problem [message #292848] Thu, 10 January 2008 00:57 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,

I need to do select the cursor depands on the passed date.
What my idea is, declaring two cursors and selecting one depands on the date. But is it possible to store the cursor name in a variable and use?..Please check this template.

Create or replace procedure temp(date IN DATE)
as
cur varchar2(30)='';
cursor1;(declaration)
cursor2;(declaration)

begin

if (date <> null)
cur=cursor1;
else
cur=cusor2;
end if;
for i in cur
-- doing some operation
end loop;
end temp;
Re: Cursor problem [message #292855 is a reply to message #292848] Thu, 10 January 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Put your cursor statement in strings, for instance:
sql1 varchar2(1000) := "<select 1>";
sql2 varchar2(1000) := "<select 2>";
cur sys_refcursor;

if date is not null then open cur for sql1;
else open cur for sql2;
end if;

Regards
Michel
Re: Cursor problem [message #293087 is a reply to message #292855] Thu, 10 January 2008 20:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You don't need to use dynamic SQL:

DECLARE
  cur sys_refcursor;
BEGIN
  IF ... THEN
     OPEN cur FOR SELECT this, ...;
  ELSE
     OPEN cur FOR SELECT that, ...;
  END IF;
  ...


Ross Leishman
Re: Cursor problem [message #297561 is a reply to message #293087] Fri, 01 February 2008 06:12 Go to previous messageGo to next message
satish_dba
Messages: 9
Registered: February 2008
Location: BANGALORE
Junior Member

DECLARE
Cr xyz;
BEGIN
IF ... THEN
OPEN Cr FOR SELECT this, ...;
ELSE
OPEN Cr FOR SELECT that, ...;
END IF;
Re: Cursor problem [message #297563 is a reply to message #297561] Fri, 01 February 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of this unformatted post?
To show you don't know how to post or to show you know how to replace "cur" by "Cr" and "sys_refcursor" by meaningless "xyz"?

Regards
Michel
Re: Cursor problem [message #297565 is a reply to message #297563] Fri, 01 February 2008 06:33 Go to previous messageGo to next message
satish_dba
Messages: 9
Registered: February 2008
Location: BANGALORE
Junior Member



To make sure that you are trying to find people mistakes.
Re: Cursor problem [message #297568 is a reply to message #297565] Fri, 01 February 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's my job.

Regards
Michel
Re: Cursor problem [message #305571 is a reply to message #297568] Tue, 11 March 2008 05:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure that it's just a hobby really.....

Smile
Re: Cursor problem [message #305654 is a reply to message #305571] Tue, 11 March 2008 08:18 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Where have you been for the last few months? Was it you who had a new baby?
Re: Cursor problem [message #305703 is a reply to message #305654] Tue, 11 March 2008 10:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It was an unfortunate combination of things.

By a piece of stunningly poor planning, we managed to move house and have a new baby at the second time, so that kept my evening free time down,

The rest of is was a contract at a site with strictly limited external web access.

I see things are much the same as they were when I left, except for Michel's post count, which has almost doubled.
icon14.gif  Re: Cursor problem [message #305940 is a reply to message #292848] Wed, 12 March 2008 06:32 Go to previous messageGo to next message
subhrajyoti
Messages: 1
Registered: March 2008
Junior Member
Hi,

you can try Ref cursor(also known as cursor variables)

thanks
subhra
Re: Cursor problem [message #305942 is a reply to message #305940] Wed, 12 March 2008 06:35 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm, is this not what have been posted several times in this topic?
Nice to see you want to help but maybe you should choose topics that have not already the same answers that you want to give.

Regards
Michel
Previous Topic: zero padding decimal in oracle
Next Topic: wrong data of query result
Goto Forum:
  


Current Time: Thu Dec 08 08:28:06 CST 2016

Total time taken to generate the page: 0.12660 seconds