2 Replies Latest reply on Jul 12, 2013 1:15 PM by Ramesh P, Oracle APEX Developer

    Ref cursor in Apex

    1024707

      Hello Everyone ,

       

      We had a requirement for the documentation team where the team would be able to check the DDL changes made on a particular day in the database .  The documentation team has to enter the date and the changes made on the respective date will be displayed on apex . I created a Store proc where I am giving date as IN parameter and ref cursor is an out parameter . The procedure is running in the database . In apex , i tried the forms on procedure option but was struggling with it .  Please can anyone suggest a possible workaround .

       

      The refcursor output is basically select output from a global temporary table.

        • 1. Re: Ref cursor in Apex
          Z?

          Hi, you need create a piplened function and then you can simply SELECT against it in a report region.

           

          Have a look at this example...

           

          https://forums.oracle.com/message/9142189#9142189

           

          Cheers

           

          Ben

          • 2. Re: Ref cursor in Apex
            Ramesh P, Oracle APEX Developer

            create or replace

            PACKAGE TEST

            AS

             

            TYPE view_det_rec

            IS

                RECORD

                (

                    file_name VARCHAR2(4000),

                    rec_sent  NUMBER,

                    rec_recd  NUMBER,

                    status    VARCHAR2(4000));

             

            type view_det_tab

            IS

                TABLE OF view_det_rec;

             

            FUNCTION view_det_report(

                    p_area IN VARCHAR2)

                RETURN view_det_tab pipelined;

             

            END TEST;

             

             

            create or replace

            PACKAGE BODY "TEST"

            AS

             

            FUNCTION view_det_report(

                    p_area IN VARCHAR2)

                RETURN view_det_tab pipelined

            AS

                v_view_det_rec test_reports.view_det_rec;

                v_view_det_tab test_reports.view_det_tab;

                v_det_rcur sys_refcursor;

            BEGIN

                v_view_det_tab := view_det_tab();

                /*--Call to get the detail information as a ref cursor into v_det_rcur ref cursor variable*/

                schm.test_pkg.view_details (p_area, v_det_rcur);

                LOOP

                    FETCH v_det_rcur

                    INTO v_view_det_rec.file_name ,

                        v_view_det_rec.rec_sent   ,

                        v_view_det_rec.rec_recd   ,

                        v_view_det_rec.status;

                    EXIT

                WHEN v_det_rcur%notfound;

                    pipe row(v_view_det_rec);

                END LOOP;

                RETURN ;

            END view_det_report;

             

            END "TEST";

             

            SELECT * from TABLE(test.view_det_report(:P1_AREA))