Here are the list of most frequently asked Oracle Interview Questions and Answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced Oracle® professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.
representation of one or more tables
Every view has a query attached to it.
same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.
%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different table or views and variables.
Cursor is a named private sql area from where information can be passed and excuted or accessed. Cursors are required to process rows individually for queries returning multiple rows.
For loop cursors.
PL/SQL uses Implicit cursors for queries.
User defined cursors are called explicit cursors.
User using Cursors in for loop the Cursor called for loop Cursor.
Raise_application_error is a procedure of package DBMS_STANDARD. that allows to user_defined error or error code and error messages from stored sub program or database trigger.
A FUNCTION must returns a value using the return statement.
database object that groups logically related procedures.
The advantages of packages are modularity, easier application design, information hiding, reusability and better performance
Truncate is Dml Statements. Cannot be rolled back. Delete allows the filtered deletion.
Deleted records can be rolled back or committed.
Join is a query,retrieves data from 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 – Joining two tables based on conditions other than equating two common columns
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have the corresponding join value in the other table.
Subquery is a query embedded with another Query
Outer Query based on the Inner Query
INSTR(string1, string2 [,n,[m]])
its find the position of the string
SUBSTR(string1, n, m)
SUBSTR returns a character string of size m in string 1, starting from nth position of string1.
MINUS returns all distinct rows selected by the first query but not by the second.
UNION returns all unique rows selected by either query.
UNION ALL returns all rows selected by either query, included all duplicates.
SAVEPOINTS are Transaction control Language.
its used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction.
Maximums of five save points are allowed.
ROWID is a pseudo column attached to each row of a table.
It is 18 characters long, blockno. Rownumber.filenumber are the components of ROWID.
PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.
ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.
CHAR pads spaces to the maximum length. VARCHAR2 does not pad blank spaces.
For CHAR it is 255 and 2000 for VARCHAR2.
The results of a query can be stored into a file by spooling into a file.
Eg. SPOOL filename.
A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its subquery,
unless table B is included in the main query’s FROM clause.
C. A SQL query statement can display data from table B that is referred to in its subquery,without including table B in its own FROM clause.
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.
Which three functions can be used to manipulate character, number, or date column values? (Choose three.)
A. A table can have up to 10,000 columns.
B. The size of a table does NOT need to be specified.
C. A table CANNOT be created while users are using the
D. The structure of a table CANNOT be modified while the table is online.
a) Between … and
b) < >
c) both a and b
d) none of the above.
a) set feedback
b) set verify
c) set confirm
d) none of the above
b) exitting from sqlplus
c) DDL command
e) none of the above
d) none of the above
b) correlated subquery
c) inline query
d) outer query
a) Union all
c) TIMSTAMP with TIME ZONE
d) TIMESTAMP WITH LOCAL TIME ZONE
e) None of the above
A database is a logically collection of data with some inherent meaning.the data’s stored rows and collumns
database are used to store the informations.
select * from sivaemp where salary = (select max(salary) from sivaemp where salary not in (select max(salary) from sivaemp))
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary < =b.salary)
select * from sivaemp a where &n = (select count(*) from sivaemp b where a.salary > =b.salary)
Stored procedure is a set of functionality which is executed when it is explicitly invoked.
FROM EMP E, EMP M WHERE E.MGR=M.EMPNO
GROUP BY M.ENAME HAVING COUNT (E.ENAME) > =ALL
(SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME);
WHERE A.EMPNO < > B.EMPNO AND A.HIREDATE=B.HIREDATE;
WHERE (SYSDATE-HIREDATE)/365 > 10
WHERE MOD (SAL, 2) < > 0;
WHERE E.MGR=M.EMPNO AND E.SAL > M.SAL
GROUP BY JOB HAVING COUNT (*) > 3;
An implicit cursor is user pass single line queries that time a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.
Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.
Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap name=myscript.sql oname=xxxx.yyy
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’,
rc := DBMS_SQL.EXECUTE(cur);
The constructs of a procedure, function or a package are :variables and constants
So that Grants are not dropped.
Yes.You can pass parameters to procedures or functions in a package.
A triggering event or statement
A trigger restriction
A trigger action
There are 12 types of triggers
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.
A trigger giving a SELECT on the table on which the trigger is written.
A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Yes, by adding datafiles to it.
Contains pointers to locations of various data files, redo log files, etc.
It Used by Oracle to store information about various physical and logical structures e.g.Tables
What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
Explicit cursors can take parameters,User create cursor called Explicit Cursor
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
Public Available to all instances
Private Available to specific instance
Cannot drop a field
Cannot rename a field
Cannot manage memory
Procedural Language option not provided
Index on view or index on index not provided
View updation problem
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another
Synonym is Schema Object,just a second name of table,.View can be created with many tables, and with virtual columns and with conditions.
Foreign key is attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
a cursor declare in the package specification without an SQL statement.
The SQL statement for the cursor is attached at runtime from calling procedures.
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.
We Provide Best Oracle Training in Chennai at Besant Technologies with Certification & Placement Assistance.For More Details on Oracle Latest Interview books please contact 9962528293.