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:
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?