2 Ответы Последний ответ: 10.07.2018 13:07, автор: fac586

    Interactive Grid - Function returning SQL query

    Prashanth Raju-Oracle

      Hi,

       

      Can we call an Function returning SQL query in "Interactive Grid" SQL Query source? My report query has many Unions to display the data in IG. Hence I would like to embed it in a function and call the same within the IG region source. Is this possible?

       

      My report query looks something like this:

       

      select cols from view1 where a=b

      union

      select cols from view2 where c=d

      union

      select cols from view3 where e=f;

       

      Thanks,

      Prashanth

        • 1. Re: Interactive Grid - Function returning SQL query
          Alli Pierre Yotti

          Hi,

           

          you can use Pipelined Table Functions

           

          1- create a package  spec like here

           

          create or replace package cols_dynamic_sql as

              type cols_rec_tabelle is record (

              cols varchar2(255));

              type cols_tabelle_tab_report is

                  table of cols_rec_tabelle;

              function get_cols_table

              return cols_tabelle_tab_report

                  pipelined;

          end cols_dynamic_sql;

           

          2- create a package body

          create or replace package body cols_dynamic_sql as

           

           

              function get_cols_table return cols_tabelle_tab_report

                  pipelined

              is

                  v_cols  varchar(255);

                  v_cols_record   cols_rec_tabelle;

                  type cols_ref is ref cursor;

                  cols_ref_cv     cols_ref;

              begin

                  open cols_ref_cv for select

                      1 cols

                                         from

                      dual

                  union

                  select

                      2 cols

                    from

                      dual

                  union

                  select

                      3 cols

                    from

                      dual;

           

           

                  loop

                      fetch cols_ref_cv   into v_cols;

                      exit when cols_ref_cv%notfound;

                      v_cols_record.cols := v_cols;

                      pipe row ( v_cols_record );

                  end loop;

           

           

                  close cols_ref_cv;

                  return;

              end get_cols_table;

           

           

          end cols_dynamic_sql;

           

          3- call that so in your IG

           

          select

              cols

            from

              table ( cols_dynamic_sql.get_cols_table )

           

          demo

           

          https://apex.oracle.com/pls/apex/f?p=59229:1

          • 2. Re: Interactive Grid - Function returning SQL query
            fac586

            Prashanth Raju-Oracle wrote:

             

             

            Can we call an Function returning SQL query in "Interactive Grid" SQL Query source? My report query has many Unions to display the data in IG. Hence I would like to embed it in a function and call the same within the IG region source. Is this possible?

             

            My report query looks something like this:

             

            select cols from view1 where a=b

            union

            select cols from view2 where c=d

            union

            select cols from view3 where e=f;

            Looks like a use case for a view rather than a function.

             

            With a view it will be easier to make the IG editable (use INSTEAD OF triggers), and result in better performance than a pipelined function.

            1 пользователь считает эту информацию полезной