This discussion is archived
6 Replies Latest reply: Jan 14, 2013 1:30 PM by NewApexCoder RSS

pl/sql report

NewApexCoder Newbie
Currently Being Moderated
Is it possible to generate a report region using a pl/sql block? As I am writing/testing out my pl/sql, my select statements aren't producing any columns on the page to be displayed.
  • 1. Re: pl/sql report
    matthew_morris Expert
    Currently Being Moderated
    Is it possible to generate a report region using a pl/sql block?
    The answer is yes. However, your post doesn't make it clear specifically what you are attempting and what is going wrong. As such, there's little I, or anyone else, can do to assist.
  • 2. Re: pl/sql report
    fac586 Guru
    Currently Being Moderated
    NewApexCoder wrote:
    Is it possible to generate a report region using a pl/sql block?
    Report regions can use a PL/SQL function block to return the SQL SELECT statement used as the region source, enabling reports with dynamic data sources/projections/predicates to be created.

    It's also possible to create report regions with SQL sources that query data produced by more advanced PL/SQL techniques, such as APEX collections or pipelined functions.

    Dynamic PL/SQL regions use PL/SQL blocks to generate HTML and output it using programs in the PL/SQL Web Toolkit, but these are not classed as report regions. Generally Dynamic PL/SQL regions are less efficient and harder to develop than reports.
    As I am writing/testing out my pl/sql, my select statements aren't producing any columns on the page to be displayed.
    What does this mean? What are you trying to do?
  • 3. Re: pl/sql report
    NewApexCoder Newbie
    Currently Being Moderated
    hmm,....I see.

    I am trying to create a report that gets information from multiple tables. But I need to create a loop within the sql to get certain desired information, thus the need for pl/sql.

    However, I may be looking at this the wrong way. Perhaps a pretty lengthy and complicated query may be necessary. I can provide the overall scope of my project if neceesary.
  • 4. Re: pl/sql report
    NewApexCoder Newbie
    Currently Being Moderated
    I have a table called SCHEDULE. This a a parent table and its two child tables are TASKS and STATUS. A schedule can have many tasks and many statuses. There can also be a scenario where schedule A has many or one task and/or many or one status and schedule B can have the same. I need to pull the information for each schedule in a report.

    I may be able to accomplish this with a few joins and some grouping clauses as opposed to a pl/sql block. On the report I want the information to be separated (page break'ed) to distinguish from schedules. I'm still learning APEX and I recall functionailty to put a break in between rows/records to separate information

    Does this seem feasable?
  • 5. Re: pl/sql report
    fac586 Guru
    Currently Being Moderated
    NewApexCoder wrote:
    I have a table called SCHEDULE. This a a parent table and its two child tables are TASKS and STATUS. A schedule can have many tasks and many statuses. There can also be a scenario where schedule A has many or one task and/or many or one status and schedule B can have the same. I need to pull the information for each schedule in a report.

    I may be able to accomplish this with a few joins and some grouping clauses as opposed to a pl/sql block. On the report I want the information to be separated (page break'ed) to distinguish from schedules. I'm still learning APEX and I recall functionailty to put a break in between rows/records to separate information

    Does this seem feasable?
    Yes, that would be the recommended approach. Note that it's very important when discussing "reports" to explicitly state whether you're using standard or interactive reports. I'm assuming a standard report as they offer more possibilities for formatting and customisation.

    Breaks in standard reports are (and always have been) hopelessly under-documented. Search the forum for previous discussion and experiment...

    Custom report templates may also be useful to get the required layout. Again, there are various examples in previous forum threads.
  • 6. Re: pl/sql report
    NewApexCoder Newbie
    Currently Being Moderated
    I appreciate the feedback and links to helpful information. Thanks again!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points