4 Replies Latest reply: Jan 29, 2013 3:14 PM by TexasApexDeveloper RSS

    Oracle Form functions and procedures in APEX, how?

    RLBickham
      I am working to recreate in APEX, already existing Read Only Forms in Oracle Forms. APEX Interactive Report functionality among other things, makes it worth while as well as targetting a different audience than the one that utilizies the Oracle Form versions. Oracle Forms versions use lots of pre and post query triggers, PLSQL Functions.

      In Oracle Forms one of the places those functions can be located is in the "Program Units" section of the Form. A typical function of this sort, based on a specific Mission ID Itinerary, collects scheduled passengers last names, formats them with a comma and space after each one, into a single string that is returned and displayed as the passenger list of one row.

      I have all this code written so I can move most of the main query of the Oracle Form into an Interactive Report. These functions and triggers called from within the Form, from the "Program Units" section of the Oracle Form rather than being stored in the Database schema in a package, where would they go inside APEX? Can I create a "Shortcut" in APEX and call it from the Interactive Report "Region Source"? Can I create the PLSQL function at the page level or region level of the Interactive Report? Or, is my best bet creating a package stored in the database, of all these functions and/or proecedures I may need from the original Oracle Form?

      Some advice would be greatly appriciated.
        • 1. Re: Oracle Form functions and procedures in APEX, how?
          Mahmoud_Rabie
          Dear RLBickham,

          Did you check
          Migration from Oracle Form 6i to ApEx (Application Express).
          http://www.slideshare.net/omebos/oracle-forms-to-apex-ogh-29-september-2009-part-1
          http://roelhartman.blogspot.com/2008/11/integrate-oracle-forms-with-apex.html

          please, if this answers your question, mark it as Correct. Otherwise, as helpful.

          Regards
          Mahmoud
          • 2. Re: Oracle Form functions and procedures in APEX, how?
            RLBickham
            I don't think I have been clear enough in describing the specific thing I want to do, it simply does not reach the level of forms to APEX conversion. It is basically a PLSQL Function problem.

            I have an Interactive Report that is currently getting 90 percent of what I want however, each row, representing a Mission may have multiple legs. Each Leg has two locations or ICAO codes attached to it. Based on the Mission number, I want to loop through the leg table, collect all the ICAO codes for that Mission, put them all into 1 variable separated by a coma and add that variable to the column display of that Interactive Report as the last column.

            In Oracle Forms I have a function saved to the database that is called within the main query. Maybe I am asking a question that does not need to be asked but in any case my question is can I put that function currently in the database somewhere in the confines of the Interactive Report and reference it via Http somehow or should I just stick with putting functions and procedures in packages stored in the database and called the conventional way?
            • 3. Re: Oracle Form functions and procedures in APEX, how?
              fac586
              RLBickham wrote:
              I don't think I have been clear enough in describing the specific thing I want to do, it simply does not reach the level of forms to APEX conversion. It is basically a PLSQL Function problem.

              I have an Interactive Report that is currently getting 90 percent of what I want however, each row, representing a Mission may have multiple legs. Each Leg has two locations or ICAO codes attached to it. Based on the Mission number, I want to loop through the leg table, collect all the ICAO codes for that Mission, put them all into 1 variable separated by a coma and add that variable to the column display of that Interactive Report as the last column.

              In Oracle Forms I have a function saved to the database that is called within the main query. Maybe I am asking a question that does not need to be asked but in any case my question is can I put that function currently in the database somewhere in the confines of the Interactive Report and reference it via Http somehow or should I just stick with putting functions and procedures in packages stored in the database and called the conventional way?
              You could move the function to the database and call it from the report query, but it sounds as if it's superfluous. In the report query use whatever form of Re: 4. How do I convert rows to columns? is appropriate to your (unspecified) database version.

              -----


              When you have a problem you'll get a faster, more effective response by including as much relevant information as possible upfront. This should include:

              <li>Full APEX version
              <li>Full DB/version/edition/host OS
              <li>Web server architecture (EPG, OHS or APEX listener/host OS)
              <li>Browser(s) and version(s) used
              <li>Theme
              <li>Template(s)
              <li>Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)

              With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
              • 4. Re: Oracle Form functions and procedures in APEX, how?
                TexasApexDeveloper
                I don't think I have been clear enough in describing the specific thing I want to do, it simply does not reach the level of forms to APEX conversion. It is basically a PLSQL Function problem.
                
                I have an Interactive Report that is currently getting 90 percent of what I want however, each row, representing a Mission may have multiple legs. Each Leg has two locations or ICAO codes attached to it. Based on the Mission number, I want to loop through the leg table, collect all the ICAO codes for that Mission, put them all into 1 variable separated by a coma and add that variable to the column display of that Interactive Report as the last column.
                
                In Oracle Forms I have a function saved to the database that is called within the main query. Maybe I am asking a question that does not need to be asked but in any case my question is can I put that function currently in the database somewhere in the confines of the Interactive Report and reference it via Http somehow or should I just stick with putting functions and procedures in packages stored in the database and called the conventional way?
                If you are running Oracle 11g or above you can use the listagg function to concatenate the string that you want for each row:

                http://www.oracle-developer.net/display.php?id=515

                Thank you,

                Tony Miller
                Ruckersville, VA