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.
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.
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!
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 */
FOR idx in 1 .. rec_employees.COUNT LOOP
-- now do something with each column in the row...
Hope this helps.
Thanks a lot for your help.
I will read the article and try it. I hope this will be ok!
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 begin val1 := inval; end; -- function get_val1 return number is begin return val1; end; end; /
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.