This content has been marked as final. Show 1 reply
1 person found this helpful
944768 wrote:As a ref cursor needs to be returned, that is what needs to be created. An execute immediate creates a cursor that is addressed implicitly. Not a cursor that can be addressed via a ref cursor interface.
If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:
■Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
■Use the OPEN FOR, FETCH, and CLOSE statements.
i have to return multiple rows from SP ,using sys_recursor as out param to BL or presentation layer.
1) which one should i use. pls tel me pros and cons.
As a ref cursor needs to be returned, no bulk fetching/collecting is required - the client that receives the ref cursor (pointer), will address the cursor via that and do the fetching of the output returned by the cursor.