4 Replies Latest reply: Feb 12, 2013 5:15 AM by Joel_C RSS

    dynamic sql in classic report

    Boris Girsch
      Hi everyone,

      I have following question/task:

      I have to generate an classic report. The problem is that the source of the report should be and dynamically generated sql query. The number and names of columns will always be the same. I already created the function which returns an varchar2 value, which in turn holds dynamically generated query. Lets say the function which generates the query is da_sql_flow_gen(v_authid number). As you can see the function takes v_authid as a parameter. v_authid it self will be a page item which will contain some random number.

      I need something like this to be a source of the region
      begin
      return 'select da_sql_flow_gen(:P4_ID) from dual;';
      end;
      but it does not work. Any suggestions ?

      Or can I solve my task only with "pl/sql dynamic content" region ?

      Thanks

      Edited by: Boris Girsch on Feb 11, 2013 6:29 AM

      Edited by: Boris Girsch on Feb 11, 2013 6:37 AM
        • 1. Re: dynamic sql in classic report
          fac586
          Boris Girsch wrote:
          Hi everyone,

          I have following question/task:

          I have to generate an classic report. The problem is that the source of the report should be and dynamically generated sql query. The number and names of columns will always be the same. I already created the function which returns an varchar2 value, which in turn holds dynamically generated query. Lets say the function which generates the query is da_sql_flow_gen(v_authid number). As you can see the function takes v_authid as a parameter. v_authid it self will be a page item which will contain some random number.

          I need something like this to be a source of the region
          begin
          return 'select da_sql_flow_gen(:P4_ID) from dual;';
          end;
          but it does not work. Any suggestions ?
          It should be:
          begin
            return da_sql_flow_gen(:P4_ID);
          end;
          • 2. Re: dynamic sql in classic report
            Boris Girsch
            It says
            Query cannot be parsed, please check the syntax of your query. (ORA-00936: missing expression)
            • 3. Re: dynamic sql in classic report
              Boris Girsch
              well it works now,

              the creation dialog of "classic report" was somehow misleading. Your suggestion to put
              begin
                return da_sql_flow_gen(:P4_ID);
              end;
              into the source was right, but you have to put it after you already created the region, otherwise you will not be able to create the region.

              Than you choose "SQL Query (PL/SQL function body returning sql query)" on the report configuration page
              as next choose "Use Generic Column Names (parse query at runtime only)" on the same page and specify the number of columns.

              Thats basically it.

              BTW forgot to mention that Im on apex 4.2

              Edited by: Boris Girsch on Feb 11, 2013 9:07 AM
              • 4. Re: dynamic sql in classic report
                Joel_C
                fac586 wrote:
                Boris Girsch wrote:
                Hi everyone,

                I have following question/task:

                I have to generate an classic report. The problem is that the source of the report should be and dynamically generated sql query. The number and names of columns will always be the same. I already created the function which returns an varchar2 value, which in turn holds dynamically generated query. Lets say the function which generates the query is da_sql_flow_gen(v_authid number). As you can see the function takes v_authid as a parameter. v_authid it self will be a page item which will contain some random number.

                I need something like this to be a source of the region
                begin
                return 'select da_sql_flow_gen(:P4_ID) from dual;';
                end;
                but it does not work. Any suggestions ?
                It should be:
                begin
                return da_sql_flow_gen(:P4_ID);
                end;
                The approach you advocate forces you to use generic column names, whereas generating the same query in a functionaly identical anonymous block doesn't.

                There is a workaround whereby if you wrap the call to the SQL-generating function within another SQL statement, you can use the named columns option:
                   begin
                      return 'SELECT * from ('||da_sql_flow_gen(:P4_ID)||')';
                   end;
                I've not installed 4.2 yet so perhaps they've closed off this particular loophole (or indeed, fixed the "bug").