CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqlcursor
Jimenemex
Jimenemex

Reputation: 3166

Call Cursor located in different procedure

I have a rather complex cursor located in a stored procedure that I'm going to need in a different procedure. Is it bad practice to just copy and paste the cursor in this procedure? Could I just call that cursor sort of like this?

OPEN diffProcedure.cursorName(params)... 
LOOP
    FETCH ....
    INTO ....
    EXIT WHEN ....
END LOOP;

The cursor is probably 200 lines of complicated code which I don't have a good understanding of (Junior Dev working on complicated business PL/SQL code), but I know what it does. Calling it with the parameters I need will get the correct data needed.

Is it a bad idea to just copy the cursor from one procedure to another? Could I call the cursor from a different procedure?

Upvotes: 1

Views: 1121

Answers (1)

krokodilko
krokodilko

Reputation: 36107

You cannot reference a cursor declared in a procedure from another procedure.
But when you declare a cursor in the package, you can call it from other procedures located in the same package, in other packages, and even from standalone procedures.

Simple example:

CREATE OR REPLACE PACKAGE somepackage IS
   CURSOR my_cursor( par IN NUMBER ) IS
   SELECT par FROM dual;
END;
/

CREATE OR REPLACE PACKAGE someotherpackage IS
   PROCEDURE MY_Procedure;
END;
/

CREATE OR REPLACE PACKAGE BODY someotherpackage IS
  PROCEDURE MY_Procedure IS
     x NUMBER;
  BEGIN
    OPEN somepackage.my_cursor( 2 );
    FETCH somepackage.my_cursor INTO x;
    CLOSE somepackage.my_cursor;
    DBMS_OUTPUT.PUT_LINE( x );
  END;
END;
/

create or replace procedure some_standalone_procedure IS
  y NUMBER;
BEGIN
    OPEN somepackage.my_cursor( 5 );
    FETCH somepackage.my_cursor INTO y;
    CLOSE somepackage.my_cursor;
    DBMS_OUTPUT.PUT_LINE( y );
END;
/

and now fire tests:

exec someotherpackage.MY_Procedure;

2


exec some_standalone_procedure;

5

Upvotes: 1

Related Questions