2 Replies Latest reply: Jul 13, 2012 4:46 AM by 915879 RSS

    LOV based on "all_procedures"

    915879
      Hi,

      We've got a package containing functions that make up certain DB fields. (for example f_get_Description, by using the ID);

      Now, we got a form where users can build their own template, by selecting a number of fields.
      When they select the field "Description", and save that template, the function isn't automatically linked obviously, so I added an extra field, "Function", where they can select one of the functions (from the package) that they want linked to that field.

      Instead of creating a table that lists all these functions, I made a view (in Designer, not in the database itself, don't know if that would be needed?), that lists every function from the package.

      The view's query is like this:



      SELECT p.procedure_name
      FROM all_procedures p
      JOIN all_objects o ON (o.owner = p.owner
      AND o.object_name = p.object_name AND o.object_type = 'PACKAGE')
      WHERE p.object_name = 'PCK_TMOF' AND p.procedure_name <> 'INIT'



      Now, I made this view in Designer, then went to the form/module, and created a new LOV based on this view. But when I want to compile, it's unsuccesful...
      I tried to compile it in Forms instead (to see what exactly caused it to fail), and it gave this message:
      "FRM-30064: Unable to parse statement SELECT VTC.P_PROCEDURE_NAME P_PROCEDURE_NAME
      FROM VW_TMOF_FUNC VTC.
      ORA-00904: "VTC"."P_PROCEDURE_NAME": invalid identifier"

      Is it that I'm forgetting something, or is it just impossible to make a SELECT from table "all_procedures" (and all_objects) in Designer?

      Thanks.

      Greets

      Edited by: user11236545 on Jul 13, 2012 11:02 AM