List of all ORACLE Interview Questions

1] EXPLAIN THE DIFFERENCE BETWEEN TRIGGER AND STORED PROCEDURE.

Answer: • A stored procedure can accept parameters while a trigger cannot. • A trigger can’t return any value while stored procedures can. • A trigger is
executed automatically on some event while a stored

2] EXPLAIN ROW LEVEL AND STATEMENT LEVEL TRIGGER.

Answer: A trigger if specified FOR EACH ROW; it is fired for each of the table being affected by the triggering statement. For example if a trigger needs to
be fired when rows of a table are deleted, it will

3] WRITE A PL/SQL PROGRAM FOR A TRIGGER.

Answer: PL/SQL program for tracking operation on a emp table. Create or Replace Trigger EmpTracking Before Insert or Delete or Update on Emp For
each row Declare Begin If Inserting then

4] ADVANTAGE OF A STORED PROCEDURE OVER A DATABASE TRIGGER.

Answer: Stored procedures can accept parameters and can return values. Triggers can neither accept parameters nor return values. A Trigger is
dependent on a table and the application has no control to not fir

5] WHAT ARE CASCADING TRIGGERS?

Answer: At times when SQL statement of a trigger can fire other triggers. This results in cascading triggers. Oracle allows around 32 cascading triggers.
Cascading triggers can cause result in abnormal behavi

6] WHAT IS A JOIN? EXPLAIN TYPES OF JOIN IN ORACLE.

Answer: A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be
queries i.e. common data between 2 tables, unique da

7] EXPLAIN VARIOUS TYPES OF JOINS.

Answer: Types of joins are: Equijoins Non-equijoins self join outer join

8] WHAT IS OBJECT DATA TYPE IN ORACLE?

Answer: New/User defined objects can be created from any database built in types or by their combinations. It makes it easier to work with complex data
like images, media (audio/video). An object types is jus

9] WHAT IS COMPOSITE DATA TYPE?

Answer: A composite data type could be a record, table, nested table, varray. This is so because all of them are composed of multiple data types.
Composite data types can be used to construct complex data typ

10] DIFFERENCES BETWEEN CHAR AND NCHAR IN ORACLE.

Answer: Nchar is used to store fixed length Unicode data. It is often used to store data in different languages. CHAR on the other hand is store fixed length
character data. When data is stored using CHAR,

11] DIFFERENCES BETWEEN CHAR AND VARCHAR2 IN ORACLE.

Answer: CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance
sake Char is quit faster than Varchar2. If we have char n

12] DIFFERENCES BETWEEN DATE AND TIMESTAMP IN ORACLE

Answer: DATE in Oracle returns month, day, year, century, hours, minutes, and seconds. For more granular details, TIMESTAMP should be used.
TIMESTAMP also returns fraction of seconds that helps to identify wh

13] DEFINE CLOB AND NCLOB DATATYPES.

Answer: CLOB and NCLOB can both be used to store 4 GB of data in the database. CLOB (Character Large Object) is used specifically to store character
set data whole NCLOB (National Large Object) is specifica

14] WHAT IS THE BFILE DATATYPES?

Answer: It refers to an external binary file and its size is limited by the operating system.

15] WHAT IS VARRAYS?

Answer: VARRAY is varying array type that is typically used when the number of instances to be stored is small. It has a set of data elements and all are of
the same data type. The size of VARRAY determines t

16] WHAT ARE LOB DATATYPES?

Answer: LOB is large object byte used to store large amount of data. The following types come under LOB data types: CLOB and NCLOB can both be
used to store 4 GB of data in the database. BLOB: Binary LOB is

17] WHAT IS A CURSOR? WHAT ARE ITS TYPES?

Answer: Cursor is used to access the access the result set present in the memory. This result set contains the records returned on execution of a query.
They are of 2 types: 1. Explicit 2. Implicit

18] EXPLAIN THE ATTRIBUTES OF IMPLICIT CURSOR

Answer: a. %FOUND - True, if the SQL statement has changed any rows. b. %NOTFOUND - True, if record was not fetched successfully. c.
%ROWCOUNT - The number of rows affected by the SQL statement. d. %ISOPEN

19] EXPLAIN THE ATTRIBUTES OF EXPLICIT CURSOR.

Answer: a. %FOUND - True, if the SQL statement has changed any rows. b. %NOTFOUND - True, if record was not fetched successfully. c.
%ROWCOUNT - The number of rows affected by the SQL statement. d. %ISOPEN

20] WHAT IS THE REF CURSOR IN ORACLE?

Answer: REF_CURSOR allows returning a recordset/cursor from a Stored procedure. It is of 2 types: Strong REF_CURSOR: Returning columns with
datatype and length need to be known at compile time. Weak REF_CU

21] WHAT ARE THE DRAWBACKS OF A CURSOR?

Answer: Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query where there is just one
network roundtrip. Cursors need more I/O and temp storage

22] WHAT IS A CURSOR VARIABLE?

Answer: In case of a cursor, Oracle opens an anonymous work area that stores processing information. This area can be accessed by cursor variable
which points to this area. One must define a REF CURSOR type,

23] WHAT IS IMPLICIT CURSOR IN ORACLE?

Answer: PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor. The
developer does not explicitly declare the cursor, thus, known

24] CAN YOU PASS A PARAMETER TO A CURSOR? EXPLAIN WITH AN EXPLAIN.

Answer: A cursor can have a parameter in the IN mode. Example: The cursor below accepts a parameter of data type varchar2. cursor sample (v_key
varchar2) is select initcap(book_title) bk_title,

25] WHAT IS A PACKAGE CURSOR?

Answer: A Package that returns a Cursor type is a package cursor. Eg: Create or replace package pkg_Util is cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE; end; /*Another pa

26] EXPLAIN WHY CURSOR VARIABLES ARE EASIER TO USE THAN CURSORS.

Answer: A cursor variable is actually a pointer pointing to a queries result set. Using a cursor variable, each time a new result set is created by a query,
cursor variable can be used to point the same. This

27] WHAT IS LOCKING, ADVANTAGES OF LOCKING AND TYPES OF LOCKING IN ORACLE?

Answer: Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control
when multiple users access table to manipulate its data at

28] WHAT ARE TRANSACTION ISOLATION LEVELS SUPPORTED BY ORACLE?

Answer: Oracle supports 3 transaction isolation levels: a. Read committed (default) b. Serializable transactions c. Read only

29] EXPLAIN HOW TO VIEW EXISTING LOCKS ON THE DATABASE.

Answer: A number of data locks need to be monitored, in order to maintain a good performance level for all sessions, along with the time for which they last.
The current existing data locks are maintained in

30] EXPLAIN HOW TO LOCK AND UNLOCK A USER ACCOUNT IN ORACLE.

Answer: SQL> ALTER USER user_name ACCOUNT LOCK; SQL> ALTER USER user_name ACCOUNT UNLOCK;

31] WHAT ARE BACKGROUND PROCESSES IN ORACLE?

Answer: Oracle uses background process to increase performance. Database writer, DBWn Log Writer, LGWR Checkpoint, CKPT System Monitor, SMON
Process Monitor, PMON Archiver, ARCn

32] WHAT IS SQL*LOADER?

Answer: SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads

33] WHAT IS A SQL*LOADER CONTROL FILE?

Answer: A SQL*Loader control file contains the following specification: • Location of the input data file. • The format of the input date file. • The target table
where the data should be loaded. • The

34] EXPLAIN ORACLE MEMORY STRUCTURES.

Answer: Two memory area. System global area(SGA) Program Global Area(PGA) SGA consist memory structure such as Shared Pool Database buffer
cache Redo log buffer large Pool Java Pool
35] WHAT IS PROGRAM GLOBAL AREA (PGA)?

Answer: The Program Global Area (PGA): stores data and control information for a server process in the memory. The PGA consists of a private SQL area
and the session memory.

36] WHAT IS A SHARED POOL?

Answer: Shared pool in oracle contains cache information that collects, parses, interprets and executes SQL statements that goes against database. This
shared pool acts like a buffer for these SQL statements.

37] WHAT IS SNAPSHOT IN ORACLE?

Answer: A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data
between distributed databases.

38] WHAT IS A SYNONYM?

Answer: A synonym is an alternative name tables, views, sequences and other database objects.

39] WHAT IS A SCHEMA?

Answer: A schema is a collection of database objects. Schema objects are logical structures created by users to contain data. Schema objects include
structures like tables, views, and indexes.

40] EXPLAIN HOW TO LIST ALL INDEXES IN YOUR SCHEMA.

Answer: The list of all indexes in a schema can be obtained through the USER_INDEXES view with a SELECT statement: SELECT index_name,
table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'tablename'

41] WHAT ARE SCHEMA OBJECTS?

Answer: Schema objects are a part of the database. They include tables, views, indexes etc. they are logical structures containing or referring to actual
data.

42] WHAT IS AN ARCHIVER?

Answer: Archiving is the process of removing of old data and unused data from the main databases. This process keeps databases smaller, more
manageable and thus acquires performance gain. To archive data, you

43] WHAT IS A SEQUENCE IN ORACLE?

Answer: A sequence is a column in a table that allows a faster retrieval of data from the table because this column contains data which uniquely identifies a
row. It is the fastest way to fetch data through a

44] DIFFERENCE BETWEEN A HOT BACKUP AND A COLD BACKUP

Answer: A cold backup is taken when database is not running. This means no users should be logged and no activity in progress. Because of this there are
no changes in the data files while backup is being take

45] HOW TO RETRIEVE 5TH HIGHEST SAL FROM EMP TABLE?

Answer: SELECT DISTINCT (emp1.sal) FROM EMP emp1 WHERE &N = (SELECT COUNT (DISTINCT (emp2.sal)) FROM EMP emp2 WHERE
emp1.sal<= emp2.sal); Will work for any value if N, including 5

46] WHAT IS $FLEX$ AND $PROFILES$? WHERE ARE THEY USED?

Answer: $FLEX$ is used to get a value used in the previous value set. It is usually used to retrieve the Flex value contained in an AOL value set or object.
$PROFILES$ is used to get the value for that profil

47] HOW TO CALL A TRIGGER INSIDE A STORED PROCEDURE?

Answer: A trigger cannot be called within a stored procedure. Triggers are executed automatically as a result of DML or DDl commands

48] WHAT IS WATER MARK IN ORACLE? EXPLAIN THE SIGNIFICANCE OF HIGH WATER MARK.

Answer: WATER MARK is a divided segment of used and free blocks. Blocks which are below high WATER MARK i.e. used blocks, have at least once
contained some data. This data might have been deleted later. Oracl

49] WHAT IS AN OBJECT GROUPS?

Answer: Object group is a container for a group of objects. One can package related objects to copy or sub class them in another module.

50] DIFFERENCE BETWEEN CLUSTERING AND MIRRORING

Answer: Clustering means one than one database server configured for the same user connection. When users connect, one of the server’s responds and
connects based on availability. The user is completely ignor

51] DIFFERENCE BETWEEN PAGING AND FRAGMENTATION

Answer: Paging is a concept occurring in memory, whereas, Fragmentation occurs on disk level.

52] CAN YOU EXPLAIN HOW TO INSERT AN IMAGE IN TABLE IN ORACLE?

Answer: Insert image into a table: Create the following table: create table pics_table ( bfile_id number, bfile_desc varchar2(30), bfile_loc bfile, bfile_type
varchar2(4))

53] HOW TO FIND OUT SECOND LARGEST VALUE IN THE TABLE?

Answer: SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP WHERE SAL <> (SELECT MAX(SAL) FROM EMP))

54] DISADVANTAGE OF USER DEFINED FUNCTION IN ORACLE.

Answer: Disadvantage of UDF in Oracle: Oracle does not support calling UDFs with Boolean parameters or return types. Users must design them to return
numbers such as 0,1 or character strings such as ‘true’,’

55] EXPLAIN THE SIGNIFICANCE OF CLUSTER TABLE OR NON CLUSTER TABLE.

Answer: A Cluster provides an alternate method of storing table data. It is made up of a group of tables that share the same data i.e. same columns and
thus are often used together. Primary benefits of this a

56] WHAT ARE THE PURPOSES OF IMPORT AND EXPORT UTILITIES?

Answer: Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back. These
utilities are used: • To take backup/dump of da

57] DIFFERENCE BETWEEN ARCHIVELOG MODE AND NOARCHIVELOG MODE

Answer: Archivelog mode is a mode in which backup is taken for all the transactions that takes place so as to recover the database at any point of time.
Noarichvelog mode is in which the log files are not wri

58] WHAT ARE THE ORIGINAL EXPORT AND IMPORT UTILITIES?

Answer: SQL*Loader, External Tables

59] WHAT ARE DATA PUMP EXPORT AND IMPORT MODES?

Answer: It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.

60] WHAT ARE SQLCODE AND SQLERRM AND WHY ARE THEY IMPORTANT FOR PL/SQL DEVELOPERS?

Answer: SQLCODE: It returns the error number for the last encountered error. SQLERRM: It returns the actual error message of the last encountered error.

61] EXPLAIN USER DEFINED EXCEPTIONS IN ORACLE.

Answer: A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as
exception. They must be raised explicitly using RAISE s

62] EXPLAIN THE CONCEPTS OF EXCEPTION IN ORACLE. EXPLAIN ITS TYPE.

Answer: Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control
are then transferred to exception-handling part. There

63] HOW EXCEPTIONS ARE RAISED IN ORACLE?

Answer: Oracle exceptions are raised internally and user need not explicitly raise them. For example, when a number is attempted to be divided by ZERO,
ZERO_DIVIDE exception is raised. User defined exception

64] WHAT IS TKPROF AND HOW IS IT USED?

Answer: tkprof is used for diagnosing performance issues. It formats a trace file into a more readable format for performance analysis. It is needed because
trace file is a very complicated file to be read as

65] WHAT IS ORACLE SERVER AUTOTRACE?

Answer: It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete). It is the most basic utility to test
the performance issues.

66] EXPLAIN THE DIFFERENT TYPES OF QUERIES IN ORACLE.

Answer: Session Queries are implicitly constructed and executed by a Session based on input parameters.Input parameters are used to perform the most
common data source actions on objects. Database Queries ar

67] WHAT IS SQL*PLUS?

Answer: SQL*Plus is an interactive and batch query tool. It gets installed with every Oracle Database Server or Client installation. It has a command-line
user interface, a Windows Graphical User Interfac

68] EXPLAIN HOW TO CHANGE SQL*PLUS SYSTEM SETTINGS.

Answer: The SET command can be used to change the settings in the SQl*PLUS environment. SET AUTOCOMMIT OFF: Turns off the auto-commit feature
SET FEEDBACK OFF: Stops displaying the "27 rows selected." messa

69] WHAT ARE SQL*PLUS ENVIRONMENT VARIABLES?

Answer: The behaviour of SQL PLUS depends on some environmental variables predefined in the OS: ORACLE_HOME: This variable stores the home
directory where the Oracle client application is installed PATH: It

70] WHAT IS OUTPUT SPOOLING IN SQL*PLUS?

Answer: The spooling feature facilitates copying of all the contents of the command line SQL*Plus to a specified file. This feature is called Spooling. Syntax:
SPOOL filename: Turns on output spooling with

71] WHAT IS A SUBQUERY IN ORACLE?

Answer: When a query needs to be run which has a condition that needs to be a result of another query then, the query in the condition part of the main one
is called a sub-query. It is usually specified in th

72] WHAT IS DATA BLOCK?

Answer: Data block is the optimum level of storage. Also known as pages, each data block corresponds to a specific number of bytes. Adjacent data blocks
form an extent.

73] EXPLAIN THE DIFFERENCE BETWEEN A DATA BLOCK, AN EXTENT AND A SEGMENT.

Answer: Data blocks, extent and segments are logical units of data storage in oracle. Data block is the optimum level of storage. Also known as pages,
each data block corresponds to a specific number of byte

74] WHAT ARE THE USES OF ROLLBACK SEGMENT?

Answer: • Rollback segments undo changes when a transaction is rolled back • They also ensure that transactions leave the uncommitted changes
unnoticed. • They can be used to recover the database to a con

75] WHAT IS ROLLBACK SEGMENT IN ORACLE?

Answer: Rollback segment is an area that is used to access data of a transaction before it was committed. This segment is used when a transaction is rolled
back in order to read the old values. This data is t

76] WHAT ARE THE DIFFERENT TYPES OF SEGMENTS?

Answer: Data Segment Index Segment Rollback Segment and Temporary Segment.

77] EXPLAIN DIFFERENCE BETWEEN SQL AND PL/SQL.

Answer: SQL is Structured Query Language comprising of Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is used to define
the scheme while DDL is used to manipulate the data. Example

78] WRITE A PL/SQL PROGRAM FOR A FUNCTION RETURNING TOTAL TAX COLLECTED FROM A PARTICULAR PLACE.

Answer: PL/SQL program Create of Replace Function Tax-Amt Place varchar2, Return Number is Place_wise_tot_tax : = 0; Begin Select sum(TaxAmt)
from Tax where location = Place; Pla

79] WHAT ARE THE TYPES PL/SQL CODE BLOCKS?

Answer: Anonymous Block • It is a block of codes without a name. • It may contain a declaration part, an execution part, and exception handlers. Stored
Program Unit • It is a block of codes with a name.

80] ADVANTAGES OF PL/SQL.

Answer: Advantages of PL/SQL:- 1. PL/SQL is structured as it consists of blocks of code and hence streamlined. This makes PL/SQL highly productive. 2. It
is highly portable, has immense error handling mecha

81] WHAT IS WEBDB?

Answer: WebDB tool is a tool to develop database driven applications and web sites. It’s a user friendly interface that allows database administrator to
application development.

82] WHAT IS NESTED TABLE?

Answer: Nested tables in oracle are similar to one dimensional array except the former’s size has no upper bound and can be increased dynamically. They
are one column database tables where the rows of a neste

83] WHAT IS CLUSTERS?

Answer: Clusters in Oracle contain data of multiple tables that have the same one or more columns. All the rows from all the tables that share the same
cluster key are stored together. Example: Create a clu
84] EXPLAIN HOW TO ADD A NEW COLUMN TO AN EXISTING TABLE IN ORACLE.

Answer: Use the ALTER TABLE command to do this. ALTER TABLE employee ADD (department VARCHAR2);

85] HOW MANY TYPES OF TABLES SUPPORTED BY ORACLE? EXPLAIN THEM

Answer: Oracle supports 4 types of tables based on how data is organized in storage: Ordinary (heap-organized) table • A basic, general purpose table •
Data is stored as an unordered collection (heap) Cl

86] EXPLAIN HOW TO VIEW ALL COLUMNS IN AN EXISTING TABLE.

Answer: Use the command DESC and the table name to view the information about the columns. Eg: SQL> desc emp; Name Null? Type ------------ --

87] EXPLAIN HOW TO RECOVER A DROPPED TABLE IN ORACLE.

Answer: An oracle table that is accidentally dropped can be recovered using FLASHBACK command. When a table is dropped it stays in the recycle bin of
Oracle until it is explicitly PURGED. Such tables can be r

88] WHAT IS AN ORACLE RECYCLE BIN?

Answer: • The tables that have been dropped can be retrieved back using the reycle bin feature of Oracle. • They can be recovered back by the Flashback
Drop action. • Recycle bin can be turned on or off i

89] WHAT IS AN EXTERNAL TABLE?

Answer: A table with its data stored outside the database as an OS file is an external table. The ORACLE_LOADER and ORACLE_DATAPUMP drivers are
used to access data of external tables. External tables can be

90] DESCRIBE HOW TO LOAD DATA THROUGH EXTERNAL TABLES.

Answer: • Create an external table with columns matching data fields in the external file. • Create a similar table. • Execute INSERT INTO ... SELECT
statement to load data from the external file.

91] WHAT IS THE ROLE OF ARCHIVER [ARCN]?

Answer: ARCn is an oracle background process responsible for copying the entirely filled online redo log file to the archive log. Once these files have been
copied, they can be overwritten. The n in ARCn repr

92] STRUCTURAL DIFFERENCE BETWEEN BITMAP AND BTREE INDEX IN ORACLE.

Answer: Structural difference between bitmap and btree index Btree It is made of branch nodes and leaf nodes. Branch nodes holds prefix key value along
with the link to the leaf node. The leaf node in turn

93] CAN YOU EXPLAIN HOW TO CONVERT ORACLE TABLE DATA INTO EXCEL SHEET?

Answer: There are 2 ways to do so: 1. Simply use the migration wizard shipped with Oracle. 2. Convert Excel sheet into CSV file. Create an oracle table,
and use SQLLoad to load the CSV file into the oracle
94] WHEN SHOULD WE GO FOR HASH PARTITIONING?

Answer: Scenarios for choosing hash partitioning: • Not enough knowledge about how much data maps into a give range. • Sizes of range partition differ
quite substantially, or are difficult to balance manual

95] WHAT IS MATERIALIZED VIEW? WHAT IS A SNAPSHOT?

Answer: A materialized view is a database object that contains the results of a query. A snapshot is similar to materialized view but has lesser features than
a materialized view. Like a snapshot, a materiali

96] WHAT ARE THE ADVANTAGES OF RUNNING A DATABASE IN NO ARCHIVE LOG MODE?

Answer: 1. Less disk space is consumed 2. Causes database to freeze if disk gets full

97] WHAT ARE THE ADVANTAGES OF RUNNING A DATABASE IN ARCHIVE LOG MODE?

Answer: It makes it possible to recover database even in case of disk failure

98] WHAT IS DBA_SEGMENT IN ORACLE?

Answer: DBA_SEGMENTS tells about the space allocated for all segments in the database for storage.

99] WHAT ARE THE DATABASE OBJECTS IN ORACLE?EXPLAIN THEM

Answer: Table Composed of rows and column that stores data. View Represents subset of data from one or more tables. Sequence Auto generates primary
key value. Index Improve performance of queries.

100 EXPLAIN THE DIFFERENCE BETWEEN ROWID AND ROWNUM.

]
Answer: ROWID is a unique pseudo number assigned to a row. Rownum returns the number of rows for a resultant query. BOTH are pseudo columns not
occupying any physical space in the database. Example of Rownu

101 WHAT IS A TABLESPACE?

]
Answer: Tablespaces is a logical storage unit. It is used to group related logical structures together.

102 COMPONENTS OF LOGICAL DATABASE STRUCTURE OF ORACLE DATABASE

]
Answer: Tablespaces Database's schema objects. Tablespace: A tablespeace is a logical unit of storage of a database on which the datafile resides. It is
not visible in the system, hence logical. Each tab

103 WHAT IS THE USE OF DATA DICTIONARY IN ORACLE?

]
Answer: Data Dictionary in Oracle contains a set of tables that carry read only database information. It contains information like definition of tables, views,
clusters, synonyms etc, default values for colum

104 EXPLAIN HOW TO ASSIGN A TABLESPACE TO A USER IN ORACLE.

]
Answer: The following statement assigns already created tablespace ‘TABLESPACE_01’ as the tablespace group for user sh: ALTER USER sh
TEMPORARY TABLESPACE TABLESPACE_01;

105 WHAT ARE THE PREDEFINED TABLESPACES IN A DATABASE?

]
Answer: When you create a new database, Oracle server will create 4 required tablespaces for the new database: SYSTEM Tablespace • Every Oracle
database contains a tablespace named SYSTEM • Created automa

106 WHAT ARE ELEMENTS OF DATABASE LOGICAL LAYERS?

]
Answer: The logical layer of the database consists of the following elements: One or more tablespaces. The database schema that comprises of items such
as tables, clusters, indexes, views, stored procedur

107 WHAT IS DATA-DICTIONARY CACHE?

]
Answer: Data-Dictionary Cache keeps information about the logical and physical structure of the database. The data dictionary contains information such as
the following: User information, such as user pri

108 EXPLAIN THE CHARACTERISTICS OF DATA FILES IN ORACLE.

]
Answer: One or more data files form a logical unit of database storage called a tablespace. The size of the data file can't be changed once it is created.

109 DEFINE PRIMARY KEY AND FOREIGN KEY.

]
Answer: A column or combination of columns that identify a row of data in a table is Primary Key. A key in a table that identifies records in other table in
called a foreign key.

110 WHAT ARE CONSTRAINTS?

]
Answer: It is the rules that prevent the invalid entry into the table. They are stored in the data dictionary. They can be defined either at column level or table
level. Following are the constraints availa

111 DEFINE REFERENTIAL INTEGRITY.

]
Answer: Referential integrity in Oracle is used to establish relationships between tables through foreign keys. When one table’s primary key is taken as a
“reference” in another table, it is called as a forei

112 PURPOSE OF USING NEXTVAL WHILE CREATING THE SEQUENCE.

]
Answer: NextVal gives you the available number in the sequence asked for.

113 WHAT IS FORALL STATEMENT? EXPLAIN WITH AN EXAMPLE

]
Answer: The FORALL binds input as a collection and sends them to the SQL engine for processing.

114 WHAT IS FLASHBACK QUERY? EXPLAIN ITS USES WITH AN EXAMPLE

]
Answer: Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. Turning on flashback query:-
EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123

115 WHAT IS THE TABLESPACE IN ORACLE? PURPOSE AND SIGNIFICANCE

]
Answer: A tablespace is a logical container unit within an Oracle database. It does not exist physically on a filesystem, however, it consists of at least one
datafile which is physically located on the file

116 HOW TO USE SEQUENCE AND WHAT USE OF SEQUENCE CACHE?

]
Answer: Seqences are often used to generate autonumber fields. A sequence is an object in Oracle used to create a number sequence, often used to
create a primary key column. Syntax: CREATE SEQUENCE sequence

117 DIFFERENCE BETWEEN FORMULA COLUMN AND PLACE HOLDER.

]
Answer: Difference between formula column and place holder Formula column Used for calculation of logic in reports. It returns only one value based on
calculations needed. Place holder Global variables a

118 HOW TO CREATE PLACEHOLDER COLUMNS IN ORACLE?

]
Answer: Steps to create placeholder columns: • Click inside the container group at the position where you want the column to be placed. • Or to create a
report level column, click in the open area of the ca

119 WHAT IS AUTONOMOUS TRANSACTION?

]
Answer: Autonomous transactions have the ability to leave the context of calling transaction, then perform an independent transaction, and again return to
the calling transaction without affecting its state.

120 HOW TO CREATE LOV IN ORACLE FORMS?

]
Answer: List of Values(LOV) are used either when a selected list is too long and hence would not be appropriate for a drop down, but needs a search form
to select the value. Steps to create LOV functionality

121 WHICH SQL COMMAND TO BE USED TO GET A PRINT OUT FROM ORACLE?
]
Answer: PRINT:

122 EXPLAIN HOW TO DISABLE AND ENABLE CONSTRAINT.

]
Answer: Constraints in Oracle like Foreign keys, CHECK constraint are used to maintain data integrity. Oracle by default enables a constraint if not enabled
or disabled explicitly. Enabling a constraint: A

123 WHAT ARE THE DIFFERENT LEVELS OF AUDITING? EXPLAIN THEM.

]
Answer: Auditing collects information at run time for performance monitoring, security etc. Different levels of auditing are available depending on the extent
to which auditing is required:- • Process task

124 DEFINE STATEMENT AUDITING, PRIVILEGE AUDITING AND OBJECT AUDITING IN ORACLE.
]
Answer: Statement auditing is the auditing of the powerful system privileges without regard to specifically named objects. Privilege auditing is the auditing of
the use of powerful system privileges without

125 WHAT IS A PROFILE IN ORACLE?

]
Answer: A Profile is specific to a user that restricts access to resources. A profile can be created using the “Create profile” command. It is extremely useful
in large organizations where large numbers of us

126 WHAT DYNAMIC DATA REPLICATION?

]
Answer: Dynamic data replication is the way in which updating or inserting records in remote database through database triggers.

127 WHAT IS TWO-PHASE COMMIT?

]
Answer: A distributed transaction can involve multiple servers. Two-Phase commit protocol ensures that all database servers participating in the transaction
either commit all statements or roll back all state

128 EXPLAIN HOW TO START A NEW TRANSACTION IN ORACLE.

]
Answer: An oracle transaction is a set of SQL statements written to perform a task. A transaction begins with the first SQL statement and ends when
COMMITTED or ROLLED BACK. Example: update emp_salary

129 EXPLAIN HOW TO END THE CURRENT TRANSACTION IN ORACLE.

]
Answer: Following commands could be run to explicitly end the current transaction: • COMMIT • ROLLBACK Following commands implicitly end the current
transaction: • A DDL statement • Disconnection of a

130 EXPLAIN HOW SAVE POINT WORKS.

]
Answer: Save points in Oracle can be used for partial rollbacks. For instance we write few SQL statements and don’t want all of them to be committed. We
can insert a save point in between and roll back change

131 DELETE VS TRUNCATE VS DROP

]
Answer: The DELETE command is used to remove some or all rows from a table. After performing a DELETE operation you need to COMMIT or
ROLLBACK the transaction to make the change permanent or to undo it. It ca

132 WHAT IS A READ WRITE TRANSACTION IN ORACLE?

]
Answer: The read consistency is set at the statement level in a READ WRITE transaction. A logical snapshot of the database is created at the beginning of
the execution of each statement and released at the

133 WHAT ARE THE SYSTEM PREDEFINED USER ROLES?

]
Answer: Oracle 10g XE comes with 3 predefined roles: CONNECT This role enables a user to connect to the database. Any user or application that needs
database access should be granted this role. RESOURCE

134 EXPLAIN THE PURPOSE OF ROLLBACK AND COMMIT STATEMENTS.

]
Answer: ROLLBACK: Rollback is used to rollback or undo all the work done in the current transaction. Example: The example below undoes all changes
committed until the save point. ROLLBACK TO SAVEPOINT sav

135 WHAT ARE INTERNAL USER ACCOUNTS IN ORACLE?

]
Answer: There are a few predefined accounts set by the System in Oracle. Oracle 10g XE comes with a number of internal accounts: SYSTEM This is the
user account that you log in with to perform all administ

136 DESCRIBE HOW TO REVOKE CREATE SESSION PRIVILEGE FROM A USER.

]
Answer: Create session privilege allows user to connect to the database. If it needs to be revoked, following command can be typed: REVOKE CREATE
SESSION FROM user_name

137 WHAT IS SQL*LOADER PARAMETERS AND WHERE DO WE USE IT?

]
Answer: SQL Loader is used for the following: • Load data across a network. • Load data from multiple data files during a single load session • Load data
into multiple tables during a single load session

138 EXPLAIN HOW TO SEE THE REPORT OUTPUT IN EXCEL SHEET IN ORACLE APPLICATIONS.
]
Answer: It is a two step process: 1. Export Oracle Report to text file 2. Import Text file into Excel

139 CAN WE RESTORE A TABLE THAT ACCIDENTALLY DROPPED? HOW?

]
Answer: An accidently dropped table can only be recovered from the backup. It is easier to recover tables in Oracle 10g because it is more or less similar to
recovering files from windows recycle bin.

140 WHAT IS THE USE OF DATA LINK IN REPORTS?

]
Answer: Data links are used to establish parent-child relationships between queries and groups via column matching.

141 WHAT ARE THE TYPES OF TRIGGERS AVAILABLE IN ORACLE REPORTS?

]
Answer: Types of triggers in Oracle reports: - 1 before parameter form 2 after parameter form 3 before report 4 between pages 5 after report

142 DESCRIBE THE PURPOSE OF VIEW IN ORACLE

]
Answer: The purpose of views is defined below. Views hide data complexity. Views add security by restricting access to the columns of a table. Views
simplify queries for the user. It is way to isolate ap

143 WHAT IS A DYNAMIC PERFORMANCE VIEW IN ORACLE?

]
Answer: • The dynamic performance views are the views that get continuously updated even while the database is open or in use. • Oracle contains a set of
underlying views that are maintained by the database

144 WHAT ARE GROUP FUNCTIONS IN ORACLE?

]
Answer: COUNT(): This function returns the number of rows in the group. A column name or ‘*’ may be passed as a parameter. MIN(column_name): This
ffunction returns the minimum value of the expression evalu

145 FUNCTION VS PROCEDURE

]
Answer: Functions are typically used to return table variables. Stored procedures cant return table variables however, can create tables. A procedure may
or may not return multiple values. A function cannot

146 WHAT IS A UNION, INTERSECT, MINUS?

]
Answer: UNION Union operator is used to return all rows from multiple tables and eliminate duplicate rows. The number of columns and the datatypes of
the columns must be identical in all the SELECT statement

147 EXPLAIN THE CHARACTERISTICS OF FUNCTION OBJECT IN ORACLE.

]
Answer: A function object in Oracle is used to perform a set of repetitive tasks. A function can be either user defined or in built. A function object can take
three parameters: - IN, OUT and INOUT. Depending

148 WHAT ARE THE TRIGGERS ASSOCIATED WITH IMAGE ITEMS? EXPLAIN THEM
]
Answer: There are 2 triggers associated with image items: • When-Image-Activated: Triggered when image is double clicked. • When-Image-Pressed:
Triggered when an image is selected or deselected.

149 EXPLAIN HOW TO VIEW THE STATUS OF THE ROLLBACK SEGMENT IN ORACLE.
]
Answer: SELECT segment_name, status FROM sys.dba_rollback_segs;

150 WHAT IS THE USE OF NOARCHIEVELOG PARAMETER IN ORACLE DATABASE?

]
Answer: On media failures while the database in NOARCHIVELOG mode, only database to the point of the most recent full database backup can be
restored.

151 WHEN DO YOU GET A .PLL EXTENSION IN ORACLE? EXPLAIN ITS IMPORTANCE
]
Answer: .PLL extension is created when we save a library module. It contains both source code and platform specific complied executable code.

152 WHAT IS THE USE OF SYSTEM.EFFECTIVE.DATE VARIABLE IN ORACLE?

]
Answer: It represents the effective database date.

153 USE OF AN INTEGRITY CONSTRAINT IS BETTER TO VALIDATE DATA. EXPLAIN

]
Answer: Use of an integrity constraint is better to validate data because it prevents invalid data entry at the basic level into the database tables.

154 EXPLAIN THE FUNCTION OF OPTIMIZER IN ORACLE.

]
Answer: The optimizer determines the most efficient way to execute a SQL statement based on the kind of data in the table and the statements fired to fetch
that data like indexes, full table scans, loops, joi

155 WHAT IS MEANT BY RECURSIVE HINTS IN ORACLE?

]
Answer: Number of times a dictionary table is repeatedly called by various processes is known as recursive hint. It occurs because of the small size of data
dictionary cache.

156 WHAT ARE THE LIMITATIONS OF A CHECK CONSTRAINT?

]
Answer: The limitation of CHECK is that the condition must be a Boolean expression evaluated using the values in the row being inserted or updated and
can't contain sub queries.

157 EXPLAIN THE USE OF ROWS OPTION IN IMP COMMAND.

]
Answer: It indicates whether or not the table rows should be imported.

158 EXPLAIN THE USE OF INDEXES OPTION IN IMP COMMAND.

]
Answer: It determines whether indexes are imported.

159 IT DETERMINES WHETHER INDEXES ARE IMPORTED.

]
Answer: GRANT specifies to import object grants.

160 EXPLAIN THE USE OF IGNORE OPTION IN IMP COMMAND.

]
Answer: IGNORE defines how object creation errors should be handled.

161 EXPLAIN THE USE OF SHOW OPTION IN IMP COMMAND.

]
Answer: When the value of show=y, the DDL within the export file is displayed.

162 WHAT IS THE USE OF FILE OPTION IN IMP COMMAND?

]
Answer: FILE param defines the name of the export file to import. Multiple files can be listed, separated by commas.

163 EXPLAIN THE USE OF LOG OPTION IN EXP COMMAND.

]
Answer: LOG specifies the log file to write messages.

164 WHAT IS HOT BACKUP AND HOW IT CAN BE TAKEN?

]
Answer: Hot backup is taking backup of archived log files when database is open. He ARCHIVELOG switch should be enabled for this to happen. Hot
backup happens while the database is still being accessed by use
165 WHAT ARE THE DIFFERENT KIND OF EXPORT BACKUPS?
]
Answer: Following are the different kinds of export backups: • Full/Complete backup: Backup of the whole database • Incremental backup: Only backup the
data that has been modified since last incremental bac

166 DIFFERENCE BETWEEN PRE-SELECT AND PRE-QUERY

]
Answer: Pre-select This fires during the execute and count query processing after an oracle form builds the select statement to be executed, but before it’s
execution Pre-query This fires before an oracle

167 DIFFERENCE BETWEEN OPEN_FORM AND CALL_FORM IN ORACLE.

]
Answer: CALL_FORM: This runs a specified form while keeping the parent form active. This allows moving between the forms. Oracle forms run the new
form with Run form preferences based on the parent form. When

168 WHAT ARE THE DIFFERENT TYPES OF RECORD GROUPS IN ORACLE? EXPLAIN EACH OF THEM
]
Answer: Record group is an internal oracle forms data structure having a similar column-row structure and relationship as a database table. They are logical
groups and never displayed to the user as such. V

169 WHAT IS A TRACE FILE AND HOW IS IT CREATED IN ORACLE?

]
Answer: Trace files are files used to store details of exceptions thrown by Oracle background processes i.e. dbwr, lgwr, pmon, smon etc. They are usually
created for diagnostic dumps as well and help in debug

170 EXPLAIN THE USE OF ONLINE REDO LOG FILES IN ORACLE.

]
Answer: Every Oracle database has a redo log, which records all changes that have been made in the data files. These files then enable us to replay the
SQL Statements. Oracle writes all the changes to the red

171 EXPLAIN ENABLE NOVALIDATE CONSTRAINT.

]
Answer: When a constraint has ENABLE NOVALIDATE state, all subsequent statements are checked for conformity to this constraint. A Table with this
constraint can contain invalid data, however, it does not allo

172 EXPLAIN ABOUT DATA DICTIONARY VIEWS WITH PREFIX USER_.

]
Answer: Data dictionary views with prefix USER_ : • Refer to users private environment in database i.e. including schema objects created by users, grants
made by user, etc • Display rows which are pertinent

173 HOW TO CREATE LOV DYNAMICALLY AT RUNTIME & ATTACH TO TEXT FIELD?
]
Answer: Steps to create a dynamic LOV: 1. Create a record group, eg: RG 2. Create RG Sql query as Select col1,col2,col3 from dual; Keep in mind to
adjust data types accordingly 3. Create an LOV and attach

174 HOW CAN WE FORCE THE DATABASE TO USE THE USER SPECIFIED ROLLBACK SEGMENT?
]
Answer: We can do so by using the following SQL statement SET TRANSACTION USE ROLLBACK SEGMENT User_Rollback_Segment_Name
175 EXPLAIN THE USE OF CONSISTENT OPTION IN EXP COMMAND.
]
Answer: It specifies the read only statement for export to ensure data consistency.

176 EXPLAIN THE USE OF ANALYSE OPTION IN EXP COMMAND.

]
Answer: It is a flag to indicate if the statistical information about the exported objects should be written to export dump file or not.

177 EXPLAIN THE USE OF PARFILE OPTION IN EXP COMMAND.

]
Answer: It specifies the file that contains the export parameters.

178 EXPLAIN THE USE OF PARFILE OPTION IN EXP COMMAND.

]
Answer: It specifies the file that contains the export parameters.

179 EXPLAIN THE USE OF RECORD OPTION IN EXP COMMAND.

]
Answer: It is the flag that indicates if a record will be stored in data dictionary tables recording the export.

180 EXPLAIN THE USE OF INCTYPE OPTION IN EXP COMMAND.

]
Answer: It specifies the type of export to be performed. It can be either COMPLETE, CUMULATIVE, INCREMENTAL.

181 EXPLAIN THE USE OF RECORD LENGTH OPTION IN EXP COMMAND.

]
Answer: Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk.

182 EXPLAIN THE USE OF TABLES OPTION IN EXP COMMAND.

]
Answer: Indicates that the type of export is table-mode and lists the tables to be exported.

183 EXPLAIN THE USE OF OWNER OPTION IN EXP COMMAND.

]
Answer: It tells that only the owner’s objects will be exported.

184 EXPLAIN THE USE OF FULL OPTION IN EXP COMMAND.

]
Answer: It tells that the entire database is to be exported.

185 EXPLAIN THE USE OF CONSTRAINTS OPTION IN EXP COMMAND.

]
Answer: It specifies whether table constraints should be exported with table data.
186 EXPLAIN THE USE OF ROWS OPTION IN EXP COMMAND.
]
Answer: It is the condition to decide if the table rows should be exported or not.

187 EXPLAIN THE USE OF INDEXES OPTION IN EXP COMMAND.

]
Answer: It determines whether index definitions are exported.

188 EXPLAIN THE USE OF GRANT OPTION IN EXP COMMAND.

]
Answer: It specifies the object grants to export.

189 EXPLAIN THE USE OF COMPRESS OPTION IN EXP COMMAND.

]
Answer: When “Y”, export will mark the table to be loaded as one extent for the import utility. If “N”, the current storage options defined for the table will be
used.

190 EXPLAIN THE USE OF FILE OPTION IN EXP COMMAND.

]
Answer: File parameter takes the name of the export file. Multiple files can be listed, separated by commas.

191 WHAT ARE THE KINDS OF ROLES IN ORACLE?

]
Answer: Following are the kinds of roles available in oracle: Connect This role allows access to the table belonging to other users. It allows using Select,
Insert, Update and Delete command. Resource

192 WHAT ARE THE PRIVILEGE TYPES?

]
Answer: The privileges can be of system or object level. SYSTEM level privileges can be assigned by the DBA and the object level privileges are assigned
by the owner of the particular object.

193 EXPLAIN HOW TO GRANT DML PRIVILEGE ON A TABLE.

]
Answer: Privilege in Oracle is used to allow or give rights to a particular user to execute a SQL query. Priveleges in oracle are granted using the GRANT
command. Example: GRANT select ON table_name TO use

194 WHAT IS THE PURPOSE OF INDEXES?

]
Answer: An index is typically used to retrieve records of a table at a faster rate. This is because an index contains value of the indexed column. Example:
The example below creates an index student_idx in

195 WHAT IS AN INITIALIZATION PARAMETER FILE IN ORACLE?

]
Answer: The initialization parameter file contains a list of initialization parameters. It should be written in the client's default character set. Oracle distribution
medium provides sample initializatio

196 WHAT ARE LAYERS IN ORACLE ARCHITECTURE?

]
Answer: Oracle Application Development Framework has a MVC(Model View Controller) architecture. There are three layers: • Model- Handles interaction
with data sources. Model is based on JSR-227. • View- Han

197 WHAT IS A STATIC DATA DICTIONARY IN ORACLE?

]
Answer: The information in data dictionary can be accessed through data dictionary views as they are not directly accessible. Many data dictionary tables
have three corresponding views: • ALL_ view: In thi

198 WHAT IS A DATABASE BUFFER CACHE?

]
Answer: Database buffer cache is a component of SGA. It has the responsibility to cache most recent accessed data. It keeps the transaction in the buffer
cache till it is written on the disk.

199 NAME THE COMPONENTS OF PHYSICAL DATABASE STRUCTURE OF ORACLE DATABASE. EXPLAIN THEM
]
Answer: Datafiles Redo log files Control files Datafiles, Redo log files and Control files are the mandatory components of a physical database structure.
Datafile: This component stores the actual data.

200 WHAT IS PRAGMA RESTRICT_REFERENCE IN ORACLE 9I? WHEN DO WE USE IT?

]
Answer: Pragma restrict_reference is used to check for violations of any rules applied, at compile time. It’s used in functions to obey some purity check rules
to control side effects. This particular pragma

201 WHY CAN'T WE ASSIGN NOT NULL CONSTRAINT AS TABLE LEVEL CONSTRAINT IN ORACLE?
]
Answer: Not NULL is a column level constraint to ensure that any value in that column is not null, hence can’t be used as a table level constraint. One can
however use it on multiple columns as per the need.

202 EXPLAIN THE USE OF BETWEEN PAGE TRIGGERS IN REPORTS.

]
Answer: The between-page trigger is used for creating conditional formatting, eg: retrieving information to put in headers or labels that cannot be part of the
main query. It is often used for reports to be v

203 HOW TO DELETE ALL DUPLICATE RECORDS FROM A TABLE USING SUBQUERY?
]
Answer: Query: DELETE from table1 where rowid not in (select MAX(rowid) from table1 GROUP BY column_name); here, column_name should be the
column having duplicate values.

204 HOW MANY TYPES OF TRIGGER CAN BE USED IN A TABLE AT A TIME? WHAT ARE THEY?
]
Answer: We can use maximum of 12 triggers on a table. • BEFORE INSERT • AFTER INSERT • BEFORE UPDATE • AFTER UPDATE • BEFORE DELETE
• AFTER DELETE You can define a trigger • for each row • for each s

205 WHAT IS PARTITIONED TABLE? WHAT ARE ITS TYPES? EXPLAIN ITS PURPOSE AND HOW TO CREATE.
]
Answer: Partitioning allows decomposing large tables and indexes into smaller manageable units called partitions. Queries and DML statement don’t need
to be modified to work with them. DDL statements can acce
206 HOW THE SMON PROCESS IS USED TO WRITE INTO LOG FILES?
]
Answer: The Shared Global Area is where SMON process runs. Once the redo log buffer is full then SMON with the help of LOGWRITER (LGWR) writes
into the log files.

207 WHAT ARE THE TYPES OF CALCULATED COLUMNS AVAILABLE? EXPLAIN THEM
]
Answer: In forms there are 2 types of calculated columns 1. formula column 2. summary column

List of all SQL IN ORACLE Interview Questions

1] FIND OUT NTH HIGHEST SALARY FROM EMP TABLE

Answer: SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); For Eg:-
Enter value for n: 2 SAL --------- 3700

2] WHICH IS MORE FASTER - IN OR EXISTS?

Answer: EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

3] WHET ARE THE DIFFERENCE BETWEEN PRIMARY KEY AND FOREIGN KEY?

Answer: 1)Primary key is unique key but foriegn key always refers to primary key. 2) Primary keys enforce entity integrity by uniquely identifying entity
instances. Foreign keys enforce. 3)Primary k

4] WHAT IS THE MAXIMUM BUFFER SIZE THAT CAN BE SPECIFIED USING THE DBMS_OUTPUT.ENABLE FUNCTION?

Answer: The buffer size limit is 1000000 and 32767 bytes per line.

5] WHAT IS AN UTL_FILE.WHAT ARE DIFFERENT PROCEDURES AND FUNCTIONS ASSOCIATED WITH IT?

Answer: UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL
and 5 procedures to output data to a file PUT, PUT_LINE,

6] CAN THE DEFAULT VALUES BE ASSIGNED TO ACTUAL PARAMETERS?

Answer: yes you can assign default value to an actual parameter.. If the actual parameter holds NULL value then the DEFAULT Value will be assigned.

7] WHAT IS DIFFERENCE BETWEEN A FORMAL AND AN ACTUAL PARAMETER?

Answer: The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual
parameters contain the values that are passed to a pro

8] DIFFERENCE BETWEEN AN IMPLICIT & AN EXPLICIT CURSOR.

Answer: PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return
more than one row you must declare an explicit curs

9] WHAT IS DIFFERENCE BETWEEN SUBSTR AND INSTR?

Answer: 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 occ
10 WHAT IS DIFFERENCE BETWEEN SQL AND SQL*PLUS?
]
Answer: SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to
type SQL commands to be executed directly against an Oracle d

11 WHAT ARE VARIOUS JOINS USED WHILE WRITING SUBQUERIES?

]
Answer: Self join-Its a join foreign key of a table references the same table. Outer Join--Its a join condition used where One can query all the rows of one of
the tables in the join condition even though the

12 DIFFERENCE BETWEEN PROCEDURE AND FUNCTION.

]
Answer: Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with
parameter. A procedure all is a PL/SQL statement by itself, while

13 WHICH DATATYPE IS USED FOR STORING GRAPHICS AND IMAGES?

]
Answer: LONG RAW data type is used for storing BLOB's (binary large objects).

14 WHAT SHOULD BE THE RETURN TYPE FOR A CURSOR VARIABLE.CAN WE USE A SCALAR DATA TYPE AS RETURN TYPE?
]
Answer: LONG RAW data type is used for storing BLOB's (binary large objects).

15 WHAT IS THE PURPOSE OF A CLUSTER?

]
Answer: 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

16 DIFFERENCE BETWEEN DATABASE TRIGGERS AND FORM TRIGGERS?

]
Answer: -Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or
navigates between fields on the screen -Can be row level or sta

17 DISPLAY THE NUMBER VALUE IN WORDS?

]
Answer: SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp; the output like, SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP')) ---------
----------------------------------------------------- 800 eight hund

18 HOW WILL YOU DELETE DUPLICATING ROWS FROM A BASE TABLE?

]
Answer: delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or delete
duplicate_values_field_name dv from table_name ta where rowid <(select min(row

19 DIFFERENTIATE BETWEEN TRUNCATE AND DELETE

]
Answer: TRUNCATE deletes much faster than DELETE TRUNCATE DELETE It is a DDL statement It is a DML statement It is a one way trip, cannot
ROLLBACK One can Rollback Doesn't have selective features (

20 CAN A PRIMARY KEY CONTAIN MORE THAN ONE COLUMNS?

]
Answer: primary key not allow null values, unique key allow only one null value. In table only one primary is allowed, unique key as many as it can allow.

21 WHAT IS DIFFERENCE BETWEEN UNIQUE AND PRIMARY KEY CONSTRAINTS?

]
Answer: A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically
define NOT NULL, whereas a column that compose a UNIQUE is not autom

22 WHAT ARE VARIOUS PRIVILEGES THAT A USER CAN GRANT TO ANOTHER USER?
]
Answer: -SELECT -CONNECT -RESOURCES

23 DIFFERENCE BETWEEN NO DATA FOUND AND %NOTFOUND

]
Answer: NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the query does not match any rows.
When the where clause of the explicit cursor does not match any

24 THERE IS A % SIGN IN ONE FIELD OF A COLUMN. WHAT WILL BE THE QUERY TO FIND IT?
]
Answer: '' Should be used before '%'.

25 WHAT IS DIFFERENCE BETWEEN RENAME AND ALIAS?

]
Answer: 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.

26 DISPLAY ODD/ EVEN NUMBER OF RECORDS

]
Answer: Odd number of records: select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); Output:- 1 3 5 Even number of records:
select * from emp where (rowid,0) in (select rowid, mo

27 TABLE NAME STUD, A MARK 23,B MARK 87,C MARK 75,D MARK 34; WRITE THE QUERY TO FIND 2ND HIGHEST MARK IN STUD TABLE?
]
Answer: To find the nth big earner: select mark from stud a where n=select count(distinct(mark)) from stud b where b.mark >= a.mark)

28 CAN YOU USE A COMMIT STATEMENT WITHIN A DATABASE TRIGGER?

]
Answer: no,we can't use commit statement in the trigger,we are using trigger for checking purpose. within table we can add constraint or trigger.purpose of
both is same.

29 WHAT IS A CURSOR?
]
Answer: 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 us

30 HOW DO YOU FIND THE NUMBER OF ROWS IN A TABLE ?

]
Answer: A bad answer is count them (SELECT COUNT(*) FROM table_name) A good answer is :- 'By generating SQL to ANALYZE TABLE table_name
COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLE

31 HOW YOU WILL AVOID YOUR QUERY FROM USING INDEXES?

]
Answer: SELECT * FROM emp Where emp_no+' '=12345; i.e you have to concatenate the column name with space within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234

32 WHAT IS A OUTER JOIN?

]
Answer: 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.
33 WHAT ARE ORACLE PRECOMPILERS?
]
Answer: 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,...

34 WHAT IS OCI. WHAT ARE ITS USES?

]
Answer: 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

35 WHAT IS A PSEUDO COLUMN. GIVE SOME EXAMPLES?

]
Answer: It is a column that is not an actual column in the table. eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.

36 WHAT ARE CURSOR ATTRIBUTES?

]
Answer: -%ROWCOUNT -%NOTFOUND -%FOUND -%ISOPEN

37 WHAT ARE DIFFERENT ORACLE DATABASE OBJECTS?

]
Answer: TABLES VIEWS, INDEXES, SYNONYMS, SEQUENCES, TIGGERS, PACKAGES, PROCEDURE, FUNCTION,

38 WHEN I HAVE 6 VALUES FOR ANY COLUMN, IN SELECT STATEMENT I GET DISPLAY OF 6 ROWS SELECTED WHILE IF I HAVE 5 VALUES FOR
] ONE COLUMN I DON'T GET DISPLAY OF 5 ROWS SELECTED, HOW TO GET THAT DISPLAY?

Answer: Even though you have 5 values for one column you won't get display of "5 rows selected" because by default the empty value taken as 'null' into the
table. So the row contains 'null' value is also cons

39 DISPLAY THE RECORDS BETWEEN TWO RANGE?

]
Answer: select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=&upto minus select rowid from emp where
rownum<&Start);

40 WHAT WHERE CURRENT OF CLAUSE DOES IN A CURSOR?

]
Answer: LOOP SELECT num_credits INTO v_numcredits FROM classes WHERE dept=123 and course=101; UPDATE students SET
current_credits=current_credits+v_numcredits WHERE CURRENT OF X; END LOOP COMMIT; END;

41 HOW YOU WILL AVOID DUPLICATING RECORDS IN A QUERY?

]
Answer: By using DISTINCT]

42 WHAT IS A VIEW?
]
Answer: A view is a virtual table. it is also one of the database object. two types: Resonable view updatable view

43 CAN CURSOR VARIABLES BE STORED IN PL/SQL TABLES.IF YES HOW. IF NOT WHY?
]
Answer: No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.

44 WHAT ARE DIFFERENT MODES OF PARAMETERS USED IN FUNCTIONS AND PROCEDURES?

]
Answer: -IN -OUT -INOUT

45 MINVALUE.SQL SELECT THE NTH LOWEST VALUE FROM A TABLE

]
Answer: select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level; Example: Given a table
called emp with the following columns: -- id numb

46 WHEN DO YOU USE WHERE CLAUSE AND WHEN DO YOU USE HAVING CLAUSE?
]
Answer: HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause. The WHERE clause is
used when you want to specify a condition for columns, sing

47 WHAT ARE VARIOUS CONSTRAINTS USED IN SQL?

]
Answer: - PRIMARY & FOREIGN KEY - NOT NULL - CHECK - UNIQUE - DEFAULT

48 CAN A FUNCTION TAKE OUT PARAMETERS. IF NOT WHY?

]
Answer: No. It is not possible that a function can take a OUT parameter because a function always assign the return value to a variable named after the
function name internally.If we use OUT parameter then it

49 HOW YOU OPEN AND CLOSE A CURSOR VARIABLE.WHY IT IS REQUIRED?

]
Answer: 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 f

50 WHAT A SELECT FOR UPDATE CURSOR REPRESENT.

]
Answer: 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 f

51 WHAT IS SYNTAX FOR DROPPING A PROCEDURE AND A FUNCTION .ARE THESE OPERATIONS POSSIBLE?
]
Answer: Drop Procedure procedure_name Drop Function function_name

52 HOW YOU WERE PASSING CURSOR VARIABLES IN PL/SQL 2.2?

]
Answer: In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or
OCI with version 2.2, the only means of passing a cu

53 WHAT IS USE OF A CURSOR VARIABLE? HOW IT IS DEFINED?

]
Answer: A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be
associated with one run time query. A cursor variable is

54 WHAT IS A CURSOR FOR LOOP?

]
Answer: Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.

55 MAXVALUE.SQL SELECT THE NTH HIGHEST VALUE FROM A TABLE

]
Answer: select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level; Example: Given a table
called emp with the following columns: -- id numb

56 SUPPOSE A CUSTOMER TABLE IS HAVING DIFFERENT COLUMNS LIKE CUSTOMER NO, PAYMENTS.WHAT WILL BE THE QUERY TO SELECT
] TOP THREE MAX PAYMENTS?

Answer: SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment <= C2.payment)

57 THERE IS A STRING 120000 12 0 .125 , HOW YOU WILL FIND THE POSITION OF THE DECIMAL PLACE?
]
Answer: INSTR('120000 12 0 .125',1,'.') output 13

List of all GENERAL ORACLE Interview Questions

1] DIFFERENCE BETWEEN DBMS & RDBMS?

Answer: ***Source: wiki.answers.com*** DBMS stands for Database Management System which is a general term for a set of software dedicated to
controlling the storage of data. RDMBS stand for Relational

2] HOW CAN I GET 5TH HIGHEST SAL. FROM EMPLOYEE TAB?

Answer: Select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max (sal) from emp where sal<(select max (sal) from emp
where sal<(select max(sal) from emp))));

3] POPULATE_GROUP (FUNCTION) POPULATE_GROUP_WITH_QUERY(FUNCTION) SET_GROUP_CHAR_CELL(PROCEDURE)

SET_GROUP_DATE_CELL(PROCEDURE) SET_GROUP_NUMBER_CELL(PROCEDURE)

Answer: oracle programming why are using that which for use

4] WHAT ARE DIFFERENCE BETWEEN POST DATABASE COMMIT AND POST-FORM COMMIT?

Answer: Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger
fires after inserts, updates and deletes have been posted to

5] WHY IS IT PREFERABLE TO CREATE A FEWER NO. OF QUERIES IN THE DATA MODEL?

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

6] USE THE ADD_GROUP_COLUMN FUNCTION TO ADD A COLUMN TO A RECORD GROUP THAT WAS CREATED AT DESIGN TIME. I) TRUE
II)FALSE

Answer: False.

7] WHAT ARE THE VARIOUS METHODS OF PERFORMING A CALCULATION IN A REPORT?

Answer: 1. Perform the calculation in the SQL statements itself.2. Use a calculated / summary column in the data model.

8] WHAT ARE THE BUILT-INS USED FOR PROCESSING ROWS?

Answer: Get_group_row_count(function) Get_group_selection_count(function) Get_group_selection(function) Reset_group_selection(procedure)

Set_group_selection(procedure) Unset_group_selection(procedure)

9] WHAT ARE THE TWO WAYS BY WHICH DATA CAN BE GENERATED FOR A PARAMETERS LIST OF VALUES?

Answer: 1. Using static values. 2. Writing select statement.

10] WHAT ARE THE BUILT-INS USED FOR SENDING PARAMETERS TO FORMS?

Answer: You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

11] CAN YOU HAVE MORE THAN ONE CONTENT CANVAS VIEW ATTACHED WITH A WINDOW?

Answer: Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated
content canvas view. At run time only one of the content canvas

12] WHAT IS NEW_FORM BUILT-IN?

Answer: When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form
calling new form completely replace the first with the sec

13] WHAT ARE THE TYPES OF CALCULATED COLUMNS AVAILABLE?

Answer: Summary, Formula, Placeholder column.

14] WHAT IS A STATIC RECORD GROUP?

Answer: A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at
runtime.

15] IS THE AFTER REPORT TRIGGER FIRED IF THE REPORT EXECUTION FAILS?

Answer: Yes.

16] WHAT IS THE "LOV OF VALIDATION" PROPERTY OF AN ITEM? WHAT IS THE USE OF IT?

Answer: When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the
LOV. Whenever the validation event occurs. If the v

17] WHAT IS A TIMER?

Answer: Timer is an "internal time clock" that you can programmatically create to perform an action each time the timer expires.

18] WHEN DO YOU USE DATA PARAMETER TYPE?

Answer: When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data
parameters are used to pass data to produts invoked with th

19] WHAT IS RELATION BETWEEN THE WINDOW AND CANVAS VIEWS?

Answer: Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate
objects (boxes, lines, images etc.,) that operators inter

20] HOW IS LINK TOOL OPERATION DIFFERENT BET. REPORTS 2 & 2.5?

Answer: In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field
is selected and the link tool is then used t

21] WHAT IS SYNCHRONIZING?

Answer: It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal
representation of the screen.

22] WHAT IS A DIFFERENCE BETWEEN PRE-SELECT AND PRE-QUERY?

Answer: Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the
statement is actually issued. The pre-query trigger fires just

23] WHAT ARE THE TWO PHASES OF BLOCK COORDINATION?

Answer: There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally
to the detail block and flushes the obsolete detail rec

24] HOW IS IT POSSIBLE TO SELECT GENERATE A SELECT SET FOR THE QUERY IN THE QUERY PROPERTY SHEET?

Answer: By using the tables/columns button and then specifying the table and the column names.

25] WHAT ARE VISUAL ATTRIBUTES?

Answer: Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.

26] WHAT IS TRIGGER ASSOCIATED WITH THE TIMER?

Answer: When-timer-expired.

27] WHAT IS THE USE OF PLACE HOLDER COLUMN?

Answer: A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to
appear.

28] HOW IS POSSIBLE TO RESTRICT THE USER TO A LIST OF VALUES WHILE ENTERING VALUES FOR PARAMETERS?

Answer: By setting the Restrict To List property to true in the parameter property sheet.

29] WHAT IS A QUERY RECORD GROUP?

Answer: A query record group is a record group that has an associated SELECT statement. The columns in a query record group derive their default
names, data types, had lengths from the database columns refere

30] HOW MANY WINDOWS IN A FORM CAN HAVE CONSOLE?

Answer: Only one window in a form can display the console, and you cannot change the console assignment at runtime.
31] WHAT IS THE DIFFERENCE BETWEEN SHOW_EDITOR AND EDIT_TEXTITEM?

Answer: Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the
edit_textitem built-in needs the input focus to be in the

32] WHAT IS THE MAXIMUM NO OF CHARS THE PARAMETER CAN STORE?

Answer: The maximum no of chars the parameter can store is only valid for char parameters, which can be upto 64K. No parameters default to 23Bytes and
Date parameter default to 7Bytes

33] WHAT IS THE DIFF. BET. SETTING UP OF PARAMETERS IN REPORTS 2.0 REPORTS2.5?

Answer: LOVs can be attached to parameters in the reports 2.5 parameter form.

34] WHAT IS THE USE OF HIDDEN COLUMN?

Answer: A hidden column is used to when a column has to embed into boilerplate text.

35] WHAT ARE THE BUILT-INS THAT ARE USED TO ATTACH AN LOV PROGRAMMATICALLY TO AN ITEM?

Answer: set_item_property get_item_property (by setting the LOV_NAME property)

36] EXPLAIN ABOUT HORIZONTAL, VERTICAL TOOL BAR CANVAS VIEWS?

Answer: Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its
menu bar. Vertical Tool bars are displayed along the

37] WHAT ARE MODELLESS WINDOWS?

Answer: More than one modelless window can be displayed at the same time, and operators can navigate among them if your application allows them to do
so . On most GUI platforms, modelless windows can also be

38] DOES A GROUPING DONE FOR OBJECTS IN THE LAYOUT EDITOR AFFECT THE GROUPING DONE IN THE DATA MODEL EDITOR?

Answer: No.

39] ATLEAST HOW MANY SET OF DATA MUST A DATA MODEL HAVE BEFORE A DATA MODEL CAN BE BASE ON IT?

Answer: Four

40] WHAT ARE THE DIFFERENT MODELS OF WINDOWS?

Answer: Modalless windows Modal windows

41] CAN A REPEATING FRAME BE CREATED WITHOUT A DATA GROUP AS A BASE?

Answer: No

42] WHAT ARE THE SQL CLAUSES SUPPORTED IN THE LINK PROPERTY SHEET?

Answer: Where start with having.

43] WHAT IS SYSTEM.COORDINATION_OPERATION?

Answer: It represents the coordination causing event that occur on the master block in master-detail relation.

44] CAN A FORMULA COLUMN REFERRED TO COLUMNS IN HIGHER GROUP?

Answer: Yes

45] WHAT IS THE USE OF IMAGE_ZOOM BUILT-IN?

Answer: To manipulate images in image items.

46] WHAT IS A LIBRARY?

Answer: A library is a collection of subprograms including user named procedures, functions and packages.

47] CAN YOU PASS DATA PARAMETERS TO FORMS?

Answer: No

48] WHAT IS DIFFERENCE BETWEEN OPEN_FORM AND CALL_FORM?

Answer: when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as
desired. when one form invokes another form by executing cal

49] CAN A FIELD BE USED IN A REPORT WITHOUT IT APPEARING IN ANY DATA GROUP?

Answer: Yes

50] CAN A FORMULA COLUMN BE OBTAINED THROUGH A SELECT STATEMENT?

Answer: Yes

51] WHAT ARE THE DIFFERENT FILE EXTENSIONS THAT ARE CREATED BY ORACLE REPORTS?

Answer: Rep file and Rdf file.

52] WHAT ARE THE TWO PANES THAT APPEAR IN THE DESIGN TIME PL/SQL INTERPRETER?

Answer: 1.Source pane. 2. Interpreter pane

53] STATE ANY THREE MOUSE EVENTS SYSTEM VARIABLES?

Answer: System.mouse_button_pressedSystem.mouse_button_shift_statesystem.mouse_itemsystem.mouse_canvassystem.mouse_record

54] WHAT ARE THE TRIGGERS ASSOCIATED WITH IMAGE ITEMS?

Answer: When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks
on an image item

55] IS IT POSSIBLE TO HAVE A LINK FROM A GROUP THAT IS INSIDE A CROSS PRODUCT TO A GROUP OUTSIDE ?

Answer: No

56] HOW DO YOU CALL OTHER ORACLE PRODUCTS FROM ORACLE FORMS?

Answer: Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If
the called product is unavailable at the time of the

57] WHAT IS THE USER-NAMED EDITOR?

Answer: A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes
such as windows display size, position, and titl

58] WHAT ARE THE DIFFERENT WINDOWS EVENTS ACTIVATED AT RUNTIMES?

Answer: When_window_activated When_window_closed When_window_deactivated When_window_resized Within this triggers, you can examine the built
in system variable system. event_window to determine the name o

59] WHAT USE OF COMMAND LINE PARAMETER CMD FILE?

Answer: It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.

60] WITH WHICH FUNCTION OF SUMMARY ITEM IS THE COMPUTE AT OPTIONS REQUIRED?

Answer: Percentage of total functions.

61] WHAT ARE THE TWO TYPES OF VIEWS AVAILABLE IN THE OBJECT NAVIGATOR(SPECIFIC TO REPORT 2.5)?

Answer: View by structure and view by type .

62] AN OPEN FORM CAN NOT BE EXECUTE THE CALL_FORM PROCEDURE IF YOU CHAIN OF CALLED FORMS HAS BEEN INITIATED BY ANOTHER
OPEN FORM?
Answer: True

63] DOES A BEFORE FORM TRIGGER FIRE WHEN THE PARAMETER FORM IS SUPPRESSED?

Answer: Yes.

64] WHAT IS AN ANCHORING OBJECT & WHAT IS ITS USE?

Answer: An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.

65] IF THE MAXIMUM RECORD RETRIEVED PROPERTY OF THE QUERY IS SET TO 10 THEN A SUMMARY VALUE WILL BE CALCULATED?

Answer: Only for 10 records.

66] HOW CAN I MESSAGE TO PASSED TO THE USER FROM REPORTS?

Answer: By using SRW.MESSAGE function.

67] WHAT IS THE ADVANTAGE OF THE LIBRARY?

Answer: Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library,
you can attach it to any other form, menu, or library

68] WHY IS A WHERE CLAUSE FASTER THAN A GROUP FILTER OR A FORMAT TRIGGER?

Answer: Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.

69] WHAT IS LEXICAL REFERENCE? HOW CAN IT BE CREATED?

Answer: Lexical reference is place_holder for text that can be embedded in a sql statements. A lexical reference can be created using & before the column
or parameter name.

70] HOW DO YOU CREATE A NEW SESSION WHILE OPEN A NEW FORM?

Answer: Using open_form built-in setting the session option Ex. Open_form('Stocks ',active,session). when invoke the mulitiple forms with open form and
call_form in the same application, state whether the fol

71] WHAT IS THE MAXIMUM ALLOWED LENGTH OF RECORD GROUP COLUMN?

Answer: Record group column names cannot exceed 30 characters.

72] WHAT IS THE USE OF TRANSACTIONAL TRIGGERS?

Answer: Using transactional triggers we can control or modify the default functionality of the oracle forms.
73] WHAT ARE BUILT-INS ASSOCIATED WITH TIMERS?

Answer: find_timercreate_timerdelete_timer

74] WHAT ARE THE DIFFERENT DISPLAY STYLES OF LIST ITEMS?

Answer: Pop_listText_listCombo box

75] WHICH OF THE ABOVE METHODS IS THE FASTER METHOD?

Answer: Performing the calculation in the query is faster.

76] IS IT POSSIBLE TO CENTER AN OBJECT HORIZONTALLY IN A REPEATING FRAME THAT HAS A VARIABLE HORIZONTAL SIZE?

Answer: Yes

77] WHAT IS TERM?

Answer: The term is terminal definition file that describes the terminal form which you are using r20run.

78] WHICH PARAMETER CAN BE USED TO SET READ LEVEL CONSISTENCY ACROSS MULTIPLE QUERIES?

Answer: Read only

79] WHERE IS THE EXTERNAL QUERY EXECUTED AT THE CLIENT OR THE SERVER?

Answer: At the server.

80] WHAT ARE PARAMETERS?

Answer: Parameters provide a simple mechanism for defining and setting the valuesof inputs that are required by a form at startup. Form parameters are
variables of type char,number,date that you define at des

81] WHAT IS THE DIFFERENCE BETWEEN BOILER PLAT IMAGES AND IMAGE ITEMS?

Answer: Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your
form, such as company logos and maps. Image items

82] EXPLAIN ABOUT CONTENT CANVAS VIEWS?

Answer: Most Canvas views are content canvas views a content canvas view is the "base" view that occupies the entire content pane of the window in
which it is displayed.

83] WHAT ARE THE MASTER-DETAIL TRIGGERS?

Answer: On-Check_delete_masterOn_clear_detailsOn_populate_details
84] WHAT ARE THE TRIGGERS AVAILABLE IN THE REPORTS?

Answer: Before report, Before form, After form , Between page, After report.

85] HOW DO YOU REFERENCE A PARAMETER?

Answer: In Pl/Sql, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = '' or :block.item =
PARAMETER Parameter name

86] WHAT ARE THE IMPORTANT DIFFERENCE BETWEEN PROPERTY CLAUSE AND VISUAL ATTRIBUTES?

Answer: Named visual attributes differ only font, color & pattern attributes, property clauses can contain this and any other properties. You can change the
appearance of objects at run time by changing the n

87] WHAT IS THE MAIN DIFF. BET. REPORTS 2.0 & REPORTS 2.5?

Answer: Report 2.5 is object oriented.

88] IS IT POSSIBLE TO MODIFY AN EXTERNAL QUERY IN A REPORT WHICH CONTAINS IT?

Answer: No.

89] WHAT IS THE USE OF BREAK GROUP?

Answer: A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.

90] WHAT IS THE DIFF. WHEN CONFINE MODE IS ON AND WHEN IT IS OFF?

Answer: When confine mode is on, an object cannot be moved outside its parent in the layout.

91] WHAT ARE THE DIFFERENT PARAMETER TYPES?

Answer: Text ParametersData Parameters

92] WHAT ARE THE VARIOUS SUB EVENTS A MOUSE DOUBLE CLICK EVENT INVOLVES?

Answer: Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down & mouse up events.

93] WHAT IS A COMBO BOX?

Answer: A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style
list item will both display fixed values and a

94] AT WHAT POINT OF REPORT EXECUTION IS THE BEFORE REPORT TRIGGER FIRED?
Answer: After the query is executed but before the report is executed and the records are displayed.

95] WHAT IS THE FRAME & REPEATING FRAME?

Answer: A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not
known before.

96] WHAT DOES THE TERM PANEL REFER TO WITH REGARD TO PAGES?

Answer: A panel is the no. of physical pages needed to print one logical page.

97] HOW CAN A SQUARE BE DRAWN IN THE LAYOUT EDITOR OF THE REPORT WRITER?

Answer: By using the rectangle tool while pressing the (Constraint) key.

98] IS IT POSSIBLE TO SPLIT THE PRINT REVIEWER INTO MORE THAN ONE REGION?

Answer: Yes

99] TO EXECUTE ROW FROM BEING DISPLAYED THAT STILL USE COLUMN IN THE ROW WHICH PROPERTY CAN BE USED?

Answer: Format trigger.

100 WHAT ARE THE DEFAULT PARAMETER THAT APPEAR AT RUN TIME IN THE PARAMETER SCREEN?
]
Answer: Destype and Desname.

101 WHERE IS A PROCEDURE RETURN IN AN EXTERNAL PL/SQL LIBRARY EXECUTED AT THE CLIENT OR AT THE SERVER?
]
Answer: At the client.

102 WHAT IS BIND REFERENCE AND HOW CAN IT BE CREATED?

]
Answer: Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a
parameter name.

103 IS IT POSSIBLE TO DISABLE THE PARAMETER FROM WHILE RUNNING THE REPORT?
]
Answer: Yes

104 FROM WHICH DESIGNATION IS IT PREFERRED TO SEND THE OUTPUT TO THE PRINTED?
]
Answer: Previewer
105 WHAT ARE THREE PANES THAT APPEAR IN THE RUN TIME PL/SQL INTERPRETER?
]
Answer: 1. Source pane. 2. Interpreter pane. 3. Navigator pane.

106 HOW CAN A BUTTON BE USED IN A REPORT TO GIVE A DRILL DOWN FACILITY?
]
Answer: By setting the action associated with button to Execute pl/sql option and using the SRW.Run_report function.

107 WHAT IS THE DIFF. WHEN FLEX MODE IS MODE ON AND WHEN IT IS OFF?
]
Answer: When flex mode is on, reports automatically resizes the parent when the child is resized.

108 DO USER PARAMETERS APPEAR IN THE DATA MODAL EDITOR IN 2.5?

]
Answer: No

109 IF A PARAMETER IS USED IN A QUERY WITHOUT BEING PREVIOUSLY DEFINED, WHAT DIFF. EXIST BETW. REPORT 2.0 AND 2.5 WHEN THE
] QUERY IS APPLIED?

Answer: While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.

110 WHICH OF THE TWO VIEWS SHOULD OBJECTS ACCORDING TO POSSESSION?

]
Answer: view by structure.

111 WHAT ARE THE DIFFERENT STYLES OF ACTIVATION OF OLE OBJECTS?

]
Answer: In place activation External activation

112 WHAT IS THE DIFFERENCE BETWEEN OLE SERVER & OLE CONTAINER?
]
Answer: 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 obje

113 WHAT IS THE DIFFERENCE BETWEEN OBJECT EMBEDDING & LINKING IN ORACLE FORMS?
]
Answer: In Oracle forms, embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.

114 WHAT IS AN OLE?

]
Answer: Object Linking & Embedding provides you with the capability to integrate objects from many Ms-Windows applications into a single compound
document creating integrated applications enables you to use t

115 WHAT ARE THE DIFFERENT OBJECTS THAT YOU CANNOT COPY OR REFERENCE IN OBJECT GROUPS?
]
Answer: Objects of different modules Another object groups Individual block dependent items Program units.
116 WHAT IS AN OBJECT GROUP?
]
Answer: 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.

117 WHEN A FORM IS INVOKED WITH CALL_FORM, DOES ORACLE FORMS ISSUES A SAVE POINT?
]
Answer: Yes

118 ANY ATTEMPT TO NAVIGATE PROGRAMMATICALLY TO DISABLED FORM IN A CALL_FORM STACK IS ALLOWED?
]
Answer: False

119 WHAT ARE THE DEFAULT EXTENSIONS OF THE FILES CREATED BY LIBRARY MODULE?
]
Answer: The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary

120 WHAT ARE THE DEFAULT EXTENSIONS OF THE FILES CREATED BY MENU MODULE?
]
Answer: .mmb, .mmx

121 WHAT ARE THE DEFAULT EXTENSIONS OF THE FILES CREATED BY FORMS MODULES?
]
Answer: .fmb - form module binary .fmx - form module executable

122 WHAT ARE DIFFERENT TYPES OF MODULES AVAILABLE IN ORACLE FORM?

]
Answer: Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up
an application menu library module - a collection of user n

123 USE THE ADD_GROUP_ROW PROCEDURE TO ADD A ROW TO A STATIC RECORD GROUP 1. TRUE OR FALSE?
]
Answer: False

124 USE THE ADD_GROUP_COLUMN FUNCTION TO ADD A COLUMN TO RECORD GROUP THAT WAS CREATED AT A DESIGN TIME?
]
Answer: False

125 WHAT ARE THE BUILT-INS USED FOR FINDING OBJECT ID FUNCTIONS?
]
Answer: Find_group(function) Find_column(function)

126 WHAT ARE THE BUILT-IN USED FOR GETTING CELL VALUES?
]
Answer: Get_group_char_cell(function) Get_group_date_cell(function) Get_group_number_cell(function)

127 WHAT ARE BUILT-INS USED FOR PROCESSING ROWS?

]
Answer: GET_GROUP_ROW_COUNT(function) GET_GROUP_SELECTION_COUNT(function) GET_GROUP_SELECTION(function)
RESET_GROUP_SELECTION(procedure) SET_GROUP_SELECTION(procedure) UNSET_GROUP_SELECTION(procedure)

128 WHAT ARE THE BUILT -INS USED FOR MODIFYING A GROUPS STRUCTURE?
]
Answer: ADD-GROUP_COLUMN (function) ADD_GROUP_ROW (procedure) DELETE_GROUP_ROW(procedure)

129 WHAT ARE THE BUILT-INS USED FOR CREATING AND DELETING GROUPS?
]
Answer: CREATE-GROUP (function) CREATE_GROUP_FROM_QUERY(function) DELETE_GROUP(procedure)

130 WHAT ARE THE DIFFERENT TYPES OF RECORD GROUPS?

]
Answer: Query Record Groups NonQuery Record Groups State Record Groups

131 HOW MANY NUMBER OF COLUMNS A RECORD GROUP CAN HAVE?

]
Answer: A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does
not exceed 64K.

132 WHAT IS A RECORD GROUP?

]
Answer: A record group is an internal Oracle Forms that structure that has a column/row framework similar to a database table. However, unlike database
tables, record groups are separate objects that belong t

133 WHAT ARE THE BUILT-INS THAT ARE USED FOR SETTING THE LOV PROPERTIES AT RUNTIME?
]
Answer: get_lov_property set_lov_property

134 WHAT ARE THE BUILT_INS USED THE DISPLAY THE LOV?
]
Answer: Show_lov List_values

135 WHAT IS THE BASIC DATA STRUCTURE THAT IS REQUIRED FOR CREATING AN LOV?
]
Answer: Record Group.

136 WHAT IS AN LOV?

]
Answer: An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
137 WHAT ARE THE BUILT-INS TO DISPLAY THE USER-NAMED EDITOR?
]
Answer: A user named editor can be displayed programmatically with the built in procedure SHOW-EDITOR, EDIT_TETITEM independent of any particular
text item.

138 WHAT ARE THE DIFFERENT TYPES OF COORDINATIONS OF THE MASTER WITH THE DETAIL BLOCK?
]
Answer: Immediate Differred->Yes Autoquery->Yes Differred->Yes Autoquery->No

139 WHAT ARE THE COORDINATION PROPERTIES IN A MASTER-DETAIL RELATIONSHIP?

]
Answer: The coordination properties are Deferred Auto-Query These Properties determine when the population phase of block coordination should occur.

140 WHAT ARE THE DIFFERENT DEFAULT TRIGGERS CREATED WHEN MASTER DELETES PROPERTY IS SET TO ISOLATED?
]
Answer: Master Deletes Property Resulting Triggers --------------------------------------------------- Isolated On-Clear-Details On-Populate-Details

141 WHAT ARE THE DIFFERENT DEFAULT TRIGGERS CREATED WHEN MASTER DELETES PROPERTY IS SET TO CASCADE?
]
Answer: Master Deletes Property Resulting Triggers --------------------------------------------------- Cascading On-Clear-Details On-Populate-Details Pre-delete

142 WHAT ARE THE DIFFERENT DEFAULT TRIGGERS CREATED WHEN MASTER DELETES PROPERTY IS SET TO NON-ISOLATED?
]
Answer: Master Deletes Property Resulting Triggers ---------------------------------------------------- Non-Isolated(the default) On-Check-Delete-Master On-Clear-
Details On-Populate-Details

143 WHAT ARE THE DIFFERENT TYPES OF DELETE DETAILS WE CAN ESTABLISH IN MASTER-DETAILS?
]
Answer: Cascade Isolate Non-isolate

144 WHAT ARE MOST COMMON TYPES OF COMPLEX MASTER-DETAIL RELATIONSHIPS?

]
Answer: There are three most common types of complex master-detail relationships: master with dependent details master with independent details detail
with two masters

145 WHAT IS COORDINATION EVENT?

]
Answer: Any event that makes a different record in the master block the current record is a coordination causing event.

146 WHAT IS A MASTER DETAIL RELATIONSHIP?

]
Answer: A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the
blocks reflects a primary key to foreign key relationship

147 WHAT ARE DIFFERENT TYPES OF IMAGES?

]
Answer: Boiler plate imagesImage Items

148 WHAT IS FORMS_DDL?

]
Answer: Issues dynamic Sql statements at run time, including server side pl/SQl and DDL

149 WHAT IS A USER_EXIT?

]
Answer: Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms
executable.

150 WHAT IS A TEXT_IO PACKAGE?

]
Answer: It allows you to read and write information to a file in the file system.

151 HOW CAN VALUES BE PASSED BET. PRECOMPILER EXITS & ORACLE CALL INTERFACE?
]
Answer: By using the statement EXECIAFGET & EXECIAFPUT.

152 WHAT ARE THE THREE TYPES OF USER EXITS AVAILABLE ?

]
Answer: Oracle Precompiler exits, Oracle call interface, NonOracle user exits.

153 WHAT IS AN USER EXIT USED FOR?

]
Answer: A way in which to pass control (and possibly arguments ) form Oracle report to another Oracle products of 3 GL and then return control ( and )
back to Oracle reports.

154 IS IT POSSIBLE TO LINK TWO GROUPS INSIDE A CROSS PRODUCTS AFTER THE CROSS PRODUCTS GROUP HAS BEEN CREATED?
]
Answer: No

155 HOW CAN A GROUP IN A CROSS PRODUCTS BE VISUALLY DISTINGUISHED FROM A GROUP THAT DOES NOT FORM A CROSS PRODUCT?
]
Answer: A group that forms part of a cross product will have a thicker border.

156 HOW CAN A CROSS PRODUCT BE CREATED?

]
Answer: By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.

157 NAME OF THE FUNCTIONS USED TO GET/SET CANVAS PROPERTIES?

]
Answer: Get_view_property, Set_view_property
158 EXPLAIN ABOUT STACKED CANVAS VIEWS?
]
Answer: Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas
views obscure some part of the underlying content canvas vi

159 CAN A PROPERTY CLAUSE ITSELF BE BASED ON A PROPERTY CLAUSE?

]
Answer: Yes

160 WHAT IS A PROPERTY CLAUSE?

]
Answer: A property clause is a named object that contains a list of properties and their settings. Once you create a property clause you can base other
object on it. An object based on a property can inherit

161 HOW DO YOU DISPLAY CONSOLE ON A WINDOW ?

]
Answer: The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned.To specify that the
console should be displayed, set the console window form

162 WHAT ARE MODAL WINDOWS?

]
Answer: Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example
operators cannot resize, scroll or iconify a modal window.

163 WHAT ARE THE VBX CONTROLS?

]
Answer: Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program
outputs.vbx control where originally develop as extensions

164 WHAT IS STRIP SOURCES GENERATE OPTIONS?

]
Answer: Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final
deployment, but can not be subsequently edited in the des

165 WHAT ARE DISPLAY ITEMS?

]
Answer: Display items are similar to text items with the exception that display items only store and display fetched or assigned values. Display items are
generally used as boilerplate or conditional text.

166 WHAT IS A TEXT LIST?

]
Answer: The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not
be displayed, a vertical scroll bar appears, allow

167 WHAT IS POP LIST?

]
Answer: The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available
choices appears.
168 WHAT ARE THE DIFFERENCE BETWEEN LOV & LIST ITEM?
]
Answer: Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.

169 WHAT IS THE DIFFERENCE BETWEEN $$DATE$$ & $$DBDATE$$$$DBDATE$$ RETRIEVES THE CURRENT DATABASE DATE$$DATE$$
] RETRIEVES THE CURRENT OPERATING SYSTEMDATE.

Answer: $$date$$ displaying local system date $$dbdate$$ displaying server date.

170 WHAT IS WHEN-DATABASE-RECORD TRIGGER?

]
Answer: Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that
the record should be processed by the next post or

171 WHAT ARE THE POSSIBLE CLAUSES INCLUDED IN THE CREATE TABLE COMMAND?
]
Answer: Where clause, order by clause

172 WHAT ARE THE WAYS TO MONITOR THE PERFORMANCE OF THE REPORT?
]
Answer: Use reports profile executable statement. Use SQL trace facility.

173 IN ORACLE VERSION 9.2.0.4.0 WHAT DOES EACH NUMBER REFERS TO?
]
Answer: 1 byte.

174 GIVE THE SEQUENCE OF EXECUTION OF THE VARIOUS REPORT TRIGGERS?

]
Answer: Before form, After form, Before report, Between page, After report.

175 WHAT IS A PHYSICAL PAGE? & WHAT IS A LOGICAL PAGE?

]
Answer: A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the
Previewer.

176 WHAT ARE THE TWO REPEATING FRAME ALWAYS ASSOCIATED WITH MATRIX OBJECT?
]
Answer: One down repeating frame below one across repeating frame.

177 IF YES, HOW?

]
Answer: By the use anchors.

178 FOR A FIELD IN A REPEATING FRAME, CAN THE SOURCE COME FROM THE COLUMN WHICH DOES NOT EXIST IN THE DATA GROUP WHICH
] FORMS THE BASE FOR THE FRAME?
Answer: Yes

179 HOW CAN A TEXT FILE BE ATTACHED TO A REPORT WHILE CREATING IN THE REPORT WRITER?
]
Answer: By using the link file property in the layout boiler plate property sheet.

180 THE JOIN DEFINED BY THE DEFAULT DATA LINK IS AN OUTER JOIN YES OR NO?
]
Answer: Yes

181 IF TWO GROUPS ARE NOT LINKED IN THE DATA MODEL EDITOR, WHAT IS THE HIERARCHY BETWEEN THEM?
]
Answer: Two group that is above are the left most rank higher than the group that is to right or below it.

182 HOW CAN A BREAK ORDER BE CREATED ON A COLUMN IN AN EXISTING GROUP?

]
Answer: By dragging the column outside the group.

183 WHAT IS THE PURPOSE OF THE PRODUCT ORDER OPTION IN THE COLUMN PROPERTY SHEET?
]
Answer: To specify the order of individual group evaluation in a cross products.

184 IF A BREAK ORDER IS SET ON A COLUMN WOULD IT AFFECT COLUMNS WHICH ARE UNDER THE COLUMN?
]
Answer: No

185 IS IT POSSIBLE TO SET A FILTER CONDITION IN A CROSS PRODUCT GROUP IN MATRIX REPORTS?
]
Answer: No

186 IS IT POSSIBLE TO INSERT COMMENTS INTO SQL STATEMENTS RETURN IN THE DATA MODEL EDITOR?
]
Answer: Yes

187 WHAT IS USE OF TERM?

]
Answer: The term file which key is correspond to which oracle report functions.

188 WHAT IS THE DIFFERENCE BETWEEN TWO NULL VALUES IN A COLUMN?

]
Answer: No two values are same and if we compare two null values each other the value is garbage. we cannot determine the value

189 WHAT ARE DIFFERENT TYPES OF CANVAS VIEWS?

]
Answer: Content canvas views Stacked canvas views Horizontal toolbar vertical toolbar.

List of all SQL PLUS Interview Questions

1] HOW TO GET/SELECT THE NTH ROW FROM THE TABLE ? HOW TO SELECT FIRST N ROWS ,LAST N ROWS FROM A TABLE

Answer: nth salary select salary from table_name a where &n=(select count(salary) from table_name b where a.salary<=b.salary); n salaries select salary
from table_name a where &n>=(sele

2] WHAT IS MEANT BY SCROLLABLE CURSOR

Answer: A scrollable cursor, however, can move forward and backward, and can seek any desired record in the cursor. Such operations are common in
applications that present results sets in scrolling windows. W

3] SUBQUERY VS JOIN

Answer: subquery retrive the data depending on certain condition or manupulation in inner query. where as joins will join the enitire data depending on the
conditions given it cannot manupulate the data or

4] FIND OUT NTH HIGHEST SALARY FROM EMP TABLE

Answer: select sal from emp E wherenulln-1=(select count( distinct sal) from emp where sal>E.sal);

5] DISPLAY THE RECORDS BETWEEN TWO RANGE I KNOW THE NVL FUNCTION ONLY ALLOWS THE SAME DATA TYPE(IE. NUMBER OR CHAR
OR DATE NVL(COMM, 0)), IF COMMISSION IS NULL THEN THE TEXT ?NOT APPLICABLE? WANT TO DISPLAY, INSTEAD OF BLANK SPACE.
HOW DO I WRITE THE QUERY

Answer: You can use the decode function for the above requirement. Please find the query as below: select ename,decode(nvl(comm,0),0,'Not
Applicable',comm) from scott.emp;

6] EXPLAIN CONNECT BY PRIOR

Answer: Retrieves rows in hierarchical order. e.g. select empno, ename from emp where.

7] WHAT IS THE MAXIMUM NUMBER OF TRIGGERS, CAN APPLY TO A SINGLE TABLE

Answer: Insert/Update/Delete :- 3 Before/After:- 2 Row Level/Statement Level:-2 Hence 3*2*2

8] CAN A VIEW BE UPDATED/INSERTED/DELETED? IF YES UNDER WHAT CONDITIONS ?

Answer: A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update
and delete is not possible.

9] WHAT ARE PL/SQL CURSOR EXCEPTIONS

Answer: Cursor_already_open, Invalid_cursor

10 EXPLAIN THE DIFFERENT TYPES OF JOINS

]
Answer: Join is a query which retrieves related columns or rows from multiple tables.Self Join - Joining the table with itself.Equi Join - Joining two tables by
equating two common columns.Non-Equi Join - Joi

11 WHAT IS A NON- CORELATED SUBQUERY

]
Answer: Non corelated subquery, where the subquery has been executed once for the entire parent statement.
12 WHAT IS A CORELATED SUBQUERY
]
Answer: Correlated subquery is the subquery, where the subquery has been executed for the every row processed by the parent statement.

13 WHAT IS MEANT BY SORTING AND GROUPING

]
Answer: For sorting we use order by clause in select statement. This is used to sort data in ascending order or descending order. To group data based on
perticulr column we use groupby clause. Both are us

14 WHAT DO YOU KNOW ABOUT SUBQUERIES

]
Answer: Subqueries will be executed once for the entire parent statement.

15 HOW DO I REPLACE A COMMA (,) WITH A BLANK IN A SELECT STATEMENT?

]
Answer: select empno||' '||ename from emp;

16 OTHER WAY TO REPLACE QUERY RESULT NULL VALUE WITH A TEXT

]
Answer: NVL or Decode

17 WHICH DATE FUNCTION RETURNS NUMBER VALUE

]
Answer: Months_betweenThis date function takes 2 valid dates and returns number of months in between them.

18 YOU WANT TO USE SQL TO BUILD SQL, WHAT IS THIS CALLED AND GIVE AN EXAMPLE
]
Answer: This is called dynamic SQL. An example would be: set lines 90 pages 0 termout off feedback off verify off spool drop_all.sql select ?drop user ?||
username||? cascade;? from dba_users where usernam

19 ANY THREE PL/SQL EXCEPTIONS

]
Answer: TOO_MANY_ROWS NO_DATA_FOUND INVALID_CURSORS CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX

20 WHAT ARE THE TYPES OF SQL STATEMENT ?

]
Answer: Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.Data Manipulation Language:
INSERT,UPDATE,DELETE,LOCK TABLE,EXPLAIN PLAN & SELECT.Transactional Control: COMMIT & ROLLBA

21 EXPLAIN CONNECT BY PRIOR ?

]
Answer: "connect by prior" is clause which is used in hierarchical queries.Example select ename,empno,mgr,job from emp start with job='PRESIDENT'
connect by prior empno=mgr;

22 HOW TO ACCESS THE CURRENT VALUE AND NEXT VALUE FROM A SEQUENCE ? IS IT POSSIBLE TO ACCESS THE CURRENT VALUE IN A
] SESSION BEFORE ACCESSING NEXT VALUE ?

Answer: Sequence name CURRVAL, Sequence name NEXTVAL.It is not possible. Only if you access next value in the session, current value can be
accessed.

23 WHAT IS ON DELETE CASCADE ?

]
Answer: When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a
referenced primary or unique key value is removed.

24 WHAT ARE THE USAGES OF SAVEPOINTS?

]
Answer: SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are
allowed.

25 IS IT POSSIBLE TO ACCESS THE CURRENT VALUE IN A SESSION BEFORE ACCESSING NEXT VALUE
]
Answer: NEXTAVAL must be issued for that sequence before CURRVAL contaons a value from Oracle Server SQL references 8

26 WHAT IS THE SUB QUERY?

]
Answer: Sub query is a query whose return values are used in filtering conditions of the main query.

27 HOW MANY LONG COLUMNS ARE ALLOWED IN A TABLE? IS IT POSSIBLE TO USE LONG COLUMNS IN WHERE CLAUSE OR ORDER BY ?
]
Answer: Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

28 WHAT IS REFERENTIAL INTEGRITY?

]
Answer: Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or
unique key of the referenced table.

29 WHAT IS THE USAGE OF SAVEPOINTS

]
Answer: SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are
allowed.

30 WHAT IS TKPROF AND HOW IS IT USED?

]
Answer: The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in
the initialization file and then turning on traci

31 WHAT ARE THE DIFFERENT TYPES OF SQL

]
Answer: There are 5 types 1.data definition type 2.data manipulation 3.data control 4.transaction control 5.data query

32 WHAT ARE THE DIFFERENT TABLESPACES IN DATABASE?

]
Answer: A tablespace is a collection of one or more datafiles.all database objects are stored in tablespaces. Diff types of tablespaces:- 1)system tablespace.
2) temp tablespace. 3) tools tablespa

33 DISPLAY THE NUMBER VALUE IN WORDS

]
Answer: select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

34 HOW CAN YOU CALL A PL/SQL PROCEDURE FROM SQL?

]
Answer: By use of the EXECUTE (short form EXEC) command.
35 YOU WANT TO DETERMINE THE LOCATION OF IDENTICAL ROWS IN A TABLE BEFORE ATTEMPTING TO PLACE A UNIQUE INDEX ON THE
] TABLE, HOW CAN THIS BE DONE?

Answer: Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select
against the proposed primary key you can squeeze out the rowi

36 WHAT IS A CARTESIAN PRODUCT?

]
Answer: A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z
number of rows where x, y, z correspond to the nu

37 IF A VIEW ON A SINGLE BASE TABLE IS MANIPULATED WILL THE CHANGES BE REFLECTED ON THE BASE TABLE?
]
Answer: if view on based on a single table then u can execute any DML directly on it and can see the changes in the base table and if view is based on join
of 2 tables then only one base table can be modif

38 WHAT IS CYCLE/NO CYCLE IN A SEQUENCE?

]
Answer: CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence
reaches its maximum value, it generates its minimum value. A

39 WHAT IS DIFFERENCE BETWEEN CHAR AND VARCHAR2? WHAT IS THE MAXIMUM SIZE ALLOWED FOR EACH TYPE?
]
Answer: CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

40 YOU ARE JOINING A LOCAL AND A REMOTE TABLE, THE NETWORK MANAGER COMPLAINS ABOUT THE TRAFFIC INVOLVED, HOW CAN YOU
] REDUCE THE NETWORK TRAFFIC?

Answer: Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the
data required for the join being sent across.

41 WHAT ARE THE TYPES OF SQL STATEMENT

]
Answer: Data definition Language Data Manipulation language data Control Language Senssion Control System Control

42 WHAT ARE THE DATA TYPES ALLOWED IN A TABLE?

]
Answer: CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

43 WHAT SQLPLUS COMMAND IS USED TO FORMAT OUTPUT FROM A SELECT?

]
Answer: This is best done with the COLUMN command.

44 EXPLAIN UNION, MINUS, UNION ALL, INTERSECT?

]
Answer: INTERSECT returns all distinct rows selected by both queries.MINUS - returns all distinct rows selected by the first query but not by the
second.UNION - returns all distinct rows selected by either qu
45 EXPLAIN THE UNION OPERATION IN SQL . WHAT IS MEANT BY OUTER JOIN
]
Answer: UNION is a set operator which combines the result set of one query with that of the other OUTER JOIN is a type of join which gives both the
matching and unmatching in the table specified in the lef

46 WHAT ARE THE MORE COMMON PSEUDO-COLUMNS

]
Answer: Types of pseudo columns rownum rowid currval nextval level user uid

47 IMPLICIT CURSOR ATTRIBUTES

]
Answer: SQL%FOUND,SQL%NOT FOUND, SQL%IS OPEN, SQL%ROW COUNT

48 EXPLICIT CURSOR ATTRIBUTES

]
Answer: %IS OPEN,%FOUND,%NOT FOUND,%ROW COUNT

49 HOW WILL YOU ACTIVATE/DEACTIVATE INTEGRITY CONSTRAINTS

]
Answer: The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

50 WHAT IS THE MAXIMUM SIZE ALLOWED FOR EACH TYPE?

]
Answer: The Max Size is upto 32.

51 WHAT IS REFERENTIAL INTEGRITY CONSTRAINT?

]
Answer: Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or
unique key of the referenced table.

52 WHAT IS THE FASTEST WAY OF ACCESSING A ROW IN A TABLE?

]
Answer: Using ROWID.CONSTRAINTS

53 WHAT IS ROWID
]
Answer: ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

54 WHAT IS THE SUB-QUERY?

]
Answer: Sub query is a query whose return values are used in filtering conditions of the main query.

55 WHAT IS A TRANSACTION?
]
Answer: It is a operation on database / table. Which has to be confirmed after completion. It may be single sql stm or multiple stm
56 WHAT IS EXPLAIN PLAN AND HOW IS IT USED?
]
Answer: The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running
the explain plan for. This is created using the utlxplan.sql s

57 HOW DO YOU SET THE NUMBER OF LINES ON A PAGE OF OUTPUT, THE WIDTH?
]
Answer: The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET
PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long

58 WHAT IS DIFFERENCE BETWEEN TRUNCATE & DELETE?

]
Answer: TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered
deletion. Deleted records can be rolled back or committed.

59 WHERE THE INTEGRITY CONSTRAINTS ARE STORED IN DATA DICTIONARY?

]
Answer: The integrity constraints are stored in USER_CONSTRAINTS.

60 WHAT IS A JOIN? EXPLAIN THE DIFFERENT TYPES OF JOINS?

]
Answer: Join is a query which retrieves related columns or rows from multiple tables.Self Join - Joining the table with itself.Equi Join - Joining two tables by
equating two common columns.Non-Equi Join - Joi

61 THE USE OF HAVING, WHERE AND GROUPBY IN ONE SQL

]
Answer: "where" filters data before grouping "Having" filters data after grouping

62 DISPLAY ODD/ EVEN NUMBER OF RECORDS

]
Answer: Odd number of records: select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); 1 3 5 Even number of records: select * from
emp where (rowid,0) in (select rowid, mod(ro

63 HOW DO I DISPLAY ROW NUMBER WITH RECORDS?

]
Answer: SELECT rownum,table_name.* FROM table_name;

64 HOW DO I ELIMINATE THE DUPLICATE ROWS?

]
Answer: Use the DISTINCT keyword right after SELECT... i.e. SELECT DISTINCT customername FROM customer

65 WHAT IS A DATABASE LINK?

]
Answer: Database Link is a named path through which a remote database can be accessed
66 HOW TO DROP THE COLUMN IN A TABLE
]
Answer: There are 2 ways of doing it : alter table"table name" drop column "column name"; This drops the column immediately. However if there is huge data
and you would like to postpone the task of drop

67 HOW WILL YOU A ACTIVATE/DEACTIVATE INTEGRITY CONSTRAINTS ?

]
Answer: The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

68 WHAT ARE THE DIFFERENT TYPES OF OUTER JOINS

]
Answer: LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

69 WHAT ARE THE PRE REQUISITES ?

]
Answer: I. to modify data type of a column ii. to add a column with NOT NULL constraint ? To Modify the datatype of a column the column must be empty. to
add a column with NOT NULL constrain, the table must

70 WHAT IS AN INTEGRITY CONSTRAINT ?

]
Answer: Integrity constraint is a rule that restricts values to a column in a table.

71 DIFFERENCE BETWEEN SUBSTR AND INSTR ?

]
Answer: INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of
string1.SUBSTR (String1 n,m)SUBSTR returns a character

72 HOW TO DROP THE INDEX

]
Answer: Drop Index Indexname

73 WHAT IS THE OUTPUT OF SIGN FUNCTION

]
Answer: SIGN (a): Returns 1 if a is positive or if a is 0, and -1 if a is less than 0.

74 TO VIEW INSTALLED ORACLE VERSION INFORMATION

]
Answer: From the SQL> prompt, type the following: select * from v$version;

75 IF YES - UNDER WHAT CONDITIONS

]
Answer: an Insert,Update,delete can be done through views is 1. inserts/updates/deletes done only in one base table at a time 2. primary key columns
should be part of view 3. columns which are going to

76 WHAT ARE THE ADVANTAGES OF VIEW

]
Answer: Advantages of view: 1. Restricts the access to particular columns and rows of the base tables. 2. Hide the data complexity. 3. Can access the data
for two different base tables without perfor

77 IF UNIQUE KEY CONSTRAINT ON DATE COLUMN IS CREATED, WILL IT VALIDATE THE ROWS THAT ARE INSERTED WITH SYSDATE?
]
Answer: If there is a unique key defined on a column which has date as the data type then one can insert the same date more than once.The date is always
stored in the format dd-mon-yyyy hh:mi:ss.The reason wh

78 WHAT ARE THE PRE-REQUISITES TO MODIFY DATATYPE OF A COLUMN AND TO ADD A COLUMN WITH NOT NULL CONSTRAINT?
]
Answer: 1) to modify datatype of a column 2) to add a column with NOT NULL constraint - the table must have 0 rows

79 IS IT POSSIBLE TO USE LONG COLUMNS IN WHERE CLAUSE OR ORDER BY

]
Answer: Not possible

80 WHAT IS CORRELATED SUB-QUERY?

]
Answer: Correlated sub query is a sub query which has reference to the main query.

List of all PROGRAMMATIC CONSTRU Interview Questions

1] WHAT IS A PACKAGE ?

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

2] WHAT IS A PROCEDURE ?

Answer: 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.

3] WHAT IS DIFFERENCE BETWEEN PROCEDURES AND FUNCTIONS ?

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

4] WHAT ARE THE USES OF DATABASE TRIGGER ?

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

5] WHAT ARE THE DIFFERENCES BETWEEN DATABASE TRIGGER AND INTEGRITY CONSTRAINTS ?

Answer: 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 doe

6] WHAT ARE THE ADVANTAGES OF HAVING A PACKAGE ?

Answer: Increased functionality (for example, global package variables can be declared and used by any procedure in the package) and performance (for
example all objects of the package are parsed compiled, an
7] WHAT ARE THE USES OF ORACLE ROLLBACK SEGMENT?

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

8] WHAT ARE ORACLE SCHEMA OBJECTS?

Answer: 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, functi

9] WHAT IS AN ORACLE DATA BLOCK?

Answer: 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

10 WHAT ARE THE DIFFERENT TYPES OF PL/SQL PROGRAM UNITS THAT CAN BE DEFINED AND STORED IN ORACLE DATABASE ?
]
Answer: Procedures and Functions, Packages and Database Triggers.

11 WHAT IS DATABASE TRIGGER ?

]
Answer: 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.

List of all SCENARIOS Interview Questions

1] WHAT IS TESTING SCENARIO ? WHAT IS SCENARIO BASED TESTING? CAN U EXPLAIN WITH AN EXAMPLE ?

Answer: Scenario testing is the real time testing techniques implemented on an application with the presence of certain applied conditions and
environment.In this type of testing the behavior of the applicati

2] TELL ME THE TOTAL PROCESS OF EIM?

Answer: after you get the legacy data thorugh exl format

1. you identify the which base table and which base column suitable for legacy data.

2.after that u have to use control file or dts pro

3] HOW DO YOU INCREASE THE PERFORMANCE OF %LIKE OPERATOR

Answer: The wildcard char % can be placed in one of three ways: %searchwordhere% searchwordhere% %searchwordhere The searchwordhere% is the
fastest because it can use an index if one is specified

4] WHAT IS SPOOLING

Answer: Acronym for simultaneous peripheral operations on-line, spooling refers to putting jobs in a buffer, a special area in memory or on a disk where a
device can access them when it is ready. Spooling is

5] IF THE SQL * PLUS HANGS FOR A LONG TIME, WHAT IS THE REASON

Answer: You are running a cartisian query, typically by mistake. Make sure every table has a join criteria specified for it. You are working on a table with
100+million rows. The database server is bu

6] WHAT ARE THE DIFFERENCES BETWEEN DATABASE DESIGNING AND DATABASE MODELING

Answer: refer http://www.aisintl.com/case/library/R-Theory_vs_ER/r-theory_vs_er.html it says, database modeling comprises: discovery, design,

documentation, communication, DDL generation, re engineering

7] DUAL TABLE EXPLAIN. IS ANY DATA INTERNALLY STORING IN DUAL TABLE. LOT OF USERS ARE ACCESSING SELECT SYSDATE FROM DUAL
AND THEY GETTING SOME MILLISECOND DIFFERENCES. IF WE EXECUTE SELECT SYSDATE FROM EMP; WHAT ERROR WILL WE GET. WHY

Answer: The built-in function SYSDATE returns a DATE value containing the current date and time on your system. DUAL is built-in relation in Oracle which
serves as a dummy relation to put in the FROM clause w

8] ALL THE USERS ARE COMPLAINING THAT THEIR APPLICATION IS HANGING. HOW YOU WILL RESOLVE THIS SITUATION IN OLTP

Answer: If the user is complaining the hang problem ..then the experience of a dba reflects the work style that he is going to perform and basically as the
rule suggest first try to connect to the database it

9] SHALL WE CREATE PROCEDURES TO FETCH MORE THAN ONE RECORD

Answer: You can create a procedure to return REF cursor or VARRAY or PL/SQL Table type out parameters which can return more than one value.

10 IF THE ENTIRE DISK IS CORRUPTED HOW WILL YOU AND WHAT ARE THE STEPS TO RECOVER THE DATABASE
]
Answer: if the entire disk is corrupted and no backup is there don nothing sit and relax their is no possibility of recovery ...a backup is required for restoration
and for recovery redo log and archive logs.

11 SCHEMA A HAS SOME OBJECTS AND CREATED ONE PROCEDURE AND GRANTED TO SCHEMA B. SCHEMA B HAS THE SAME OBJECTS LIKE
] SCHEMA A. SCHEMA B EXECUTED THE PROCEDURE LIKE INSERTING SOME RECORDS. IN THIS CASE WHERE THE DATA WILL BE STORED
WHETHER IN SCHEMA A OR SCHEMA B

Answer: This is an interesting question. So I thought to try it out instead of simply provide a guess. Here is the solution: Schema1 Leo Table Name emp
Procedure Test Schema2 Leo1 Table Nam

12 IF THE LARGE TABLE CONTAINS THOUSANDS OF RECORDS AND THE APPLICATION IS ACCESSING 35% OF THE TABLE WHICH METHOD TO
] USE: INDEX SEARCHING OR FULL TABLE SCAN

Answer: Index is more useful in this situation since it retrive rows faster than fts. fts read all blocks and since table contain thousands or rows .

13 IN EXCEPTION HANDLING WE HAVE SOME NOT_FOUND AND OTHERS. IN INNER LAYER WE HAVE SOME NOT_FOUND AND OTHERS. WHILE
] EXECUTING WHICH ONE WHETHER OUTER LAYER OR INNER LAYER WILL CHECK FIRST

Answer: inner layer. execution carry on furthur without going to outer exception blocks.

14 WHAT IS MUTATED TRIGGER, IS IT THE PROBLEM OF LOCKS. IN SINGLE USER MODE WE GOT MUTATED ERROR, AS A DBA HOW YOU WILL
] RESOLVE IT

Answer: mutated trigger: example:Table A has an insert trigger.In that Trigger: There is a statement like insert into Table A, which caues mutated
trigger.Avoid to have those kind of triggers in the database.

15 WHAT IS BULK SQL

]
Answer: Bulk sql are forall and bulk collect INTO statement, For performence reason the bulk bind is used to eleminate the context switching between two
sql and pl/sql eng.

16 HOW TO DO THE SCHEDULED TASK/JOBS IN UNIX PLATFORM

]
Answer: cron job feature in unix

17 YOU ARE REGULARLY CHANGING THE PACKAGE BODY PART. HOW WILL YOU CREATE OR WHAT WILL YOU DO BEFORE CREATING THAT
] PACKAGE

Answer: Create package specifications before creatin package body.

18 HOW CAN YOU SEE THE SOURCE CODE OF THE PACKAGE
]
Answer: SELECT TEXT FROM USER_SOURCE/ALL_SOURCE/DBA_SOURCE WHERE NAME= AND TYPE='PACKAGE'; HERE THE TYPE MAY BE
PACKAGE,PROCEDURE,....

19 WHAT ARE THE DIFFERENCES YOU HAVE SEEN WHILE INSTALLING ORACLE ON NT AND UNIX PLATFORM
]
Answer: Oracle Server = Oracle Instance + Oracle Database Oracle instance comprises of Background Process and memory structures in Unix all
background processes are treated as independent processes but in win

20 IN WHICH SITUATION WHETHER PEAK TIME OR OFF PEAK TIME YOU WILL EXECUTE THE ANALYZE TABLE COMMAND. WHY
]
Answer: You have to run the analyze command during off peak time only because it actually performs full table scan.

List of all ORACLE ARCHITECTURE

Interview Questions
1] WHAT ARE THE LARGE OBJECT TYPES SUPPORTED BY ORACLE?

Answer: Oracle provides six data types for storing LOBs: CLOB and LONG for large fixed-width character data NCLOB for large fixed-width national
character set data BLOB and LONG RAW for storing un

2] EXPLAIN DIFFERENT TYPES OF SEGMENT. DATA SEGMENT, INDEX SEGMENT, ROLLBACK SEGMENT AND TEMPORARY SEGMENT.

Answer: There are four types of segments used in Oracle databases: - data segments - index segments - rollback segments - temporary segments Data
Segments: There is a single data segment to hold al

3] EXPLAIN THE TYPES OF DATA FILES USED BY THE ORACLE RDBMS.

Answer: Online redo log files A control file contains information such as location of redo log files, backup data and redo log information. The control file is
updated to reflect the structure changes eve

4] WHAT IS THE DIFFERENCE BETWEEN SID AND GLOBAL DATABASE NAME ?

Answer: SID (System Identifier) : A SID (almost) uniquely identifies an instance. Actually, $ORACLE_HOME, $ORACLE_SID and $HOSTNAME identify an
instance uniquely. The SID is 64 characters, or less; at least o

5] WHAT IS THE DIFFERNECE BETWEEN MATERIALIZED VIEW AND SNAPSHOT

Answer: A materialized view is a replica of a target master from a singlepoint in time. The concept was first introduced with Oracle7 termed asSNAPSHOT.
In Oracle release 7.1.6 snapshots were enhanced to enab

6] DESCRIBE ORACLE ARCHITECTURE IN BRIEF.

Answer: The Oracle database has: - Logical layer: The components of the logical layer map the data to these physical components - Physical layer: The
physical layer consists of the files that reside on

7] EXPLAIN SGA MEMORY STRUCTURES: SHARED POOL, DATABASE BUFFER CACHE, REDO LOG CACHE, LARGE POOL JAVA POOL.

Answer: SGA (System Global Area) is a dynamic memory area of an Oracle Server. In SGA,the allocation is done in granuels. The size of the SGA is
dependent on SGA_MAX_SIZE parameter. The memory structures c
8] WHAT IS THE PHYSICAL AND LOGICAL STRUCTURE OF ORACLE?

Answer: Logical Database structures Logical structures include tablespaces, schema objects, data blocks, extents and segments. Tablespaces Database is
logically divided into one or more tablespaces. Ea

9] SGA DEFINITION FILES

Answer: System Global Area The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance.
When an instance starts, the SGA is allocated by Oracle a

10 WHAT IS SQL LOADER? EXPLAIN THE FILES USED BY SQL LOADER TO LOAD FILE. I.E LOADER CONTROL FILE, INPUT DATAFILE, LOG FILE,
] BAD FILE, DISCARD FILE

Answer: SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats,
selective loading, and multi-table loads. When a con

11 EXPLAIN THE AREAS OF MEMORY USED BY ORACLE, I.E. SOFTWARE CODE AREA, SYSTEM GLOBAL AREA (SGA), PROGRAM GLOBAL
] AREA(PGA), SORT AREA.

Answer: Software area code: - It is a protected location that is used to store oracle code that is supposed to be run. The location is different from users'
programs. The software area code is read only and c

12 EXPLAIN THE METHODS PROVIDED BY SQL LOADER.

]
Answer: Conventional Path Load Direct Path Load

13 WHAT IS THE DIFF B/W BTREE INDEX AND BITMAP INDEX

]
Answer: Bitmap indexes are more advantageous than b-tree indexes when the table has millions of rows and the key columns have low cardinality. bitmap
indexes provide better performance than b tree indexes whe

14 WHAT ARE THE CHARACTERISTICS OF DATA FILES

]
Answer: A data file is created when a 'create tablespace' or 'alter tablespace' command is executed. Creating a data file of size 10MB actually allocates
10MB space on the hard disk. If a tablespace is dec

15 EXPLAIN IN BRIEF ORACLE DATABASE OBJECTS.

]
Answer: Tables Oracle stores information in the form of tables. For eg you can have a table named as climate in which you can store information about the
climate of a place in the form of columns which could

16 WHAT DOES A CONTROL FILE CONTAIN ?

]
Answer: The controlfile contains : 1. Name and Location of Datafiles Redo Logfiles. 2. Timestamp of Database Creation. 3. Current log sequence number is
recorded when log switches are occur. 4. Checkpoi

17 WHAT IS THE FUNCTION OF SMON?

]
Answer: The SMON background process performs all system monitoring functions on the oracle database. Each time oracle is re-started, SMON performs a
warm start and makes sure that the transactions that wer

18 PHYSICAL DATABASE STRUCTURE

]
Answer: The physical database structure comprises of datafiles, redo log files and control files Datafiles Datafiles contain database's data. The data of
logical data structures such as tables and indexes

19 EXPLAIN THE CATEGORIES OF ORACLE PROCESSES I.E. USER, DATA WRITING PROCESSES, LOGGING PROCESSES AND MONITORING
] PROCESSES.

Answer: * User process ? User process is used in invocation of application software. * Data writing process - A database writer process is used to write
buffer content into a datafile. They are specifically

20 WHAT ARE THE BACK GROUND PROCESSES IN ORACLE?

]
Answer: There are a number of background processes running. Each process is used for a specific job. * SMON - system monitor process is used to clean
temporary segments and crash recovery of a crashed inst

21 WHAT ARE THE LARGE OBJECT TYPES SUPPORTED BY ORACLE?

]
Answer: Oracle provides six data types for storing LOBs: CLOB and LONG for large fixed-width character data NCLOB for large fixed-width national
character set data BLOB and LONG RAW for storing unstructure

22 WHAT IS THE DIFF B/W BTREE INDEX AND BITMAP INDEX

]
Answer: Btree Index is very usefull for OLTP and BITMAP index are useful for Decision Support System. When OLTP environment higly level DML activites
where doing and it is better to use Btree indexes is bette

23 WHAT IS THE DIFFERENCE BETWEEN MATERIALIZED VIEW AND SNAPSHOT

]
Answer: A materialized view is a replica of a target master from a singlepoint in time. The concept was first introduced with Oracle7 termed asSNAPSHOT.
In Oracle release 7.1.6 snapshots were enhanced to enab

24 WHAT IS THE FUNCTION OF REDO LOG ?

]
Answer: Oracle maintains logs of all transactions against the databse. These transactions are recorded in files called Online Redo log files.These logs are
used to recover the database's transaction in their

25 WHAT IS THE USE OF REDO LOG INFORMATION ?

]
Answer: Oracle maintains logs of all transactions against the databse. These transactions are recorded in files called Online Redo log files.These logs are
used to recover the database's transaction in their

26 WHAT IS A DATA DICTIONARY ?

]
Answer: The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database. It stores
information about both the logical and physical structure

27 DESCRIBE THE DIFFERENT TYPE OF INTEGRITY CONSTRAINTS SUPPORTED BY ORACLE ?

]
Answer: NOT NULL Constraint - Disallows Nulls in a table's column. UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and N

28 WHAT IS SYSTEM TABLESPACE AND WHEN IS IT CREATED?

]
Answer: Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM
tablespace always contains the data dictionary tables for the entire d

List of all CONCEPTS AND ARCHITE

Interview Questions
1] WHAT ARE THE COMPONENTS OF LOGICAL DATABASE STRUCTURE OF ORACLE DATABASE?

Answer: Tablespaces and the Database's Schema Objects.

2] WHAT IS A DATA DICTIONARY ?

Answer: The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database. It stores
information about both the logical and physical structure o

3] WHAT IS A DATA FILE ?

Answer: Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The data of logical database
structures such as tables and indexes is physically store

4] DESCRIBE THE DIFFERENT TYPE OF INTEGRITY CONSTRAINTS SUPPORTED BY ORACLE ?

Answer: NOT NULL Constraint - Disallows Nulls in a table's column. UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and Nulls

5] WHAT IS THE FUNCTION OF REDO LOG ?

Answer: The Primary function of the redo log is to record all changes made to data.

6] CAN AN INTEGRITY CONSTRAINT BE ENFORCED ON A TABLE IF SOME EXISTING TABLE DATA DOES NOT SATISFY THE CONSTRAINT ?

Answer: I think so yes we can enforce integrity constraint on a table if some existing table does not satisfy the constraint by using "ENABLE NOVALIDATE"

7] WHAT IS THE USE OF REDO LOG INFORMATION ?

Answer: The Information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to
a database's data files.

8] CAN OBJECTS OF THE SAME SCHEMA RESIDE IN DIFFERENT TABLESPACES.?

Answer: Yes.

9] WHAT IS PRIVATE DATABASE LINK ?

Answer: Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global
object name in a SQL statement or in the definitio

10 WHAT IS SYSTEM TABLESPACE AND WHEN IS IT CREATED?

]
Answer: Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM
tablespace always contains the data dictionary tables for the entire d

11 EXPLAIN THE RELATIONSHIP AMONG DATABASE, TABLESPACE AND DATA FILE.

]
Answer: Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

12 WHAT IS SELF-REFERENTIAL INTEGRITY CONSTRAINT ?

]
Answer: If a foreign key reference a parent key of the same table is called self-referential integrity constraint.

13 HOW TO DEFINE DATA BLOCK SIZE ?

]
Answer: A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in
ORACLE datablocks. Block size is specified in INIT.ORA file

14 WHAT IS A VIEW ?
]
Answer: A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the
table(s) the view uses.)

15 WHAT IS AN INTEGRITY CONSTRAINS ?

]
Answer: An integrity constraint is a declarative way to define a business rule for a column of a table.

16 WHAT ARE SYNONYMS USED FOR ?

]
Answer: Synonyms are used to : Mask the real name and owner of an object. Provide public access to an object Provide location transparency for tables,
views or program units of a remote database. Simplify th

17 WHAT IS DATA BLOCK ?

]
Answer: 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.

18 WHAT ARE THE REFERENTIAL ACTIONS SUPPORTED BY FOREIGN KEY INTEGRITY CONSTRAINT ?
]
Answer: UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a
referenced row is deleted all associated dependent rows are

19 WHAT ARE SCHEMA OBJECTS ?

]
Answer: Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms,
indexes, clusters, database triggers, procedures, funct

20 WHAT IS SCHEMA?
]
Answer: A schema is collection of database objects of an user and is used synonym of an user. Schema name = user name

21 WHAT IS AN INDEX SEGMENT ?

]
Answer: Each Index has an Index segment that stores all of its data.

22 WHAT IS ROLLBACK SEGMENT ?

]
Answer: A Database contains one or more Rollback Segments to temporarily store "undo" information.

23 WHAT IS A DATA SEGMENT ?

]
Answer: Each Non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment.
The data of every table in the cluster is stored in

24 WHAT ARE THE DIFFERENT TYPE OF SEGMENTS ?

]
Answer: Data Segment, Index Segment, Rollback Segment and Temporary Segment.

25 WHAT IS A TEMPORARY SEGMENT ?

]
Answer: Temporary segments are created by ORACLE when a SQL statement needs a temporary work area to complete execution. When the statement
finishes execution, the temporary segment extents are released to th
26 WHAT IS A SEGMENT ?
]
Answer: A segment is a set of extents allocated for a certain logical structure.

27 WHAT ARE THE TYPE OF SYNONYMS?

]
Answer: There are two types of Synonyms Private and Public.

28 WHAT IS A SEQUENCE ?
]
Answer: A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

29 WHAT ARE THE ADVANTAGES OF VIEWS ?

]
Answer: Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table. Hide data complexity.
Simplify commands for the user. Present the data in a

30 DO VIEW CONTAIN DATA ?

]
Answer: Views do not contain or store data.

31 WHAT ARE THE REFERENTIAL ACTIONS SUPPORTED BY FOREIGN KEY INTEGRITY CONSTRAINT ?
]
Answer: UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a
referenced row is deleted all associated dependent rows are

32 WHAT IS ROW CHAINING ?

]
Answer: In Circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs , the data for the row is stored in
a chain of data block (one or more) reserved

33 WHAT IS A TABLESPACE?
]
Answer: A tablespace is the logical division of the oracle database which include the datafiles with their size and locations in the database. It is of two types
(1)created by oracle database itself at the

34 CAN A VIEW BASED ON ANOTHER VIEW ?

]
Answer: Yes.

35 WHAT ARE THE COMPONENTS OF PHYSICAL DATABASE STRUCTURE OF ORACLE DATABASE?

]
Answer: ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.

36 WHAT IS THE MAXIMUM NUMBER OF CHECK CONSTRAINTS THAT CAN BE DEFINED ON A COLUMN ?
]
Answer: No Limit.

37 DESCRIBE REFERENTIAL INTEGRITY ?

]
Answer: A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns
(the dependent value) matches a value in a colu

38 WHAT IS DIFFERENCE BETWEEN UNIQUE CONSTRAINT AND PRIMARY KEY CONSTRAINT ?

]
Answer: A column defined as unique can contain only null value while a column define as Primary Key cannot contain NULL value.

39 WHAT DOES A CONTROL FILE CONTAIN ?

]
Answer: A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a
database's files and redolog files. Time stamp of databas

40 WHAT ARE THE CHARACTERISTICS OF DATA FILES ?

]
Answer: A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of
database storage called a tablespace.

41 WHAT IS AN EXTENT ?
]
Answer: An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

42 WHAT IS NETWORK DATABASE LINK ?

]
Answer: Network database link is created and managed by a network domain service. A network database link can be used when any user of any database
in the network specifies a global object name in a SQL state

43 WHAT IS PUBLIC DATABASE LINK ?

]
Answer: Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database
specifies a global object name in a SQL statement or obje

44 WHAT ARE THE TYPES OF DATABASE LINKS ?

]
Answer: Private Database Link, Public Database Link & Network Database Link.

45 WHEN CAN HASH CLUSTER USED ?

]
Answer: Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The
resulting hash key value points directly to the are

46 WHAT IS HASH CLUSTER ?

]
Answer: A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key
value are stores together on disk.

47 WHAT IS INDEX CLUSTER ?

]
Answer: A Cluster with an index on the Cluster Key.

48 WHAT IS CLUSTER KEY ?

]
Answer: The related columns of the tables in a cluster is called the Cluster Key.

49 WHAT ARE CLUSTERS ?

]
Answer: Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

50 WHAT IS A PUBLIC SYNONYMS ?

]
Answer: Public synonyms can be accessed by any user on the database.

51 WHAT IS A PRIVATE SYNONYMS ?

]
Answer: A Private Synonyms can be accessed only by the owner.

52 WHAT IS A SYNONYM ?
]
Answer: A synonym is an alias for a table, view, sequence or program unit.

53 WHAT IS TABLE ?
]
Answer: A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored
in rows and columns.

54 WHAT IS THE USE OF CONTROL FILE ?

]
Answer: When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for
database operation to proceed. It is also used in databa

55 WHEN AN INSTANCE OF AN ORACLE DATABASE IS STARTED, ITS CONTROL FILE IS USED TO IDENTIFY THE DATABASE AND REDO LOG FILES
] THAT MUST BE OPENED FOR DATABASE OPERATION TO PROCEED. IT IS ALSO USED IN DATABASE RECOVERY.

Answer: a table space can hold object of different schema if proper privilege is given to schema on which the table space resides.

56 WHAT IS A REDO LOG ?

]
Answer: REDO is the record of changes made to the database. It is used to reapply changes that may have been lost by not being written to disk before
shutdown. When a DML statement runs, the changes are recor
57 WHAT IS AN INDEX ?
]
Answer: An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data
retrieval. Index can be created on one or more columns

58 WHAT ARE THE LIMITATIONS OF A CHECK CONSTRAINT ?

]
Answer: The condition must be a Boolean expression evaluated using the values in the row being inserted or updated and can't contain sub queries,
sequence, the SYSDATE,UID,USER or USERENV SQL functions, or th

59 WHAT IS DATABASE LINK ?

]
Answer: A database link is a named object that describes a "path" from one database to another.

60 HOW ARE INDEXES UPDATE ?

]
Answer: Indexes are automatically maintained and used by ORACLE. Changes to table data are automatically incorporated into all relevant indexes.

List of all PL-SQL Interview Questions

1] WHAT IS DIFFERENCE BETWEEN A PROCEDURE & FUNCTION?

Answer: A FUNCTION is always returns a value using the return statement. A PROCEDURE may return one or more values through parameters or may not
return at all.

2] STATE THE DIFFERENCE BETWEEN IMPLICIT AND EXPLICIT CURSORS.

Answer: Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly
cursors are no need to declare oracle creates and p

3] EXPLAIN ROWID, ROWNUM? WHAT ARE THE PSEUDO COLUMNS WE HAVE?

Answer: ROWID - Hexa decimal number each and every row having unique.Used in searching ROWNUM - It is a integer number also unique for sorting
Normally TOP N Analysys. Other Psudo Column are NEXTVAL,

4] HOW WE CAN CREATE A TABLE IN PL/SQL BLOCK. INSERT RECORDS INTO IT? IS IT POSSIBLE BY SOME PROCEDURE OR FUNCTION?
PLEASE GIVE EXAMPLE...

Answer: CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2) AS l_stmt VARCHAR2(200); BEGIN
DBMS_OUTPUT.put_line('STARTING '); l_stmt := 'create table '|| p_table_name || ' as

5] WHAT IS DIFFERENCE BETWEEN STORED PROCEDURES AND APPLICATION PROCEDURES, STORED FUNCTION AND APPLICATION
FUNCTION?

Answer: Stored procedures are sub programs stored in the database and can be called & execute multiple times where in an application procedure is the
one being used for a particular application same is the wa

6] STATE THE ADVANTAGE AND DISADVANTAGE OF CURSOR?

Answer: Advantage : In pl/sql if you want perform some actions more than one records you should user these cursors only. bye using these cursors you
process the query records. you can easily move the recor
7] NAME THE TABLES WHERE CHARACTERISTICS OF PACKAGE, PROCEDURE AND FUNCTIONS ARE STORED?

Answer: User_objects, User_Source and User_error.

8] WHAT IS RAISE_APPLICATION_ERROR?

Answer: Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-
program or database trigger.

9] EXPLAIN THE USAGE OF WHERE CURRENT OF CLAUSE IN CURSORS?

Answer: WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers

10 WHAT WILL THE OUTPUT FOR THIS CODING? DECLARE CURSOR C1 IS SELECT * FROM EMP FORUPDATE; Z C1%ROWTYPE; BEGIN OPEN C1;
] FETCH C1 INTO Z; COMMIT; FETCH C1 IN TO Z; END;

Answer: By declaring this cursor we can update the table emp through z,means wo not need to write table name for updation,it may be only by "z".

11 1) WHAT IS THE STARTING "ORACLE ERROR NUMBER"? 2) WHAT IS MEANT BY FORWARD DECLARATION IN FUNCTIONS?
]
Answer: One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to
function and procedures also ORACLE ERROR NO s

12 HOW TO DEBUG THE PROCEDURE?

]
Answer: You can use DBMS_OUTPUT oracle supplied package or DBMS_DEBUG pasckage.

13 WHAT IS PL/SQL? WHAT ARE THE ADVANTAGES OF PL/SQL?

]
Answer: PL/SQL(a product of Oracle) is the 'programming language' extension of sql. It is a full-fledged language although it is specially designed for
database centric activities.

14 EXPLAIN HOW PROCEDURES AND FUNCTIONS ARE CALLED IN A PL/SQL BLOCK?

]
Answer: Function can be called from SQL query + explicitly as well e.g 1)select empno,salary,fn_comm(salary)from employee;
2)commision=fn_comm(salary); Procedure can be called from begin-end clause. e.g

15 WHAT IS TRIGGER, CURSOR, FUNCTIONS IN PL-SQL AND WE NEED SAMPLE PROGRAMS ABOUT IT?
]
Answer: Trigger is an event driven PL/SQL block. Event may be any DML transaction. Cursor is a stored select statement for that current session. It will not
be stored in the database, it is a logical compo

16 CAN COMMIT, ROLLBACK, SAVEPOINT BE USED IN DATABASE TRIGGERS? IF YES THAN HOW? IF NO WHY? WITH REASONS
]
Answer: we cannot commit inside a trigger. As we all know that when a dml is complete one can issue a commit. A trigger if created is fired before the dml
completes. so we cannot commit intermediatel

17 HOW MANY TYPES OF DATABASE TRIGGERS CAN BE SPECIFIED ON A TABLE? WHAT ARE THEY?
]
Answer: 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 ea

18 WHAT IS REF CURSOR?

]
Answer: In PL/SQL ,pointer has a datatype REF X where REF-Reference X-class of objects Cursor Variables has a datatype REF-CURSOR where Cursor
Varibales are like pointers which hold the memory location o

19 WHAT WILL HAPPEN AFTER COMMIT STATEMENT?

]
Answer: Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;----- commit; end loop;
end; The cursor having query as SELECT .... FOR UPDAT

20 WHERE THE PRE_DEFINED_EXCEPTIONS ARE STORED?

]
Answer: In the standard package. Procedures, Functions & Packages ;

21 WHAT ARE THE MODES OF PARAMETERS THAT CAN BE PASSED TO A PROCEDURE?

]
Answer: IN parameter is the default mode which acts as a constant inside calling environment.value passed in this parameter can not be changed.OUT
parameter mode is used to pass value from calling environment

22 CAN WE DECLARE A COLUMN HAVING NUMBER DATA TYPE AND ITS SCALE IS LARGER THAN PRICESION EX: COLUMN_NAME NUMBER
] (10,100), COLUMN_NAME NUMBAER (10,-84)

Answer: Yes, we can declare a column with above condition. table created successfully.

23 HOW TO DISABLE MULTIPLE TRIGGERS OF A TABLE AT A TIME?

]

Answer: ALTER TABLE

DISABLE ALL
TRIGGER

24] IN PL/SQL FUNCTIONS WHAT IS USE OF OUT PARAMETER EVEN THOUGH WE HAVE RETURN STATEMENT.

Answer: Without parameters you can get the more than one out values in the calling program. It is recommended not to use out
parameters in functions. If you need more than one out values then use procedures i

25] HOW TO AVOID USING CURSORS? WHAT TO USE INSTEAD OF CURSOR AND IN WHAT CASES TO DO SO?

Answer: just use subquery in for clause ex: for emprec in (select * from emp) loop dbms_output.put_line(emprec.empno); end loop; no
exit statement needed implicit open,fetch,close occurs

26 WHAT IS DIFFERENCE BETWEEN % ROWTYPE AND TYPE RECORD?

]
Answer: % ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns
columns of different table or views and variables. E.g. TYPE r_e

27 WHAT ARE THE RESTRICTIONS ON CURSOR VARIABLES?

]
Answer: Currently, cursor variables are subject to the following restrictions:You cannot declare cursor variables in a package spec. For example, the
following declaration is not allowed:CREATE PACKAGE emp_st

28 HOW WE CAN CREATE A TABLE THROUGH PROCEDURE?

]
Answer: You can create table from procedure using Execute immediate command.

29 IN A DISTRIBUTED DATABASE SYSTEM CAN WE EXECUTE TWO QUERIES SIMULTANEOUSLY? JUSTIFY?

]
Answer: As Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.

30 GIVE THE STRUCTURE OF THE PROCEDURE?

]
Answer: PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;

31 WHAT IS A DATABASE TRIGGER ? NAME SOME USAGES OF DATABASE TRIGGER?

]
Answer: Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events
transparently, Enforce complex business rules Derive column va

32 WHAT ARE TWO VIRTUAL TABLES AVAILABLE DURING DATABASE TRIGGER EXECUTION?
]
Answer: 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

33 IS IT POSSIBLE TO USE TRANSACTION CONTROL STATEMENTS SUCH A ROLLBACK OR COMMIT IN DATABASE TRIGGER? WHY?
]
Answer: 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.

34 WHAT IS A STORED PROCEDURE?

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

35 WHAT ARE TWO PARTS OF PACKAGE?

]
Answer: The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the
packages and local to the schema. Package Body contains actual pr

36 WHAT IS PRAGMA EXECPTION_INIT? EXPLAIN THE USAGE?

]
Answer: 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 err

37 WHAT IS A CURSOR FOR LOOP?

]
Answer: 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. eg. FO

38 WHAT IS DIFFERENCE BETWEEN A CURSOR DECLARED IN A PROCEDURE AND CURSOR DECLARED IN A PACKAGE SPECIFICATION?
]
Answer: A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package. A cursor declared in a
procedure is local to the procedure that can not be a

39 WHAT IS PL/SQL?
]
Answer: PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional
branching.

40 WHAT IS THE BASIC STRUCTURE OF PL/SQL?

]
Answer: PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

41 WHAT ARE THE COMPONENTS OF A PL/SQL BLOCK ?

]
Answer: components of PL/SQL are: declare: (optional) variable declare Begin: (Mandatory)Procedural statement Exception: (optional) error to be trapped
End: (Mandatory) So BEGIN and END a

42 WHAT ARE THE TWO PARTS OF A PROCEDURE ?

]
Answer: Procedure Specification and Procedure Body.

43 WHAT ARE ADVANTAGES OF STORED PROCEDURES

]
Answer: Procedure Specification and Procedure Body.

44 WHAT IS CHARACTER FUNCTIONS?

]
Answer: Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns
results based upon groups of rows rather than one result per row, use

45 WHAT ARE % TYPE AND % ROWTYPE ? WHAT ARE THE ADVANTAGES OF USING THESE OVER DATATYPES?
]
Answer: % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire
row of a table or view or columns selected in the cursor

46 WHAT IS A CURSOR ? WHY CURSOR IS REQUIRED ?

]
Answer: 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.

47 WHAT IS THE USE OF CASCADE CONSTRAINTS?

]
Answer: When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
48 WHAT HAPPENS IF A PROCEDURE THAT UPDATES A COLUMN OF TABLE X IS CALLED IN A DATABASE TRIGGER OF THE SAME TABLE ?
]
Answer: Mutation of table occurs.

49 WHAT IS NVL?
]
Answer: NVL: Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P
& return numeric values. They are MOD, SQRT, ROUND,

50 WRITE THE ORDER OF PRECEDENCE FOR VALIDATION OF A COLUMN IN A TABLE ? I. DONE USING DATABASE TRIGGERS. II. DONE USING
] INTEGARITY CONSTRAINTS.

Answer: First column should be validated by constraints and then Triggers

51 WHAT IS SPOOL?
]
Answer: spool command used for printing the out put of the sql statments in a file. Eg. spool /tmp/sql_out.txt select emp_name, emp_id from emp where
dept='sales'; spool off; we can see the out on /tm

52 WHAT IS ROLLBACK?
]
Answer: Rollback causes work in the current transaction to be undone.

53 WHAT ARE THE CURSOR ATTRIBUTES USED IN PL/SQL ?

]
Answer: %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 -

54 EXPLAIN THE TWO TYPE OF CURSORS ?

]
Answer: There are two types of cursors, Implicit Cursor and Explicit Cursor. PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit
Cursors. They can be declared and used.

55 WHAT IS INTERSECT?
]
Answer: Intersect is the product of two tables listing only the matching rows.

56 WHAT ARE THE DATATYPES A AVAILABLE IN PL/SQL ?

]
Answer: Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD &
TABLE.

57 THE INSERT INTO STATEMENTS IN SQL?

]
Answer: INSERT INTO table_name VALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

58 QUESTION: BELOW IS THE TABLE CITY GENDER NAME DELHI MALE A DELHI FEMALE B MUMBAI MALE C MUMBAI FEMALE D DELHI MALE E I
] WANT THE O/P AS FOLLOWS: MALE FEMALE DELHI 2 1 MUMBAI 1 1 PLEASE HELP ME IN WRITING THE QUERY THAT CAN YIELD THE O/P
MENTIONED ABOVE?

Answer: select city, sum(decode(gender,'male',1,0)) Male_cnt, sum(gender,'female',1,0) female_cnt from table_name group by city

59 WHAT IS SAVEPOINT?
]
Answer: Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.

60 WHAT IS MUTATING SQL TABLE?

]
Answer: Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering
statement might need to read either directly for a

61 WHAT IS SQL*LOADER?
]
Answer: SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database,
two types of input must be provided to SQL*Loader

62 WHAT IS PL/SQL TABLE ?

]
Answer: Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary
PL/SQL tables can have one column and a primary key. Cursors
63 WHAT IS A JOIN?
]
Answer: JOIN is the form of SELECT command that combines info from two or more tables. Types of Joins are Simple (Equijoin & Non-Equijoin), Outer &
Self join. Equijoin returns rows from two or more tables j

64 WHAT IS SYNONYMS?
]
Answer: Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience. Two levels are
Public - created by DBA & accessible to all the users. Priv

65 WHAT IS CONSISTENCY?
]
Answer: Consistency: Assures users that the data they are changing or viewing is not changed until the are thro' with it.

66 WHAT IS SEQUENCES?
]
Answer: Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the
transaction is rolled back, then that sequence number is

67 WHAT IS INDEXES?
]
Answer: Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are
frequent retrieval of fewer than 10-15% of the rows in a

68 GIVE THE STRUCTURE OF THE FUNCTION ?

]
Answer: FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End;

69 WHAT ARE THE PL/SQL STATEMENTS USED IN CURSOR PROCESSING ?

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

70 WHAT IS DATE FUNCTIONS?

]
Answer: Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

71 WHAT IS COLUMN?
]
Answer: COLUMN command defines column headings & format data values.

72 WHAT IS COMPUTE?
]
Answer: Command control computations on subsets created by the BREAK command.

73 WHAT IS COMMIT?
]
Answer: Commit is an event that attempts to make data in the database identical to the data in the form. It involves writing or posting data to the database
and committing data to the database. Forms check th

74 WHAT IS SET TRANSACTION?

]
Answer: Set Transaction is to establish properties for the current transaction.

75 WHAT IS SQL INTEGRITY?

]
Answer: Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum
concurrent access to data. Commit statement releases all locks.

76 HOW TO SORT THE ROWS IN SQL?

]
Answer: Sort the Rows: SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, .. SELECT column1, column2, ... FROM
table_name ORDER BY columnX DESC SELECT column1, column2, ... FROM table_

77 WHAT ARE TTITLE AND BTITLE?

]
Answer: TTITLE & BTITLE are commands to control report headings & footers.

78 WHAT IS SET?
]
Answer: SET command changes the system variables affecting the report environment.
79 WHAT IS UNION?
]
Answer: Union is the product of two or more tables. Which is removed duplicate values from the query.

80 WHAT IS MINUS?
]
Answer: Minus is the product of two tables listing only the non-matching rows.

81 WHAT IS CORRELATED SUB QUERY?

]
Answer: Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update
or Delete. Use CRSQ to answer multipart questions whose

82 WHAT IS MULTIPLE COLUMNS?

]
Answer: Multiple columns can be returned from a Nested Subquery.

83 WHAT IS A DATA TYPE?

]
Answer: Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits. Cannot Query on a long column. Char, Varchar2 Max.
size is 2000 & default is 1 byte. Number(p,s) p is preci

84 WHAT IS TRANSACTION?
]
Answer: Transaction is defined as all changes made to the database between successive commits.

85 WHAT IS POSTING?
]
Answer: Posting is an event that writes Inserts, Updates & Deletes in the forms to the database but not committing these transactions to the database.

86 WHAT IS LOCKING?
]
Answer: Locking are mechanisms intended to prevent destructive interaction between users accessing data. Locks are used to achieve.

87 HOW PACKAGED PROCEDURES AND FUNCTIONS ARE CALLED FROM THE FOLLOWING? A. STORED PROCEDURE OR ANONYMOUS BLOCK B.
] AN APPLICATION PROGRAM SUCH A PRC *C, PRO* COBOL C. SQL *PLUS

Answer: a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE b.
BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCT

88 WHAT IS SQL DEADLOCK?

]
Answer: Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a
resource locked by the second user and the second use

89 WHAT ARE THE RETURN VALUES OF FUNCTIONS SQLCODE AND SQLERRM ?

]
Answer: SQLCODE returns the latest code of the error that has occurred. SQLERRM returns the relevant error message of the SQLCODE.

90 THE MOST IMPORTANT DDL STATEMENTS IN SQL ARE?

]
Answer: CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table
CREATE INDEX - creates an index (search key) DROP INDEX -

91 OPERATORS USED IN SELECT STATEMENTS ARE?

]
Answer: = Equal <> or != Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE
Search for a pattern

92 SELECT STATEMENTS IN SQL?

]
Answer: SELECT column_name(s) FROM table_name SELECT DISTINCT column_name(s) FROM table_name SELECT column FROM table WHERE
column operator value SELECT column FROM table WHERE column LIKE pattern SELECT

93 THE SELECT INTO STATEMENT IS MOST OFTEN USED TO CREATE BACKUP COPIES OF TABLES OR FOR ARCHIVING RECORDS?
]
Answer: SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source SELECT column_name(s) INTO newtable [IN externaldatabase]
FROM source WHERE column_name operator value.

94 THE UPDATE STATEMENT IN SQL.

]
Answer: UPDATE table_name SET column_name = new_value WHERE column_name = some_value
95 WHAT IS THE DELETE STATEMENTS IN SQL?
]
Answer: DELETE FROM table_name WHERE column_name = some_value Delete All Rows: DELETE FROM table_name or DELETE * FROM table_name

96 THE IN OPERATOR MAY BE USED IF YOU KNOW THE EXACT VALUE YOU WANT TO RETURN FOR AT LEAST ONE OF THE COLUMNS.
]
Answer: SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

97 WHAT IS OVERLOADING OF PROCEDURES?

]
Answer: The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters
is called overloading of procedures. e.g. DBMS_OUTPUT pu

98 WHAT IS AN EXCEPTION? WHAT ARE TYPES OF EXCEPTION?

]
Answer: Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS