Explain the difference between trigger and stored procedure.
Latest answer: Trigger in act which is performed automatically before or after a event occur. Stored procedure is a
set of functionality which is executed when it is explicitly invoked.................
Read answer
Explain Row level and statement level trigger.
Latest answer: Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If
statement doesn’t affect any row, no trigger action happens................
Read answer
Write a PL/SQL program for a trigger.
Latest answer: PL/SQL program for tracking operation on a emp table
Create or Replace Trigger EmpTracking
Before Insert or Delete or Update on Emp..................
Read answer
Advantage of a stored procedure over a database trigger.
Latest answer: Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the
program. Triggers are similar to stored procedure except it is executed automatically when any operations are
occurred on the table..................
Read answer
What are cascading triggers?
Latest answer: A Trigger that contains statements which cause invoking of other Triggers are known as cascading
triggers. Here’s the order of execution of statements in case of cascading triggers:................
Read answer
What is a JOIN? Explain types of JOIN in oracle.
Latest 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 tobe queries......................
Read answer
What is a join, explain the types of joins?
Latest answer: A join is a query that extracts data from two or more tables, views or snapshots...........
Read answer
Explain the types of joins.
Latest answer: Types of joins are: Equijoins, Non-equijoins, self join, outer join. A Join is used to create query using
two or more tables and views..........
Read answer
What is object data type in oracle?
Latest answer: Object data type is created using object type in oracle. The object data type can only be created
using SQL data type such as Number, Varchar 2, Date etc.................
Read answer
What is composite data type?
Latest answer: Collections are usually referred to as Composite Data types. Composite type is one that has
components in it. A variable of a composite type contains one or more scalar types................
Read answer
Differences between CHAR and NCHAR in Oracle.
Latest answer: CHAR and NCHAR are character data types which and have a fixed length. However, CHAR has a
specified size in bytes by default and NCHAR has a size specified in characters by default................
Read answer
Differences between CHAR and VARCHAR2 in Oracle.
Latest answer: CHAR values have a fixed length. They are padded with space characters to match the specified
length...........
Read answer
Differences between DATE and TIMESTAMP in Oracle
Latest answer: TIMESTAMP and DATE vary in formats as follows: DATE stores values as century, year, month,
date, hour, minute, and second............
Read answer
Define CLOB and NCLOB datatypes.
Latest answer: Both CLOB and NCLOB are used to store huge character data in the database, CLOBs store single-
byte character set data............
Read answer
What is the BFILE datatypes?
Latest answer: The BFILE datatype is used to store unstructured binary data outside the database. The column of
BFILE type stores file locator that points the OS file which actually stores data...........
Read answer
What is Varrays?
Latest answer: Varrays, variable length array is quite similar to array of C++ or Java. It has fixed upper-bound size
that has to be specified while it is declared..........
Read answer
What are LOB datatypes?
Latest answer: The LOB datatypes such as BLOB, CLOB, NCLOB and BFile can store large blocks of unstructured
data such as graphics, image, video clips etc............
Read answer
What is a cursor? What are its types?
Latest answer: Oracle engine uses private working area to process queries. This work area is called as cursor. The
data that is stored in the cursor is called as Active Data Set.............
Read answer
Explain the attributes of implicit cursor
Latest answer: There are four attributes of implicit cursor in oracle: SQL%IsOPEN - Implicit cursor always returns
FALSE as it gets closed automatically...........
Read answer
Explain the attributes of explicit cursor.
Latest answer: There are four attributes of explicit cursor in oracle. %IsOPEN - This evaluates TRUE when cursor is
open else FALSE..........
Read answer
What is the ref cursor in Oracle?
Latest answer: Cursor is a reference type in oracle. We can allocate different storage locations to the cursor when
the program runs............
Read answer
What are the drawbacks of a cursor?
Latest answer: Implicit cursors are less efficient than explicit cursors, Implicit cursors are more vulnerable to data
errors..................
Read answer
What is a cursor variable?
Latest answer: A cursor variable is capable to get associated with different SELECT statements at run time. It is a
reference type which is quite similar to pointer in C. In order to use cursor variable...........
Read answer
What is implicit cursor in Oracle?
Latest answer: An implicit cursor is a cursor which is internally created by Oracle, It is created by Oracle for each
individual SQL...........
Read answer
Can you pass a parameter to a cursor? Explain with an explain
Latest answer: Yes, explicit cursors can take parameters...........
Read answer
What is a package cursor?
Latest answer: In a package cursor, the SQL statement for the cursor is attached dynamically at runtime from calling
procedures............
Read answer
Explain why cursor variables are easier to use than cursors.
Latest answer: They are easier to define as there is no need to specify a query statement, The query can also be
specified dynamically at the opening time...........
Read answer
What is locking, advantages of locking and types of locking in oracle?
Latest answer: Locking protect table when several users are accessing the same table. Locking is a concurrency
control technique in oracle. It helps in data integrity while allowing maximum..........
Read answer
What are transaction isolation levels supported by Oracle?
Latest answer: READ COMMITTED: If row locks are obtained by a certain transaction, then any other transaction
that contains DML needs to wait until the row locks have been released by that particular transaction.............
Read answer
Explain how to view existing locks on the database.
Latest 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............
Read answer
Explain how to lock and unlock a user account in Oracle.
Latest answer: SQL> ALTER USER user_name ACCOUNT LOCK; SQL> ALTER USER user_name ACCOUNT
UNLOCK;
Read answer
What are background processes in oracle?
Latest answer: Oracle uses background process to increase performance : Database writer, DBWn, Log Writer,
LGWR, Checkpoint, CKPT, System Monitor, SMON, Process Monitor, PMON, Archiver, ARCn...........
Read answer
What is SQL*Loader?
Latest answer: It is a database tool that allows data to be loaded from external files into database tables, It is
available as part of the free Oracle 10g Expression Edition..........
Read answer
What is a SQL*Loader Control File?
Latest answer: A SQL*Loader control file contains the following specification: location of the input data file, format of
the input date file, target table where the data should be loaded............
Read answer
Explain oracle memory structures.
Latest answer: Two memory area: System global area(SGA), Program Global Area(PGA)..........
Read answer
What is Program Global Area (PGA)?
Latest answer: The PGA is a memory area that contains data and control information for the Oracle server
processes. This area consists of the following components:.............
Read answer
What is a shared pool?
Latest answer: It is the area in SGA that allows sharing of parsed SQL statements among concurrent users...........
Read answer
What is snapshot in oracle?
Latest answer: A recent copy of a table or a subset of rows or cols of a table is called as snapshot in oracle. A
snapshot is more useful in distributed computing environment. We can create..........
Read answer
What is a synonym?
Latest answer: Synonym simplifies the use of the table, the table for which synonym is created can be referred by
synonym name............
Read answer
What is a schema?
Latest answer: Schema represents structure of the database. Database has two main types of schemas partitioned :
Physical schema: Describes the database design at the physical level.............
Read answer
Explain how to list all indexes in your schema.
Latest answer: The list of all indexes in a schema can be obtained through the USER_INDEXES view with a
SELECT statement:...........
Read answer
What are Schema Objects?
Latest answer: Schema objects are the logical database structure that represents database's data. Schema objects
include tables, views, sequences, synonyms, indexes, clusters, database............
Read answer
What is an Archiver?
Latest 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.............
Read answer
What is a sequence in oracle?
Latest answer: A Sequence is a user created database object. A sequence can be shared by multiple users to
generate unique integers. This object is used to create a primary key value...............
Read answer
Difference between a hot backup and a cold backup
Latest answer: Cold Backup : In this type of backup, after the database is shut down, DBA exits the SVRMGR utility
and copies the log files, data files and control files onto a backup media...........
Read answer
How to retrieve 5th highest sal from emp table?
SELECT DISTINCT (emp1.sal) FROM EMP emp1 WHERE &N = (SELECT COUNT (DISTINCT (emp2.sal))..........
Read answer
What is $FLEX$ and $PROFILES$? Where are they used?
$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..........
Read answer
How to call a trigger inside a stored procedure?
A trigger cannot be called within a stored procedure. Triggers are executed automatically as a result of DML or DDl
commands............
Read answer
What is WATER MARK IN oracle? Explain the significance of High water mark.
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...........
Read answer
What is an object groups?
Object group is a container for a group of objects..........
Read answer
Difference between clustering and mirroring
Clustering means one than one database server configured for the same user connection. When users connect, one
of the server’s responds..........
Read answer
Difference between paging and fragmentation
Paging is a concept occurring in memory, whereas, Fragmentation occurs on disk level..........
Read answer
Can you explain how to insert an image in table in oracle?
Insert image into a table: Create the following table: create table pics_table (bfile_id number,.........
Read answer
How to find out second largest value in the table?
SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP.........
Read answer
Disadvantage of user defined function in oracle.
Disadvantage of UDF in Oracle: Oracle does not support calling UDFs with Boolean parameters or return types..........
Read answer
Explain the significance of cluster table or non cluster table.
A Cluster provides an alternate method of storing table data. It is made up of a group of tables that share the same
data.........
Read answer
Business object interview questions
Latest answer: Differentiate between Data Mining and Data warehousing. What is Data purging? What are CUBES?
What are OLAP and OLTP? What are the different problems that “Data mining” can solve? What are different stages
of “Data mining”?..................... .
Read answer
OLAP interview questions
Latest answer: Explain the concepts and capabilities of OLAP. Explain the functionality of OLAP. What are MOLAP
and ROLAP? Explain the role of bitmap indexes to solve aggregation problems. Explain the encoding technique used
in bitmaps indexes. What is Binning? What is candidate check?..................
Read answer
