Dynamic Cursors

Question

I use a cursor for the statement:

SELECT NAME FROM STUDENT WHERE ROLL = 1;

I used:

CURSOR C IS SELECT NAME FROM STUDENT WHERE ROLL = roll;
--roll is a variable I receive via a procedure, and the procedure works fine for the received parameter.

Upon executing this, I am able to retrieve all records with roll = 1.

Now, I need to retrieve the records of a group (possibly via a cursor), just like:

SELECT NAME FROM STUDENT WHERE ROLL IN (2, 4, 6);

But the values in the IN clause are known only at run time. How should I do this? That is, is there any way I could assign parameters to the WHERE clause of the cursor?

I tried using an array in the declaration of the cursor, but an error pops up telling something like: standard types cannot be used.

I used:

CURSOR C IS SELECT NAME FROM STUDENT WHERE ROLL IN (rolls);
--rolls is an array initialized with the required roll numbers.

I believe the correct way to do this would be to create a procedure which the result of which is a [ref-cursor](http://stackoverflow.com/questions/1170548/get-resultset-from-oracle-stored-procedure). In the procedure you are executing this cursor, make a call to the 2nd procedure which outputs the ref-cursor. In this way you can pass parameters to the procedure you're calling thereby allowing you to assign dynamic parameters at run time while having avoided use of dynamic SQL which could allow for SQL injection. — xQbert, Apr 15 '12 at 14:49
@xQbert: Thanks, that worked. How can I use **print rc** within pl sql? — user980411, Apr 15 '12 at 14:57
@user980411 You'll note I"m not a big fan of typing so I just copy other people's ideas :D http://www.orafaq.com/node/980 — xQbert, Apr 15 '12 at 15:06

Justin Cave · Accepted Answer · 2012-04-16T03:23:58.000

First, I assume that the parameter to your procedure doesn't actually match the name of a column in the STUDENT table. If you actually coded the statement you posted, roll would be resolved as the name of the column, not the parameter or local variable so this statement would return every row in the STUDENT table where the ROLL column was NOT NULL.

CURSOR C 
    IS SELECT NAME 
         FROM STUDENT 
        WHERE ROLL = roll;

Second, while it is possible to use dynamic SQL as @Gaurav Soni suggests, doing so generates a bunch of non-sharable SQL statements. That's going to flood the shared pool, probably aging other statements out of cache, and use a lot of CPU hard-parsing the statement every time. Oracle is built on the premise that you are going to parse a SQL statement once, generally using bind variables, and then execute the statement many times with different values for the bind variables. Oracle can go through the process of parsing the query, generating the query plan, placing the query in the shared pool, etc. only once and then reuse all that when you execute the query again. If you generate a bunch of SQL statements that will never be used again because you're using dynamic SQL without bind variables, Oracle is going to end up spending a lot of time caching SQL statements that will never be executed again, pushing useful cached statements that will be used again out of the shared pool meaning that you're going to have to re-parse those queries the next time they're encountered.

Additionally, you've opened yourself up to SQL injection attacks. An attacker can exploit the procedure to read any data from any table or execute any function that the owner of the stored procedure has access to. That is going to be a major security hole even if your application isn't particularly security conscious.

You would be better off using a collection. That prevents SQL injection attacks and it generates a single sharable SQL statement so you don't have to do constant hard parses.

SQL> create type empno_tbl is table of number;
  2  /

Type created.

SQL> create or replace procedure get_emps( p_empno_arr in empno_tbl )
  2  is
  3  begin
  4    for e in (select *
  5                from emp
  6               where empno in (select column_value
  7                                 from table( p_empno_arr )))
  8    loop
  9      dbms_output.put_line( e.ename );
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL> set serveroutput on;
SQL> begin
  2    get_emps( empno_tbl( 7369,7499,7934 ));
  3  end;
  4  /
SMITH
ALLEN
MILLER

PL/SQL procedure successfully completed.

:sir can you please explain what do you mean by non sharable sql — Gaurav Soni, Apr 15 '12 at 20:23

score 1 · Answer 2 · answered Apr 15 '12 at 16:28

1

create or replace procedure dynamic_cur(p_empno VARCHAR2) IS
cur     sys_refcursor;
v_ename emp.ename%type;
 begin
  open cur for 'select ename from emp where empno in (' || p_empno || ')';
  loop
   fetch cur into v_ename;
   exit when cur%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close cur;
end dynamic_cur;

Procedure created

Run the procedure dynamic_cur

declare
v_empno   varchar2(200) := '7499,7521,7566';
begin
  dynamic_cur(v_empno);
end;

Output

ALLEN
WARD
JONES

Note:As mentioned by XQbert,dynamic cursor leads to SQL injection ,but if you're not working on any critical requirement ,where security is not involved then you can use this .

answered Apr 15 '12 at 16:28

Gaurav Soni

6,278
9
52
72

1

Just because the tasks you're working on don't need to be secure does not imply that you should ignore SQL injection attacks. If you write code like this, an attacker can read any data in any table that the owner of the procedure has access to-- it is extremely rare that this would not be a security concern. Using dynamic SQL also generates a ton of non-sharable SQL statements and will require a lot of hard parsing. – Justin Cave Apr 15 '12 at 17:58
@justin:thnx for the comments sir I will keep your words in mind – Gaurav Soni Apr 15 '12 at 18:26

score 1 · Answer 3 · answered Apr 15 '12 at 16:37

Maybe you can pass rolls as a set of quoted comma separated values. e.g. '1', '2' etc If this value is passes into the procedure in a varchar input variable, the it can be used to get multiple rows as per the table match.

Hence the cursor SELECT NAME FROM STUDENT WHERE ROLL IN (rolls);

will be evaluated as SELECT NAME FROM STUDENT WHERE ROLL IN ('1','2');

Hope it helps

Dynamic Cursors

3 Answers3