3 Replies Latest reply on Mar 19, 2019 7:52 AM by Fabrizio Delli Priscoli

    dynamic table in a from clause

    Fabrizio Delli Priscoli

      Hi all, I'm on APEX 18.2 and database is oracle enterprise edition 12c.

      Here is my scenario.

      I have an interactive report with a form based on a table.

      When I click on a pencil on the beginning of the record I go to the detail (the form). In this form I have many fields but the most important are year and month.

      So depending on value of this 2 fields I must populate a sub-region with some others informations.

      The problem is that these informations are stored in different tables, each for combination year/month: for example I have a table for 1990-1, another for 1990-2, another for 1990-3 and so on.

      What I want is to use the same sub-region and populate this region with informations coming from different tables.

      I have thought to use a PL/SQL region, and store the select in a variable and then execute it, using dynamic SQL.

      Is this approach correct or con you give me some suggestions to help me to solve this problem?

      Thanks for collaboration,

      Fabrizio

        • 1. Re: dynamic table in a from clause
          fac586

          Fabrizio Delli Priscoli wrote:

           

          Hi all, I'm on APEX 18.2 and database is oracle enterprise edition 12c.

          Here is my scenario.

          I have an interactive report with a form based on a table.

          When I click on a pencil on the beginning of the record I go to the detail (the form). In this form I have many fields but the most important are year and month.

          So depending on value of this 2 fields I must populate a sub-region with some others informations.

          The problem is that these informations are stored in different tables, each for combination year/month: for example I have a table for 1990-1, another for 1990-2, another for 1990-3 and so on.

          What I want is to use the same sub-region and populate this region with informations coming from different tables.

          I have thought to use a PL/SQL region, and store the select in a variable and then execute it, using dynamic SQL.

          Is this approach correct or con you give me some suggestions to help me to solve this problem?

          The best way to solve this problem would be to store the data in one table with a DATE column to indicate to indicate the year and month...

          • 2. Re: dynamic table in a from clause
            Fabrizio Delli Priscoli

            I know this, but I have hundred of tables stored in this wrong way in the database.

            Is it possible to use dynamic SQL in the from space of a PL/SQL region? Probably in this way I can resolve the problem.

            Or another solutiond could be to create a working table with structure of informations I need and everytime truncate and re-populate this table and base the region on this working table....

            • 3. Re: dynamic table in a from clause
              Fabrizio Delli Priscoli

              I have resolved my problem.

              I have created a working table in which I insert records using dynamic SQL and i have based my interactive report on this working table.