Oracle Interview Questions – Part 31

Q.Where is the external query executed at the client or the server?

At the server.

Q.What is the difference between OLE Server & Ole Container?

An Ole server application creates ole Objects that are embedded or linked in ole Containers ex. Ole servers are ms_word & ms_excel. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications. Ex. oracle forms is an example of an ole Container.

Q.What is an object group?

An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules.

Q.My database was terminated while in BACKUP MODE, do I need to recover? (for DBA)

If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle7.2, you can simply take the individual datafiles out of backup mode and restart the database.
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.
Thiru Vadivelu contributed the following:
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hot backup mode:
The above commands need to be issued when the database is mounted.

Q.How can a group in a cross products be visually distinguished from a group that does not form a cross product?

A group that forms part of a cross product will have a thicker border.

Q.It is possible to use raw devices as data files and what is the advantages over file. system files ?

Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less.

Q.What is the use of RECORD LENGTH option in EXP command ?

Record length in bytes.

Q.What is the difference between ON-VALIDATE-FIELD trigger and a POST-CHANGE trigger ?

When you changes the Existing value to null, the On-validate field trigger will fire post change trigger will not fire. At the time of execute-query post-change trigger will fire, on-validate field trigger will not fire.

Q.When is PRE-QUERY trigger executed ?

When Execute-query or count-query Package procedures are invoked.

Q.How do you trap the error in forms 3.0 ?

using On-Message or On-Error triggers.

Q.EXIT_FORM is a restricted package procedure ?

a. True b. False

Q.What the PAUSE package procedure does ?

Pause suspends processing until the operator presses a function key

Q.What is the difference between a Function Key Trigger and Key Function Trigger ?

Function key triggers are associated with individual SQL*FORMS function keys You can attach Key function triggers to 10 keys or key sequences that normally do not perform any SQL * FORMS operations. These keys referred as key F0 through key F9.

Q.Can you attach an lov to a field at run-time? if yes, give the build-in name.?

Yes. Set_item_proprety

Q.What are the different type of a record group?

Query record group
Static record group
Non query record group

Q.What is the built-in routine used to count the no of rows in a group?

Get_group _row_count
System Variables

Q.Give the Types of modules in a form?


Q.What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?


Q.What is a library in Forms 4.0?

A library is a collection of Pl/SQL program units, including user named procedures, functions & packages

Q.Explain the following file extension related to library?

The library pll files is a portable design file comparable to an fmb form file
The library lib file is a plat form specific, generated library file comparable to a fmx form file
The pld file is Txt format file and can be used for source controlling your library files Parameter.

Q.What is a package ? What are the advantages of packages ? What is Pragma EXECPTION_INIT ? Explain the usage ?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Q.Explain how procedures and functions are called in a PL/SQL block ?

Function is called as part of an expression.
sal := calculate_sal (‘a822′);
procedure is called as a PL/SQL statement
calculate_bonus (‘A822′);

Q.What is a cursor for loop ?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
e.g.. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;

Q.What is a stored procedure ?

A stored procedure is a sequence of statements that perform specific function.

Q.Difference between an implicit & an explicit cursor.?

only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR…IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

Q.What is the purpose of a cluster?

Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.