Can I pass a cursor in a procedure?

Question

hi, can I pass a cursor in a procedure?

CURSOR  BLT_CURSOR IS
SELECT  BLT.sol_id,
        BLT.bill_id,
        BLT.bank_id
FROM BLT;

Is my cursor.

Procedure abc(i want to pass the cursor here)

How do I do it.

Answer 1

This does not make sense. Why would you want to pass a cursor to a procedure? (Whatever the cursor does can be done by another cursor inside that stored procedure. You do not need to have that particular cursor!)

Answer 2

It might be DBMS-specific, for Oracle see 6.12.7 of Oracle PL/SQL Programming

Answer 3

It's not possible in MSSQL2005. I don't know about 2008 or other platforms.

There's been several times I'd like to do it. It would support a more modular architecture. I could have a proc that performs a common process on a dataset but be able to pass a cursor to that dataset from a variety of other procedures.

Answer 4

From MSDN (SQL Server 2008): The cursor data type can be used only on OUTPUT parameters. When you specify a cursor data type, the VARYING and OUTPUT keywords must also be specified. You can have multiple output parameters specified with the cursor data type.

Answer 5

Remember that cursors are terribly ineffient and should be avoided. I'm sure there is a better set-based way to do what you want to do. If you have a data set you want to be able use in many procs, consider making it a view.

Answer 6

I am assuming you are using Oracle (it would appear so).

You can do this:

PROCEDURE abc( p_cursor SYS_REFCUROR) IS
   v_sol_id blt.sol_id%TYPE;
   v_bill_id blt.bill_id%TYPE;
   v_bank_id blt.bank_id%TYPE;
BEGIN
   LOOP
      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;
      EXIT WHEN p_cursor%NOTFOUND;
      ...
   END LOOP;
END;

Then use it:

DECLARE
   v_cursor SYS_REFCURSOR;
BEGIN
   OPEN v_cursor FOR
      SELECT  BLT.sol_id,
              BLT.bill_id,
              BLT.bank_id
      FROM BLT;
   abc (v_cursor);
END;

However, note that the procedure abc needs to know the structure of the cursor, i.e. that it returns 3 columns of particular types. If you wanted to be able to pass any cursor to the procedure then you'd need to look at using the DBMS_SQL package (and that's not trivial!)