Home » SQL & PL/SQL » SQL & PL/SQL » Cursor (Oracle 9i)
Cursor [message #441037] Thu, 28 January 2010 06:45 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
How can we change the WHERE condition in normal cursor dynamically.

Consider the below example

DECLARE
CURSOR C IS
SELECT * FROM emp WHERE deptno=&a;
Begin
FOR i in C
LOOP
DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
END LOOP;
END;
/

IN this i want to change the deptno to job.
My requirement is like this
SELECT * FROM emp WHERE job=&a

Please help me on this scenario .
Thanks in advance
Re: Cursor [message #441040 is a reply to message #441037] Thu, 28 January 2010 06:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>   DECLARE
  2   CURSOR C IS
  3   SELECT * FROM sriram WHERE &icond=&a;
  4   Begin
  5   FOR i in C
  6   LOOP
  7   DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
  8   END LOOP;
  9   END;
 10  /
Enter value for icond: sal
Enter value for a: 800
old   3:  SELECT * FROM sriram WHERE &icond=&a;
new   3:  SELECT * FROM sriram WHERE sal=800;
7369SMITH

PL/SQL procedure successfully completed.

SQL>



SQL> ed
Wrote file afiedt.buf

  1    DECLARE
  2   CURSOR C IS
  3   SELECT * FROM sriram WHERE &icond=&a;
  4   Begin
  5   FOR i in C
  6   LOOP
  7   DBMS_OUTPUT.PUT_LINE(i.empno||i.ename);
  8   END LOOP;
  9*  END;
SQL> /
Enter value for icond: job
Enter value for a: 'CLERK'
old   3:  SELECT * FROM sriram WHERE &icond=&a;
new   3:  SELECT * FROM sriram WHERE job='CLERK';
7369SMITH
7876ADAMS
7900JAMES
7934MILLER

PL/SQL procedure successfully completed.

SQL>


It depends on you...AND THE SITUATION( As based on the information provided)

sriram Smile

[Updated on: Thu, 28 January 2010 07:03]

Report message to a moderator

Re: Cursor [message #441046 is a reply to message #441037] Thu, 28 January 2010 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramoradba showed you how to do it in SQL*Plus, now if it is in a stored procedure you have to use dynamic SQL and build the query inside the procedure.

Regards
Michel
Re: Cursor [message #441058 is a reply to message #441037] Thu, 28 January 2010 07:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, you can also have cursors with parameters.

SQL> SET serverout ON;
SQL>
SQL> DECLARE
  2    CURSOR C ( p_rows number ) IS
  3      SELECT ROWNUM rn FROM all_objects WHERE ROWNUM <= p_rows;
  4  Begin
  5   FOR i in C (5)
  6     LOOP
  7     DBMS_OUTPUT.PUT_LINE('A' || i.rn);
  8   END LOOP;
  9
 10
 11   FOR i in C (2)
 12   LOOP
 13     DBMS_OUTPUT.PUT_LINE('B' || i.rn);
 14   END LOOP;
 15
 16  END;
 17  /

A1
A2
A3
A4
A5
B1
B2

PL/SQL procedure successfully completed.

SQL>


Having an "&" Client parameter in a stored procedure makes not much sense anyway.
Re: Cursor [message #441060 is a reply to message #441058] Thu, 28 January 2010 07:58 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
ThomasG wrote on Thu, 28 January 2010 13:52
Of course, you can also have cursors with parameters.


That's irrelevant to the OPs question since he wants to change the columns referenced in the where clause.

ThomasG wrote on Thu, 28 January 2010 13:52

Having an "&" Client parameter in a stored procedure makes not much sense anyway.

Would have thought it just won't work, unless you fancy recompiling the procedure each time you want to run it.
Re: Cursor [message #441061 is a reply to message #441060] Thu, 28 January 2010 08:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
cookiemonster wrote on Thu, 28 January 2010 14:58
That's irrelevant to the OPs question since he wants to change the columns referenced in the where clause.

Ooooppps. Didn't spot that with my glasses being fogged in this cold. Very Happy
Re: Cursor [message #441112 is a reply to message #441061] Thu, 28 January 2010 21:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/7373/0/

sriram Smile
Re: Cursor [message #441121 is a reply to message #441112] Thu, 28 January 2010 21:59 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
clean the fogged glasses./fa/7374/0/

sriram Smile
  • Attachment: glasses.gif
    (Size: 6.44KB, Downloaded 189 times)

[Updated on: Thu, 28 January 2010 22:00]

Report message to a moderator

Re: Cursor [message #441213 is a reply to message #441037] Fri, 29 January 2010 08:53 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
dynamic sql should be used sparingly and with good reason. Most often you don't really need dynamic sql to do what you want. Sometimes you have to write a bit of code but in the end you won't have to use dynamic sql.

For example, is your query expression really unknown till query time, or is it that you just pick from a list of known possible expressions. For example: do you have 30 columns and your query could be against any of these 30 columns? If so you do not need dynamic sql. One way to avoid it would be to use a manually partitioned query with short circuiting. Here is an example:

select ... from table where :1 = 'C1' and c1 = :2 union all
select ... from table where :1 = 'C2' and c2 = :2 union all
...
select ... from table where :1 = 'C30' and c30 = :2

Notice that for 30 different columns, there are 30 different select statements, one each for each of the columns.

Notice also that there is a paramter = constant test that checks to see if the column name held in the parameter is the column relevant to the specific select. This is our short circuiting mechanism. Oracle will evaluate at runtime if the parameter = constant is true or not for each sub query and only run those queries where it is true. In this case each sub query is mutually exclusive to all the others and thus oracle will only execute one of the select statements of our 30 select union all query.

Now this might seem like some work but in reality it is not. Your query is the same all the time. That means this code will scale for you as your system concurrancy increases. It also means that people maintaining this code and debugging errors in this code do not have to guess what the actually query was which was executed. Lastly, there is no chance of "SQL INJECTION" which might be possible in your dynamic solution.

Even though there may be lots more lines of code, the 30 select solution is better than the one select dynamic solution. This is hard for the simple minded to grasp at first but it is 99% always so that a static solution is better than a dynamic solution.

Of course we must evaluate each situation for ourselves.

Good luck, Kevin
Previous Topic: Deterministic
Next Topic: Fun With Triggers and Mutating tables
Goto Forum:
  


Current Time: Fri Dec 02 12:21:24 CST 2016

Total time taken to generate the page: 0.06113 seconds