Home » SQL & PL/SQL » SQL & PL/SQL » can we give variable contraining sql statment to declare a cursor (oracle 9i)
can we give variable contraining sql statment to declare a cursor [message #357678] Thu, 06 November 2008 04:12 Go to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
hi guys
can we give variable contraining sql statment to declare a cursor?like

VARIABLENAME:= 'Select a from abc';
declare cursor for VARIABLENAME
Re: can we give variable contraining sql statment to declare a cursor [message #357686 is a reply to message #357678] Thu, 06 November 2008 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can open a Ref Cursor for a query contained in a string:
DECLARE
  TYPE ty_rc IS REF CURSOR;

  c      ty_rc;
  v_date date;
BEGIN
  OPEN c FOR 'select sysdate from dual';
  FETCH c INTO v_date;
  CLOSE c;
END;
/
Re: can we give variable contraining sql statment to declare a cursor [message #357687 is a reply to message #357686] Thu, 06 November 2008 04:42 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
thanks for your reply but the problem is i have to get the query from table. thats why i ask to use a variable instead of select satement directly in cursor declaration.
Re: can we give variable contraining sql statment to declare a cursor [message #357690 is a reply to message #357678] Thu, 06 November 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Replace the constant string by the variable name.

Regards
Michel
Re: can we give variable contraining sql statment to declare a cursor [message #357693 is a reply to message #357678] Thu, 06 November 2008 04:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes you can .......


SQL> DECLARE
  2    TYPE ty_rc IS REF CURSOR;
  3    c      ty_rc;
  4    v_month VARCHAR2 (15);
  5    str VARCHAR2(50):='select TO_CHAR(sysdate,''MONTH'') from dual';
  6  BEGIN
  7    OPEN c FOR str;
  8    FETCH c INTO v_month;
  9    DBMS_OUTPUT.PUT_LINE('MONTH is :'||v_month);
 10    CLOSE c;
 11  END;
 12  /
MONTH is :NOVEMBER

PL/SQL procedure successfully completed.

SQL>


Smile
Rajuvan.
Re: can we give variable contraining sql statment to declare a cursor [message #357701 is a reply to message #357693] Thu, 06 November 2008 05:14 Go to previous messageGo to next message
kashifchughtai
Messages: 113
Registered: October 2007
Senior Member
so its mean we can only use REF cursor to do it. we cannot use normal cursor in the same way?
Re: can we give variable contraining sql statment to declare a cursor [message #357702 is a reply to message #357678] Thu, 06 November 2008 05:16 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No .. We cannot ..

That is One of the reason , it is called REF cursor .

Smile
Rajuvan.
Previous Topic: Enforcing business rule
Next Topic: format after concat number
Goto Forum:
  


Current Time: Wed Dec 07 10:55:17 CST 2016

Total time taken to generate the page: 0.16673 seconds