1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Another option for an APEX report (Classic or Interactive)

Discussion in 'Oracle Application Express (APEX)' started by DTSIGuy, May 14, 2014.

  1. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Learned something new about what is possible in APEX w/ respect to creating a report and thought I'd share that.

    By default, when you create a Report you have two options for the source :
    - The standard SQL query​
    - A function that returns a query​

    However, I find there are times when I need the report to be based on the results of a PL/SQL process and that presents a problem. Essentially, I needed the results of date arithmetic, counters, summations and so forth that are not part of the table...it's all derived from the table. However, I had no idea how to go about doing that w/o resorting to using a view which isn't a good fit for the problem anyway.

    The answer turned out to be a PIPELINED function. At the time, I had no idea what that was but I figured it out.

    Here are the steps in pseudo-code as I have to protect the source... :

    1. Create a package spec w/ a function in it. I prefer to put ALL of my functions/procedures in a package...it just makes more sense. Here's an example of what to put in the package spec :

    Code (SQL):
    TYPE t_results IS RECORD(A NUMBER, B NUMBER, C NUMBER);
    TYPE t_results_tbl IS TABLE OF t_results;

    FUNCTION do_this(Z_IN IN NUMBER) RETURN t_results_tbl PIPELINED;
    2. Now build the function in the package body :

    Code (SQL):
    FUNCTION do_this(FZ_IN IN NUMBER) RETURN t_results_tbl PIPELINED IS
       -- I defined cursors and such here, but the important ones were
       my_row t_results;
    BEGIN
       -- Do all you typical PL/SQL stuff here and somewhere in there...do this
                  my_row.A := ...SOME DATA item;
                  my_row.B := ...SOME DATA item;
                  my_row.C := ...SOME DATA item;
       -- And then the all important
                  PIPE ROW(my_row);                   -- This adds the record to the 'virtual' table
       -- After all the processing is done...
       RETURN;
    END;

    3. Now go build your report and for the source type of SQL query and enter :

    Code (SQL):
    SELECT * FROM TABLE(custompkg.do_this(:P250_ID));
    ...and it worked! I just love it when I learn something new and it actually works out.


    Obviously, this can have some drawbacks...like memory usage, so you'll want to think it bit about how much data you intend to return from the database. In my case I'm returning less than 100 records, each of which consist of 3 numbers that are 5 digits in size. My chief table however has some 400 columns in it (yes, it's stupid) so there's row-chaining going on and it's proprietary so I can't change it...sigh.

    Hope this helps somebody out...had a great time figuring this out.


    CJ