2 Replies Latest reply on Jul 23, 2018 5:06 PM by rp0428

    exporting from ref cursor functions

    3747648

      when execute function return sys_refcursor  the result can i showd in the outout variable log tab  , now the problem there is no way to export this result to excel sheet  directly with out copy past method
      is any upgrading for this point  or any  alternated way to export without copy past

      thank you

        • 1. Re: exporting from ref cursor functions
          thatJeffSmith-Oracle

          Copy paste is the only way today.

           

          It's a known enhancement request.

          • 2. Re: exporting from ref cursor functions

            Just write a simple pipelined function that utilizes your ref cursor as the data source.

             

            Then execute a simple query like:

            select * from table(myPipelinedFunction)

            Then you can export those results.

             

            The Oracle docs have examples of writing and using pipelined functions and, specifically, an example using a REF CURSOR as input to the function

             

            Just search for 'oracle 12c pipelined functions'

            https://docs.oracle.com/database/121/ADDCI/pipe_paral_tbl.htm#ADDCI2140

            A table function can take a collection of rows as input. An input collection parameter can be either a collection type or a REF CURSOR.

            . . .

            Pipelined Table Functions with REF CURSOR Arguments

            A pipelined table function can accept any argument that regular functions accept. A table function that accepts a REF CURSOR as an argument can serve as a transformation function. That is, it can use the REF CURSOR to fetch the input rows, perform some transformation on them, and then pipeline the results out (using either the interface approach or the native PL/SQL approach).

            Try the example in the doc and then adapt it to your own needs.

             

            Then you will have a nice utililty function you can use whenever you have other REF CURSORS whose results you want to export.