1 Reply Latest reply: Jan 27, 2013 2:57 AM by 947771 RSS

    EXECUTE IMMEDIATE vs OPEN FOR

    947771
      Hi,

      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.

      yours sincerely
        • 1. Re: EXECUTE IMMEDIATE vs OPEN FOR
          Billy~Verreynne
          944768 wrote:

          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, 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.

          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.