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
- Dozens more answers to tough Oracle questions from Brian Peas land are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow D BAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, Amerada, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.