14 Replies Latest reply on Sep 14, 2018 8:31 AM by Billy~Verreynne

    How to build parameterized report in Oracle Apex

    3780478

      I'm using APEX 4.2. I have a report with SQL Query in the Region Source. is it possible to have Interactive report with dynamic sql, as my query keeps changing from the UI, including the view name, order by etc.

       

       

      This is how the fields look like, and depending on user input should pull the report.

      Capture.PNG

      My query looks like

       

      SELECT DISTINCT T.BILL_NUMBER, T.DETAIL_LINE_ID,

      T.ORIGCITY, T.ORIGPROV, T.DESTCITY, T.DESTPROV,

      ----------------------------------------------------------------

      FROM ******* T

      where T.CUSTOMER =:P4_SHIPPER and

           T.ORIGNAME = :P4_SHIPPER_NAMER

       

      My question is how to write where clause, as it will keep changing depending on user input.

       

      Thank You

        • 1. Re: How to build parameterized report in Oracle Apex
          StewStrykerNH

          First, please create a profile with name and some details so we can learn who we're working with.

           

          Sorry to say, but you can't create an Interactive Report using dynamic SQL exactly.

           

          However, if the fields displayed and general structure stayed the same, I suspect it would be possible to build an Oracle view that worked this way.  Something like this:

           

          CREATE TABLE my_test_ref

             (code_value VARCHAR2(32),

              display_value VARCHAR2(120));

           

          INSERT INTO my_test_ref

              (code_value,

               display_value)

          VALUES

              ('ABLE',

               'Able');

           

          INSERT INTO my_test_ref

              (code_value,

               display_value)

          VALUES

              ('BAKER',

               'Baker');

           

          INSERT INTO my_test_ref

              (code_value,

               display_value)

          VALUES

              ('CHARLIE',

               'Charlie');

           

          INSERT INTO my_test_ref

              (code_value,

               display_value)

          VALUES

              ('DELTA',

               'Delta');

          COMMIT;

           

          SELECT *

            FROM my_test_ref;

           

          SELECT *

            FROM (SELECT *

                    FROM my_test_ref a

                    JOIN (SELECT '1' AS query_type

                           FROM dual) b

                      ON 1 = 1

                   WHERE b.query_type = 1

                     AND a.code_value = 'ABLE'

                  UNION

                  SELECT *

                    FROM my_test_ref a

                    JOIN (SELECT '2' AS query_type

                           FROM dual) b

                      ON 1 = 1

                   WHERE b.query_type = 2

                     AND a.code_value = 'BAKER')

          WHERE query_type = 2

           

          The final query shows how you could filter onon query_type to determine which subquery is returned.

           

          Good luck,

           

          Stew

           

          p.s. If this works for you or was helpful, please mark "Correct".

          • 2. Re: How to build parameterized report in Oracle Apex
            Scott Wesley

            Or see responses already present in your other post Stew identified.

            Re: how to dynamically select data in to a report

            • 3. Re: How to build parameterized report in Oracle Apex
              Fernando Lima

              If you have defined two o more querys, and they have to change using a parameter you can use a "Server side" condition to display the report.

              For example.

               

              -IR region 1 source

              select 'a' name from dual;

              -IR region 2 source

              select 'b' name, 'c' state from dual;

               

              Item=> :P2_SHOW_PARAMTER (get parameter)

               

              IR region 1 "Server side condition"=> :P2_SHOW_PARAMTER=1

              IR region 1 "Server side condition"=> :P2_SHOW_PARAMTER=2

              =======================================================================================

              Other way to made it, using "classic report" it have function returning a query, you could use it to return a string on a area field

              return :P2_SHOW_PARAMTER;

               

              but you have to configure some extra skills in classic report

              • 4. Re: How to build parameterized report in Oracle Apex
                Billy~Verreynne

                I do exactly this - by using a substitution variable to inject predicates into the SQL.

                 

                E.g. hidden and protected page item P1_PREDICATE.

                 

                The report SQL look something as follows:

                 

                select * from foo where 1 = 1 &P1_PREDICATE.

                 

                 

                The value for P1_PREDICATE depends on the optional page items serving as filters. E.g.

                -- create the predicate
                if :P1_SHIPPER is not null then
                   :P1_PREDICATE := :P1_PREDICATE || ' and shipper = :P1_SHIPPER';
                end if;
                
                if :P1_TRIP is not null then
                   :P1_PREDICATE := :P1_PREDICATE || ' and trip = to_number(:P1_TRIP)';
                end if;
                
                etc.
                

                 

                There are key security issues when injecting SQL like this.

                 

                Only trusted code is allowed to do the SQL injection. The code injected contains bind variables.

                 

                I use this approach to enable the user to shrink the IRR dataset (max 100,000 rows) to the max allowed, or lower, before slicing, dicing, grouping, or whatever, that IRR dataset.

                • 5. Re: How to build parameterized report in Oracle Apex
                  Fernando Lima

                  How are you concatenating?

                  I can see a cocat like this:

                   

                  select * from foo where 1 = 1 &P1_PREDICATE. 

                  and it'll works bad.

                  select * from foo where 1 = 1 'and shipper = :P1_SHIPPER'

                   

                  you have to return all select query as string

                  'select * from foo where 1 = 1 and shipper = P1_SHIPPER'

                   

                  Please login at apex oracle or create a free account and show me a example i don't know how is you query.

                  • 6. Re: How to build parameterized report in Oracle Apex
                    Billy~Verreynne

                    See https://apex.oracle.com/pls/apex/f?p=86939:1 for the basic approach used - in APEX 4.2 I simply added the substitution variable as is to the IRR's SQL, as SQL function support was not yet available for IRR SQL, and the APEX parser worked a tad differently.

                    • 7. Re: How to build parameterized report in Oracle Apex
                      Scott Wesley

                      There's something about the injection example that gives me the heebee jeebees.

                       

                      In the interest of variation, I just stumbled across this from a training guide.

                       

                      DECLARE
                        v_sql VARCHAR2(2000);
                      BEGIN
                        v_sql := 
                      q'[select 
                        id
                        ,first_names
                        ,name
                        ,dob
                        ,job_title
                        ,par_type
                        ,par_id
                        ,eff_from
                        ,eff_to
                      FROM PARTIES 
                      WHERE par_type = 'I']';
                      
                        IF :Pn_NAME IS NOT NULL THEN
                        v_sql := v_sql || q'[ and upper(name) like upper(:Pn_NAME)||'%']';
                        END IF;
                        IF :Pn_ORG IS NOT NULL THEN
                        v_sql := v_sql || ' and par_id = :Pn_ORG ';
                        END IF;
                      
                        RETURN v_sql;
                      END;
                      
                      • 8. Re: How to build parameterized report in Oracle Apex
                        Billy~Verreynne

                        Well Scott, I guess when you practise safe hex, this form of SQL injection is okay... ;-)

                         

                        Though I would not recommend the crude substitution variable approach, if a better, and more formal, method is available - like a db stored proc function that looks at the item variables and crafts the report SQL for you, as can be done in APEX v18.

                        • 9. Re: How to build parameterized report in Oracle Apex
                          Fernando Lima

                          You could:

                          1) Return complete query, you don't have to concatenate

                          2) Replacement string is not good use &VAR. you have to use :VAR to complete the query.

                          3) In this case you can use decode or case statements to define query

                           

                          select * from user_objects

                          where object_name

                          a.OBJECT_NAME like

                                               '%' || decode(:P1_NAME, null, a.OBJECT_NAME, :P1_NAME) || '%';

                           

                          Finally, if you agree, you could give me access to change you apex demo application and complete your implementation.

                          • 10. Re: How to build parameterized report in Oracle Apex
                            Fernando Lima

                            Here is function returning query example

                             

                            declare

                              query clob := 'select * from user_objects';

                            begin

                              if :P5_NAME is not null then

                                query := query || ' where object_name like ''%' || :P5_NAME || '%''';

                              end if;

                              return query;

                            end;

                            • 11. Re: How to build parameterized report in Oracle Apex
                              Scott Wesley

                              Oh, Billy. That's terrible ;p

                              • 12. Re: How to build parameterized report in Oracle Apex
                                Billy~Verreynne

                                You are proposing concatenating user supplied literals into dynamic code (SQL) - and that is just bloody silly.

                                 

                                As for the DECODE approach - good luck with getting an optimal execution plan.

                                • 13. Re: How to build parameterized report in Oracle Apex
                                  Fernando Lima

                                  I expected that you had in mind all dynamic SQL with user inputs have chance to injection, and you must catch, verify and control it.

                                  I'm helping you in apex with some ideas. I don't mind how you improve your solution.

                                  I hope you get the best solution and post it into apex forum.

                                   

                                  To finish, What's about function returning pipelined table?

                                  select * from table(fn_get(a,b,c,d...,z))

                                  I never thought in execution plan before, but you get one idea more.

                                   

                                  Regards.

                                  • 14. Re: How to build parameterized report in Oracle Apex
                                    Billy~Verreynne

                                    I did not start this thread - am not the OP. Have been using APEX since it was known as Project Marvel.

                                     

                                    The basic question is how does one dynamically add variable predicates to an IR (Interactive Report) SQL, in APEX v4.

                                     

                                    The answer is using  a substitution variable (protected against browser modification), the way I demonstrated in the sample app. Have used this approach in a corporate APEX v4.2.6 app I designed and created many years ago, that is still in use by our customers.

                                     

                                    If APEX v18.x is used, then a function can be used, instead of the more primitive (and inherently dangerous) substitution variable approach.

                                     

                                    As I stated more than once, the key issue is preventing the web user from injecting code when using this method. And this needs to be done by protecting the substitution variable from being set by the browser, and only injecting bind variables (never literals) into the code.

                                     

                                    What do you disagree with, with this approach for APEX v4?