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.