Oracle – Technical FAQ – Part 2

Q6) What are the database triggers and stored procedures?
A6) Database triggers are procedures that are automatically executed as a result of insert in, update to or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which can not be enforced using the integrity rules. We can have the trigger as before trigger or after trigger and at statement or row level.
e.g. operations insert, update, delete — 3
before, after 3 * 2 — a total of 6 combinations

At statement level (once for the trigger) or row level (for every execution) 6 * 2 — a total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 onwards

Stored Procedures : Stored procedures are procedures that are stored in complied form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.


Q7) How many integrity rules are there and what are they?

A7) There are three integrity rules and they are as follows

a)Entity integrity Rule : The entity integrity rule enforces that the primary key can not be Null

b) Foreign key integrity rule : The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in child tables the master tables can not be deleted.

c) Business integrity rules : The third integrity rule is about the complex business processes which can not be implemented by the above 2 rules.


Q.8.) What are the various Master and Detail relationships?

A.8.) The various Master and Detail relationship are

a) Non Isolated : The Master can not be deleted when a child is existing

b) Isolated : The master can be deleted when the child is existing

c) Cascading : The child gets deleted when the master is deleted.


Q9) What are the various Block Coordination properties ?.

A9) The various block coordination properties are

a) Immediate – Default setting. The detail records are shown when the Master Record are shown.

b) Differed with AutoQuery — Oracle forms defer fetching the detail records until the operator navigates to the detail block.

C) Deffered with no auto query — The operator must navigate to the detail block and explicitly executes a query.


Q10) What are the different Optimisation techniques ?

A10) The various optimisation techniques are

a) Execute plan : We can see the plan of the query and change it accordingly based on the indexes

b) Optimizer_hint : Set_item_property (‘DeptBlock’,OPTIMIZER_HINT,’FIRST_ROWS’);

Select /* + First Rows */ Deptno, Dname, Loc, Rowid from dept

where (Deptno > 25)

c) Optimize_sql : By setting the optimize_sql = No, Oracle Forms assign a single cursor for all SQL statements. This slow downs the processing because for everytime the SQL must be parsed whenever they are executed. f45run module = my_firstform useid = scott/tiger optimize_Tp = No


  more questions are here :

Part 3 is here :

Part 1 is here :




4 thoughts on “Oracle – Technical FAQ – Part 2

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s