Behavioral Programmatic Constructs Interview Questions & Answers:

1. Explain What are the different types of PL/SQL program units that can be defined and stored in ORACLE database?

Procedures and Functions,Packages and Database Triggers.

2. Explain What are the differences between Database Trigger and Integrity constraints?

A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table. A trigger does not apply to data loaded before the definition of the trigger, therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger. A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.

3. Explain What is an Oracle Data Block?

ORACLE database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

Oracle database consists of logical storage place which is called as blocks and the bytes in a block is set through DB_BLOCK parameter in parameter file

4. Explain What is a Procedure?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

Procedure is pl/sql subprogram that can accept parameters and can be invoked form calling enviourment.Procedures are created to perform actions on an applications

5. Explain What are the uses of Oracle Rollback Segment?

Rollback Segments are used :
To generate read consistent database information during database recovery to rollback uncommitted transactions for users

6. Explain 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.

7. Explain What is a Package?

A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.

package is collection of logicaly related procedure,funtion,varialbles
wich are stored all togther.Package implemnt the concept of Object orintation Encalsulatio,Datahiding andd polymorphism disadvantage of the package when a single function or procedure is called from package it also compiles all its related obejcts and get it store in SGA.

8. Explain the uses of Database Trigger?

Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.

9. Explain What are Oracle Schema Objects?

Schema objects are the logical structures that directly refer to the databases data.
Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

10. Explain What is difference between Procedures and Functions?

A Function returns a value to the caller where as a Procedure does not.