Problem solve Get help with specific problems with your technologies, process and projects.

Changing the actual SQL of the predicate in the middle of a form

Aloha and thank you for making yourself open to questions.

Regards: VPD/FGAC, changing the actual SQL of the predicate in the middle of a form not just the bind variable.

Background: Oracle 8i 8.17 and up
Oracle forms (with embedded PL/SQL engine. using assented

Example: Taking a standard HR example depending on whether you are a manager or just an employee, your "predicate" is different. When you are an employee the predicate is:

where employ = "your number"

When you are a manager predicate is

 where employ no in (select * from employed where Dept No = "your Dept")

A team we are working with suggested we should change from being an employee to manager in the middle of a Oracle form (gui and associated code). I'm not comfortable with this, as it would take re parsing the SQL each time (to make sure we have the correct predicate). Is there anything I'm just not seeing?

Oracle 9i facilitates this much easier than 8i. With 9i, you can just change your assigned role and it will append the correct predicate to your SQL statement. You don't have to change a thing other than your assigned role.

Oracle 8i makes things a little more difficult. You'll most likely have to use dynamic SQL statements in your Forms application. Re parsing shouldn't be too bad of a problem since all of these statements will eventually be parsed, and hopefully with bind variables, you'll find them in the Shared Pool, parsed and ready to go!


For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.