Oracle Interview Questions – Part 30

Q.What are the common Import/ Export problems? (for DBA )

ORA-00001: Unique constraint (…) violated – You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).
ORA-01555: Snapshot too old – Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO
ORA-01562: Failed to extend rollback segment – Create bigger rollback segments or set parameter COMMIT=Y while importing
IMP-00015: Statement failed … object already exists… – Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

Q.Why is it preferable to create a fewer no. of queries in the data model?

Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.

Q.What are the different kind of export backups?
Full back – Complete database
Incremental – Only affected tables from last incremental date/full backup date.
Cumulative backup – Only affected table from the last cumulative date/full backup date.

Q.How free extents are managed in Ver 6.0 and Ver 7.0 ?
Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the neighboring free extent in Ver 7.0

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

On-validate-field trigger fires, when the field Validation status New or changed. Post-field-trigger whenever the control leaving form the field, it will fire.

Q.What does an on-clear-block Trigger fire?
It fires just before SQL * forms the current block.

Q.List the windows event triggers available in Forms 4.0?

Q.List system variables available in forms 4.0, and not available in forms 3.0?
System Date_threshold

Q.List the built-in routine for controlling window during run-time?

Q.What are the PL/SQL Statements used in cursor processing ?

DECLARE CURSOR name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

Q.What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,… This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql statements into calls to the precompiler runtime library. The output must be compiled and linked with this library to creator an executable.

Q.What is difference between SUBSTR and INSTR?
SUBSTR returns a specified portion of a string eg SUBSTR(‘BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string. eg INSTR(‘ABC-DC-F’,’-‘,2) output 7 (2nd occurence of ‘-‘)

Q.What is OCI. What are its uses?
Oracle Call Interface is a method of accesing database from a 3GL program. Uses–No precompiler is required,PL/SQL blocks are executed like other DML statements.
The OCI library provides
–functions to parse SQL statemets
–bind input variables
–bind output variables
–execute statements
–fetch the results

Q.Difference between NO DATA FOUND and %NOTFOUND?
NO DATA FOUND is an exception raised only for the SELECT….INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

Q.Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

Q.How many types of database triggers can be specified on a table ? What are they ?

Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned Boolean value.

Q.What are the menu items that oracle forms 4.0 supports?
Plain, Check,Radio, Separator, Magic

Q.Give the equivalent term in forms 4.0 for the following. Page, Page 0?
Page – Canvas-View
Page 0 – Canvas-view null.

Q.What is the use of RECORD option in EXP command?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.

Q.List the built-in routines for the controlling canvas views during run-time?

Q.What is the use of GRANT option in EXP command?
A flag to indicate whether grants on databse objects will be exported or not. Value is ‘Y’ or ‘N’.

Q.What is a Trigger ?
A piece of logic that is executed at or triggered by a SQL *forms event.

Q.What are the different types of Package Procedure ?
1. Restricted package procedure.
2. Unrestricted package procedure.

Q.What is a OUTER JOIN?
Outer Join–Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

Q.What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.