5 Replies Latest reply on Jun 13, 2013 9:14 AM by Christian Erlinger

    Forms 11gR1 with Database Package

    Stephane Baribeau

      Hi everyone,


      I'm asking this here, i need to know what i'm doing wrong.

      I have a database package that contains pipiled record.


      I want to use it in a form with forms builder.


      if i do



      select * from table(mypackage.myfunction());



      in PL/SQL Developper, i get all what i except (list of record)


      if i try to use this select in a CURSOR inside my form, i get error about SQL type not authorised

      with my from table();


      but if i use it with a populate_group_with_query, everything works perfectly.


      So, there's a way i can use my package inside a form program unit?


      Thanks for your help.

        • 1. Re: Forms 11gR1 with Database Package

          The PL/SQL interpretor built into Oracle Forms is not at the same level as the database.  Consequently, depending on where SQL or PL/SQL is parsed - your code will pass or fail.  In the case where you get an error in a CURSOR, the Forms PL/SQL interpretor is validating the SQL statement and it fails because it doesn't support the pipelined construct.  However, because the POPULATE_GROUP_WITH_QUERY send the SQL statement to the database to be parsed/validate, it succeeds because the database does support pipelined construct and Forms is simply receiving the record set returned by the database.


          Neimad wrote:

          So, there's a way i can use my package inside a form program unit?

          Yes, have your database package populate and return a collection; which Forms can handle.  Then, in your Forms trigger just LOOP through the collection to process/display the data.  Another option would be to use the pipelined query in your package.



          • 2. Re: Forms 11gR1 with Database Package
            Stephane Baribeau

            Thanks for your answer,

            Have you an example to populate the database package to return a collection? Is it like POPULATE_GROUP_WITH_QUERY?


            Thanks again for your help, i'm lost about this.


            Have a nice day!

            • 3. Re: Forms 11gR1 with Database Package

              No, working with collection is not like the POPLUATE_GROUP_WITH_QUERY built-in because you have to write the code to process the rows and columns of the collection.  A collection, in Oracle, is essentially an Array.

              To work with a collection, you have to create an object in your trigger PL/SQL to store the collection.  To do this you create a user defined TYPE and then create an object based on this type.  Take a look at the article, https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-procedure-based-block for an example of how to write a database package the uses a collection as the source of data for a Forms data block.

              Base on this article, in your Forms trigger you would then create an object typed off of the package type T_EMPLOYEE.  Next, you call your database package and assign the collection to your trigger object.  Finally, you loop through the collection to perform the needed action on each row.  Here is an example of the basic code using the article above as an example:

              /* sample code - for demonstration purposes */
              /* code is untested */
                rec_employees   EMPLOYEE.T_EMPLOYEE;
                FOR idx in 1 .. rec_employees.COUNT LOOP
                  -- now do something with each column in the row...
                  rec_employees(idx).empno ...
                  rec_employees(idx).ename ...
                  rec_employees(idx).job ...
                END LOOP;

              Hope this helps.


              • 4. Re: Forms 11gR1 with Database Package
                Stephane Baribeau

                Thanks a lot for your help.

                I will read the article and try it. I hope this will be ok!



                • 5. Re: Forms 11gR1 with Database Package
                  Christian Erlinger

                  Well, if your pipelined function doesn't have arguments the easiest way would be to wrap a view around it and select from the view. If you have arguments there is another ugly workaround for that; just make getter/setters for the parameters and call them accordingly:


                  create or replace package body get_set as
                    val1 number;
                  procedure set_val1(inval in number) is
                  val1 := inval;
                  function get_val1 return number is
                  return val1;


                  and create the view like


                  create or replace force view my_view as select * from mypackage.myfunction(get_set.get_val1);


                  Then you'd simply need to call get_set.set_val1 in forms before you open a cursor on my_view. It is a ugly workaround, but IMHO better then using dynamic SQL.