5 Replies Latest reply: Aug 5, 2012 8:42 AM by CharlesLiam RSS

    APEX 4.1 Report region Source

    CharlesLiam
      Hi everyone,

      I have created a region source from table name which vary from 3 combo box. Is there a way to present this query to generate a report with proper column name not using Use Generic Column Names (parse query at runtime only) ?

      Type: SQL Query (PL/SQL function body returning SQL Query)
      Region Source: Here's my pl/sql:

      DECLARE
      x VARCHAR2 (4000);
      BEGIN
      x :=
      'Select * from PDV_'
      || CASE
      WHEN :P2_CASE_NAME IS NOT NULL
      THEN ''||:P2_CASE_NAME||''
      END
      || CASE
      WHEN :P2_CASE_NUMBER_ID IS NOT NULL
      THEN ''||:P2_CASE_NUMBER_ID||''
      END
      || CASE
      WHEN :P2_SOURCE_TX IS NOT NULL
      THEN ''||:P2_SOURCE_TX||';'
      END;
      return x;
      END;

      Thanks,
      CL

      Edited by: CharlesLiam on Aug 5, 2012 12:31 AM

      Edited by: CharlesLiam on Aug 5, 2012 12:32 AM
        • 1. Re: APEX 4.1 Report region Source
          jariola
          Hi,

          You can create function that returns your column names. Or set one item to hold list of column names. And in report attributes use Heading Type PL/SQL
          >
          Function returning colon delimited headings:

          Enter a PL/SQL command to return a string of column headings delimited by colons. For example, entering the string "return 'Deptno:DName:Loc'" would be sufficient for the SCOTT.DEPT table.
          >

          Regards,
          Jari

          -----
          My Blog: http://dbswh.webhop.net/dbswh/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai

          Edited by: jarola on Aug 5, 2012 12:47 PM
          • 2. Re: APEX 4.1 Report region Source
            CharlesLiam
            Hi Jari,

            Thanks for sharing Jari. I'll try that out. That's an interesting way to solve this problem.

            To drill in
            x := 'Select * from PDV_'****

            The view PDV_***** is originally taken from table pdv_ctrl and pdv_detail.
            Table pdv_ctrl is where I take the column name for pdv_*****.
            The table pdv_detail is where the records for pdv_*****.
            • 3. Re: APEX 4.1 Report region Source
              fac586
              CharlesLiam wrote:
              Hi everyone,

              I have created a region source from table name which vary from 3 combo box. Is there a way to present this query to generate a report with proper column name not using Use Generic Column Names (parse query at runtime only) ?

              Type: SQL Query (PL/SQL function body returning SQL Query)
              Region Source: Here's my pl/sql:
              All code should be posted wrapped in tags<tt>\
              ...\
              </tt> tags
              to preserve formatting and prevent it being interpreted by the forum software.
              DECLARE
              x VARCHAR2 (4000);
              BEGIN
              x :=
              'Select * from PDV_'
              || CASE
              WHEN :P2_CASE_NAME IS NOT NULL
              THEN ''||:P2_CASE_NAME||''
              END
              || CASE
              WHEN :P2_CASE_NUMBER_ID IS NOT NULL
              THEN ''||:P2_CASE_NUMBER_ID||''
              END
              || CASE
              WHEN :P2_SOURCE_TX IS NOT NULL
              THEN ''||:P2_SOURCE_TX||';'
              END;
              return x;
              END;
              At build time when the report query is validated there is no <tt>P2_CASE_NAME/P2_CASE_NUMBER_ID/P2_SOURCE_TX</tt> items/values to be evaluated (these are components of your app, not the Application Builder that is currently running), so the function returns the invalid query <tt>Select * from PDV_</tt>, which forces use of the Use Generic Column Names (parse query at runtime only) option to bypass query validation at build time.

              Set up the function so that it returns a valid query at build time, either by providing default values for the page items:
              declare
                x varchar2(4000);
              begin
                x :=    'select * from pdv_'
                     || dbms_assert.simple_sql_name(
                              coalesce(:p2_case_name, 'foo')
                           || coalesce(:p2_case_number_id, '1')
                           || coalesce(:p2_source_tx, 'bar'));
                return x;
              end;
              or by returning an equivalent column specification using a basic query:
              declare
                x varchar2(4000);
              begin
                if     :p2_case_name is not null
                   and :p2_case_number_id is not null
                   and :p2_source_tx is not null
                then
                  x :=    'select a, b, c from pdv_'
                       || dbms_assert.simple_sql_name(
                                :p2_case_name
                             || :p2_case_number_id
                             || :p2_source_tx);
                else
                  x := 'select null a, null b, null c from dual';
                end if;
                return x;
              When generating dynamic queries, always eliminate the possibility of SQL Injection:

              <li>Do not use <tt>SELECT *</tt> if the column names are known: use a column list.
              <li>Validate variables contain what they're supposed to using <tt>dbms_assert</tt> methods.

              >
              x := 'Select * from PDV_'****

              The view PDV_***** is originally taken from table pdv_ctrl and pdv_detail.
              Table pdv_ctrl is where I take the column name for pdv_*****.
              The table pdv_detail is where the records for pdv_*****.
              >

              However the need to use this approach makes me very suspicious of the data model you are using...
              • 4. Re: APEX 4.1 Report region Source
                CharlesLiam
                Hi Jari,

                I followed your instruction and I found what I'm looking for a COLUMN NAME not generic. Your idea really helps. That's my first time to use Report attribute - Heading Type: PL/SQL. Here's what I added.

                <div class="jive-quote">
                DECLARE
                TYPE col_table
                IS
                TABLE OF all_tab_columns.column_name%TYPE INDEX BY binary_integer;
                t_colname col_table;
                colname_list VARCHAR2(4000) := '';

                BEGIN
                SELECT column_name BULK COLLECT
                INTO t_colname
                FROM all_tab_columns
                WHERE owner = 'BCVSOWN'
                AND upper(table_name) = upper('PDV_'||:p2_case_name||:p2_case_number_id||:p2_source_tx);

                FOR i IN 1..t_colname.LAST
                LOOP
                IF i = t_colname.LAST THEN
                colname_list := colname_list||t_colname(i);
                ELSE
                colname_list := colname_list||t_colname(i)||':';
                END IF;
                END LOOP;

                RETURN colname_list;
                END;
                </div>

                I also learn here not to create the function object in sql command then calling the function in Report attribute - Heading Type: PL/SQL.

                Thank you very much
                CL
                • 5. Re: APEX 4.1 Report region Source
                  CharlesLiam
                  Hi fac586,

                  Sorry about the forum ethics. Now I know. Honestly I thought those frame background between pl/sql was created by doing copy paste. :)

                  Thank you also for adding some insight on how to solve this. I often used built-in system packages. Your example give me more reason that I need to dig more on dbms_** packages.

                  Thanks,
                  CL