I have a procedure Oracle which has a series of input parameters (IN) and an OUT parameter of type SYS_REFCURSOR .
I created a dynamic sql that sets the conditions of where based on the input parameters and then I simply execute the command:
OPEN pRefCursor FOR vSqlDyn;
where pRefCursor is my output parameter (SYS_REFCURSOR) and vSqlDyn is my SQL dynamic.
The application calling procedure takes control of the return, ie LOOP to get information.
Now I have been asked to make a control in the procedure to return an error message if the procedure returns more than X lines, as this is burdening the bus.
I would like to hear from you how I can do this control. As you can see I do not LOOP/FETCH , I just open the cursor by executing SQL.
From what I got from info, to know the number of cursor lines I have to open it and do FETCH , but this will not impact the return to the application, since the application is doing it loop in cursor.