Oracle Interview Questions – Part 29

Q.What are different types of canvas views?
Content canvas views
Stacked canvas views
Horizontal toolbar
vertical toolbar.

Q.How can a button be used in a report to give a drill down facility?
By setting the action associated with button to Execute pl/sql option and using the SRW.Run_report function.

Q.What are three panes that appear in the run time pl/sql interpreter?
1. Source pane.
2. interpreter pane.
3. Navigator pane.

Q.To display the page no. for each page on a report what would be the sourceĀ 
& logical page no. or & of physical page no.?

& physical page no.

Q.Give the sequence in which triggers fired during insert operations, when the following 3 triggers are defined at the same block level ?


Q.State the order in which these triggers are executed ?


Q.What are the display styles of an alert?
Stop, Caution, note

Q.What are the different types of windows?
Root window, secondary window.

Q.What is the built-in function used for finding the alert?

Q.List the editors available in forms 4.0?
Default editor
User_defined editors
system editors.

Q.Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

Q.Name the tables where characteristics of Package, procedure and functions are stored ?

User_objects, User_Source and User_error

Q.What is a Virtual Private Database? (for DBA)
Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies’ data within the same schema, without them knowing about it. VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

Q.What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.

Q.What are the cursor attributes used in PL/SQL?
ISOPEN – to check whether cursor is open or not
ROWCOUNT – number of rows fetched/updated/deleted.
FOUND – to check whether cursor has fetched any row. True if rows are fetched.
NOT FOUND – to check whether cursor has fetched any row. True if no rows are featched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Q.What is Database Trigger?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.

Q.Display the number value in Words?
SQL> select sal, (to_char(to_date(sal,’j’), ‘jsp’))
from emp;
the output like,
——— —————————————-
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like, Rs. Three Thousand only.
SQL> select sal “Salary “,
(‘ Rs. ‘|| (to_char(to_date(sal,’j’), ‘Jsp’))|| ‘ only.’))
“Sal in Words” from emp
Salary Sal in Words
——- ———————————————–
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.

Q.What is difference between Rename and Alias?
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.

Q.How you open and close a cursor variable. Why it is required?
OPEN cursor variable FOR SELECT…Statement
CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used. In order to free the resources used for the query CLOSE statement is used.

Q.Display Odd/ Even number of records?
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

Q.What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.

Q.Can the default values be assigned to actual parameters?

Q.What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.

Q.What is Fine Grained Auditing? (for DBA)
Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:
o Add policy on table with autiting condition…
execute dbms_fga.add_policy(‘HR’, ‘EMP’, ‘policy1′, ‘deptno > 10′);
o Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;
select * from EMP where c1 = 11; — Will trigger auditing
select * from EMP where c1 = 09; — No auditing
o Now we can see the statments that triggered the auditing condition…
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

Q.System.effective_date system variable is read only True/False?

Q.What is an Alert?
An alert is a modal window that displays a message notifies the operator of some application condition

Q.What is a predefined exception available in forms 4.0?
Raise form_trigger_failure

Q.What is a radio Group?
Radio groups display a fixed no of options that are mutually Exclusive. User can select one out of n number of options.

Q.Committed block sometimes refer to a BASE TABLE ?

Q.Two popup pages can appear on the screen at a time ?Two popup pages can appear on the screen at a time ?
a. True. b. False?
a. True.