DBMS Questions and Answers for Interview (Part-III)

31. When do we get a MUTATING ERROR ?

This happens with TRIGGERS. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of VIEWS or TEMPORARY TABLES so the database is selecting from one while updating the other.

32. How to DISABLE REFERENTIAL INTEGRITY ?

Use the DIABLE option in CREATE TABLE or ALTER TABLE or using

DISABLE { { UNIQUE (column) (column)... PRIMARY KEY | CONSTRAINT } [CASCADE] | ALL TRIGGERS;

NOTE : For diabling REFERENTIAL INTEGRITY we have to include CASCADE option.

33. How to know what all CONSTRAINTS are present in a table ?

Using the USER_CONSTRAINTS view we can get the type of constaints declared on a table.

Use ALL_CONSTRAINTS to list the constraints on all of the tables that the user have access. DBA_CONSTRAINTS lists all of the constraints in the database.

34. What is MASTER - DETAIL relationship ? Can we write a master-detail relationship programs

without using the setings at design time. If so how ?

It is an association between TWO BASE TABLE blocks - a MASTER block and a DETAIL block. The relationship between the blocks reflects a PRIMARY KEY - FOREIGN KEY relationship between the tables on which the blocks are based.

Yes. Using the SET_RELATION property.

35. What does BUFFER RECORDS option and ARRAY SIZE parameter ?

ARRAY SIZE - Specifies the minimum no. of records that get fetched each time forms goes to the database.

BUFFER RECORDS - Specifies the minimum no of records that should be placed in memory when records are fetched from the database. Even if you specify a low value of 3, the minimum per form is slightly over 300.

36. During VALIDATION WHAT CHECKS are done with respective to FIELDS / ITEMS ?

1] Data type, 2] Maximum length, 3] Fixed length, 4] Required and

5] Range Low value / Range High value.

37. What is the difference between PRIMARY KEY and UNIQUE KEY ?

The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed.

In case of PRIMARY KEY columns entry of duplicate as well as NULL value is restricted.

38. What is the DIFFERENCE between PRE-QUERY and POST-QUERY ?

PRE-QUERY fires ONLY ONCE during EXECUTE-QUERY or COUNT-QUERY processing, just before Oracle Forms constructs and issues the SELECT statement to identify rows that match the query criteria.

POST-QUERY fires each time for records placed on the blocks list of records.

39. When do you use ON-DATABASE-RECORD triigger ?

Use an ON-DATABASE-RECORD to perform an action every time a record is first marked as an INSERT or UPDATE.

This trigger fires, as soon as Oracle Forms determines thro’ validation that the record should be processed by the next post or commit as an INSERT or UPDATE

40. What are RESTRICTED PACKAGED PROCEDURES ? Why are they restricted from using ?

Any PACKAGED PROCEDURE that affects the basic functions of SQL*FORMS is a RESRICTED PACKAGED PROCEDURE. You should use restricted packaged procedure only in KEY-TRIGGERS, USER-NAMED TRIGGERS that are invoked by KEY-TRIGGERS, and ON_NEW_FIELD_INSTANCE triggers. You should not use restricted packaged procedures in any of the following types of triggers.

• On-error,On-Database-Record,On-delete,On-insert,On-Lock,
• On-Message,On-New-Record,On-Remove-record,On-Update,
• On-Validate-Field, and On-validate-Record triggers.
• Post-Change triggers.
• Pre- and Post- Field, Pre- and Post- Record, Pre- and Post-Block, Pre- and Post-Form triggers.
• Pre- and Post-Query triggers.
• Pre- and Post-Insert, Pre- and Post-Update, Pre- and Post-Delete, Pre- and Post-Commit triggers.
• User-Named triggers that are invoked by any of the above triggers.

41. What is the DIFFERENCE between EXPLICIT CURSOR & IMPLICIT CURSOR ?

IMPLICIT CURSORS are automatically opened by issuing a SELECT statement. But the EXPLICIT cursors are to be opened using OPEN, fetching is done using FETCH and closing using CLOSE.

42. What is the difference between ROWID and ROWNUM ?

ROWID is the logical address of the row, whereas ROWNUM returns the sequence no. in which the row was retrieved when first feched from a table.

43. What is the RESULT of the statement ?

SELECT EMPNO, NAME,SAL FROM EMP WHERE ROWNUM >2;

Result : 0, No rows will be selected.

44. How do you evaluate performance ?

Using SQL TRACE. It is an utility that can monitor and report on database performance when one or more queries are run against the database.

It is used to gather statistics when running the query (i.e) reports on CPU time spent on the query, the total no. of rows processed and statistics related to parsing and cache performance.

45. What will EXPLAIN PLAN give ?

It is an utility that shows how Oracle will access data for a given query. Use EXPLAIN PLAN to determine the effective way to write queries and decide whether to INDEX CERTAIN COLUMNS or TO USE CLUSTERS.

It shows :

1] The type of query processed; SELECT, INSERT,UPDATE or DELETE.

2] The cost assigned by the COST BASED OPTIMIZER if it is in use.

3] The steps that are necessary to return the data.

4] The internal operations that were performed for each step.

5] The object accessed for each step.