1) Select Count(1) From Dual;,What Is Output?
Answer : 1
2) How to delete duplicate records from table without Distinct Clause
Answer: Using row id
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vinay',4500);
insert into Employee values(3,'Teja',4500);
insert into Employee values(4,'Ashwin',1100);
insert into Employee values(5,'Raj',45000);
select rowid,id,name,sal from Employee;
select max(rowid) from Employee group by id,name,sal;
delete from Employee where rowid not in ( select max(rowid) from Employee group by
id,name,sal);
3)How To Retrieve Second Highest Salary From Table?
Answer:
select id,name,sal from Employee a where 2=(select count(distinct sal) from Employee b
where a.sal<=b.sal);
4) Diff B/W Primary Key & Unique Key?
Answer:
5) Diff B/W Procedure & Package?
Answer:
A procedure is a stored program in Oracle that is written down when a particular task has to
be done.
A package is a database object that can contain procedures or functions that perform tasks
that are related to each other.
6)Which Is Better Procedure Or Package?
Answer:
Better Performance:
Package: Packages which contains procedures and functions can be compiled and are
loaded into memory at once.
Procedures: Procedures are compiled and loaded separately for each procedure.
7) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?
Answer:
Cursor is a pointer which points result set and retrieve each row at a time from the result
set.
Types of Cursors:
Implicit
Explicit
Implicit cursor:
It used when sql statement return only one row.
It has 4 attributes
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.
Explicit cursor:
It is used when sql statement returns more than one row.
It has also 4 attributes
CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.
9) What Is Exception? Types Of Exceptions?
Answer:
An exception is an error which occurs at run time.
Predefined
User defined
10) What Is Sqlcode And Sqlerr?
Answer:
Sqlcode: Number of the error
Sqlerr: message related to the SQL Code.
11) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?
Answer:
Ref_Cursor:
Reference to a cursor.
It is a datatype in PL/SQL.
Special type of cursor variable.
Ref cursor gets associated with multiple SELECT statements in a single PL/SQL block.
Types:
Strong Ref Cursor: Strong ref cursors have return type and return type should be record
data type.
DECLARE
TYPE cursor_variable_name IS REF CURSOR
RETURN (return type);
Weak Ref Cursor: Do not have any return type
DECLARE
TYPE ref_cursor_name IS REF CURSOR;
Sys_RefCursor
Sys Ref cursor is an Oracle built in cursor variable.
12) How to Give Privilege to One Procedure In Package?
Answer:
GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER
13) What Is %Type & %Rowtype? What Is Main Use?
Answer:
% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.
The advantages are :
1) Need not know about variable's data type.
2) If the database definition of a column in a table changes, the data type of a variable
changes accordingly.
%TYPE is used to declare a field with the same type as that of a specified table's column:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE is used to declare a record with the same types as found in the specified
database table, view or cursor:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
14) How To Improve Performance?
Answer:
Make sure you have appropriate indexes.
Make sure you have up-to-date statistics on all the tables, using the subprograms in the
DBMS_STATS package.
Analyse the execution plans and performance of the SQL statements, using:
EXPLAIN PLAN statement.
15) BULK COLLECT
Answer
This keyword has the functionality of fetching the records in bulk rather than fetching one by
one.
It is a type of collection variable.
16) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?
Answer
BULK COLLECT keyword has the functionality of fetching the records in bulk rather than
fetching one-by-one.
The main advantage of using BULK COLLECT is it increases the
performance by reducing the interaction between database and PL/SQL engine.
LIMIT:
The bulk collect concept loads the entire data into the target collection variable as a bulk.
This size limit can be easily achieved by introducing the ROWNUM condition in the
'SELECT' statement, whereas in the case of cursor this is not possible.
Oracle has provided 'LIMIT' clause that defines the number of records that needs to be
included in the bulkin case of cursor.
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;
FORALL:
The FORALL allows to perform the DML operations on data in bulk.
It is similar to that of FOR loop statement except in FOR loop things happen at the record-
level whereas in FORALL there is no LOOP concept. Instead the entire data present in the
given range is processed at the same time.
17) What Is Merge Statement? Give Me Syntax?
Answer
Oracle MERGE statement selects data from one or more source tables and updates or
inserts it into a target table.
18) What Is View? Use Of View's?
Answer
View is a virtual table which can be derived based on a query
Uses:
Readability and confidentiality
19) Which Will Fire Default First Statement Level Or Row Level Trigger?
Answer:
Oracle runs triggers of each type in a planned firing sequence when more than one trigger
is fired by a single SQL statement. First, statement level triggers are fired, and then row
level triggers are fired.
20) What is functional based index? Write syntax?
Answer:
CREATE INDEX members_last_name_fi ON members(UPPER(last_name));
A function-based index calculates the result of a function that involves one or more columns
and stores that result in the index.
A function-based index speeds up the query by giving the optimiser more chance to
perform an index range scan instead of full index scan.
21) How to update complex view?
Answer:
When can we update a view?
The view must include the PRIMARY KEY of the table based upon which the view has been
created.
It should not have any field made out of aggregate functions.
It must not have any DISTINCT clause in its definition.
It must not have any GROUP BY or HAVING clause in its definition.
It must not have any SUB-QUERIES in its definitions.
If the view you want to update is based upon another view, the later should be updatable.
Any of the selected output fields (of the view) must not use constants, strings or value
expressions.
When can we cannot update a view?
The SELECT statement which is used to create the view should not include the following
GROUP BY clause or ORDER BY clause.
DISTINCT keyword.
NOT NULL values.
An aggregate or analytic function.
A collection expression in a SELECT list.
A sub-query in a SELECT list.
A sub-query designated WITH READ ONLY.
The view should not be created using nested queries or complex queries.
The view should not be updated when it doesn’t contain a key preserved table.
view cannot update if view contains pseudo columns or expressions.
22) Can you alter procedure with in package?
Answer:
All objects in a package are stored as a unit, the ALTER PACKAGE statement
re compiles all package objects. You cannot use the ALTER PROCEDURE statement or
ALTER FUNCTION statement to recompile individually a procedure or function that is part
of a package.
23) Is it possible to open cursor which is in package in another procedure?
Answer:
Yes its possible.
create or replace package pkg_Util is
cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE;
end;
/
--Here is a different package that references the cursor
create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end;
24) What is substr()&instr()?
Answer:
INSTR and SUBSTR are string functions..
Position of a sub string in a main string.
SELECT INSTR('CLUBORACLE','L') RES FROM DUAL;
RES
---
2
Subsring from a main string.
SELECT substr('CLUBORACLE',3,2) RES FROM dual;
RES
----
UB
25) Difference between case and decode?
Answer:
CASE allows you to perform IF-THEN-ELSE logic in your SQL statements, similar to
DECODE.
Difference between CASE and DECODE is
CASE is a statement where as DECODE is a function.
CASE can be used in both SQL and PLSQL . But DECODE can be used only in SQL.
CASE is used in where clause But you cant use DECODE in where clause.
CASE is used in relational operators where as DECODE is used in equality operators.
26) Can you use sysdate in check constraints? If no, why?
Answer:
A check constraint cannot refer a function like SYSDATE.
You can get a different result every time you call it.
27) Difference between column level constraints & table level constraints?
Answer:
Column constraints and table constraints have the same function; the difference is in where
you specify them.
Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE,
CHECK, or FOREIGN KEY constraint definition.
Column-level constraints (except for check constraints) refer to only one column.
28) What is optimiser?
The database can execute a SQL statement in multiple ways, such as full table scans,
index scans, nested loops etc.
The optimiser generates a set of plans for the SQL statement based on available access
paths and hints.
The optimiser estimates the cost of each plan based on statistics in the data dictionary.
The optimiser compares the plans and chooses the plan with the lowest cost.
29) What is pivot operator?
Answer
PIVOT:
Pivot operator transposes an aggregated row of a table into a column.
The distinct row values become the columns in the output and aggregated column value
places itself under the appropriate pivoted column.
30) SQL loader
Answer:
SQL*Loader loads data from an external flat file into a table in the database.
The input data file stores delimited or raw data like CSV, tab-delimited, and pipe-delimited.
The parameter file stores the location of the input/output files.
The control file specifies on how data is loaded.
The parameter file specify the user id that will connect to the Oracle database.
The email.bad file stores invalid data.
The discard file contains records that did not match any record-selection criteria specified in
the control file.
31) What is Raise_application_error.
Answer:
It is a procedure of package DBMS_STANDARD that allows user to raise user_defined
error messages from database trigger or stored sub-program.
32) Explain mutating table error.
Answer:
It occurs when a trigger tries to update a row that it is currently being used. It is resolved by
using views or temporary tables, so database selects one and updates the other.
33) How many triggers can be applied to a table?
Answer:
A maximum of 12 triggers applied to one table
34) Difference between RANK() and Dense_Rank () function.
Answer:
Rank Function provides rank to each value in a column based on the column specified by
order by clause.
Dense Rank function:
Similar to RANK function however the DENSE_RANK function does not skip any ranks
when the ranks are similar in the preceding rows.
35) Can we avoid index by using hint?
Answer:
Yes we can avoid index using no_index optimizer hint. Oracle hints are also called as
optimiser hints.
optimizer hint is a code written with in an sql query which controls or instructs the optimizer.
NO_INDEX(<table_name> < index_name>): Instructs the optimizer to not to use the
named index in determining a plan.
SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename FROM emp, dept WHERE
emp.deptno = dept.deptno;
36) Can we delete primary key of one table without changing foreign key of second
table?
Answer:
No, you cannot delete the primary key from one table without removing all the
dependencies that other tables have on that key.
ON DELETE CASCADE:
When we create a foreign key using this ON DELETE CASCADE, it deletes the
referencing rows in the child table when the referenced row is deleted in the parent table
which has a primary key.
37) How to avoid Mutating error?
Answer:
If we insert a row in table X and a for each trigger executes a query on table x.Oracle
throws errors called mutating error.
Using compound trigger we can avoid this.
38) How to print all dates of particular month in a table (if it is not present in column)
select to_date('01.12.2019','dd.mm.yyyy')+level-1 from dual
connect by level <= TO_CHAR(LAST_DAY(to_date('01.12.2019','dd.mm.yyyy')),'DD')
39) How can we print 1 to 10 numbers in single query.
Answer:
select level from dual connect by level<=10
40) If the view is updated, is it updated in base table.
Answer:
Yes, base table is updated
drop table emp;
create table emp(id int,name varchar2(10),location varchar2(10));
insert into emp(id,name,location) values(1,'vishnu','chennai');
insert into emp(id,name,location) values(2,'priya','US');
drop view v1;
create view v1 as select id,location from emp;
update v1 set location='Bhuva' where id=2;
select * from emp;
1 vishnu chennai
2 priya Bhuva
41) What are collections in Oracle.
Answer:
A collection is an ordered group of elements having the same data type.
There are two types of collection:
1) Persistent.
2) Non Persistent.
Persistent collection are stored physically in database and can be accessed when needed.
where as Non Persistent collection are available only for one session.
PL/SQL provides three collection types.
Persistent collection:
Nested table
Variable-size array or Varray
Non Persistent collection:
Associative array
42) Difference between CHAR,VARCHAR,VARCHAR2
Answer:
VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2
can stores the 4000 bytes of characters.
VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not
occupy the space.
CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded
NULL and more over the VARCHAR2 is variable length.
No storage space is taken for null values.
43) Difference between Raise ,Raise_application_error and pragma exception_init in
oracle.
Answer:
RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-
defined exception. It is also used to raise standard oracle exception. Without showing an
actual message of exception, RAISE will just raise an exception and fails the execution of
the code.
RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an
error with number and user understandable error message. Exception includes both ORA
error and Application error.
The pragma EXCEPTION_INIT associates an exception name with an Oracle error
number.Exception includes ORA error.
44) Example of predefined exception in pl/sql.
Answer:
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
INVALID_CURSOR
CURSOR_ALREADY_OPEN.
45) What are INSTEAD of triggers?
Answer:
The INSTEAD OF triggers are the triggers written especially for modifying views, which
cannot be directly modified through SQL DML statements.
Answer : 1
2) How to delete duplicate records from table without Distinct Clause
Answer: Using row id
drop table Employee;
create table Employee(id int,name varchar2(10),sal int);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vishnu',2300);
insert into Employee values(1,'Vinay',4500);
insert into Employee values(3,'Teja',4500);
insert into Employee values(4,'Ashwin',1100);
insert into Employee values(5,'Raj',45000);
select rowid,id,name,sal from Employee;
select max(rowid) from Employee group by id,name,sal;
delete from Employee where rowid not in ( select max(rowid) from Employee group by
id,name,sal);
3)How To Retrieve Second Highest Salary From Table?
Answer:
select id,name,sal from Employee a where 2=(select count(distinct sal) from Employee b
where a.sal<=b.sal);
4) Diff B/W Primary Key & Unique Key?
Answer:
Primary
Key
|
Unique
Key
|
Unique
+ not null
|
Unique+
one null
|
creates
clustered index
|
creates
non clustered index
|
only
one primary key in a table
|
more
than one unique key in a table.
|
5) Diff B/W Procedure & Package?
Answer:
A procedure is a stored program in Oracle that is written down when a particular task has to
be done.
A package is a database object that can contain procedures or functions that perform tasks
that are related to each other.
6)Which Is Better Procedure Or Package?
Answer:
Better Performance:
Package: Packages which contains procedures and functions can be compiled and are
loaded into memory at once.
Procedures: Procedures are compiled and loaded separately for each procedure.
7) What Is Cursor?Types Of Cursor? And Diff B/W Cursors?
Answer:
Cursor is a pointer which points result set and retrieve each row at a time from the result
set.
Types of Cursors:
Implicit
Explicit
Implicit cursor:
It used when sql statement return only one row.
It has 4 attributes
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN.
Explicit cursor:
It is used when sql statement returns more than one row.
It has also 4 attributes
CUR_NAME%ROWCOUNT
CUR_NAME%FOUND
CUR_NAME%NOTFOUND
CUR_NAME%ISOPEN.
9) What Is Exception? Types Of Exceptions?
Answer:
An exception is an error which occurs at run time.
Predefined
User defined
10) What Is Sqlcode And Sqlerr?
Answer:
Sqlcode: Number of the error
Sqlerr: message related to the SQL Code.
11) What Is Ref_Cursor? Type Of Ref_Cursors And Diff B/W?
Answer:
Ref_Cursor:
Reference to a cursor.
It is a datatype in PL/SQL.
Special type of cursor variable.
Ref cursor gets associated with multiple SELECT statements in a single PL/SQL block.
Types:
Strong Ref Cursor: Strong ref cursors have return type and return type should be record
data type.
DECLARE
TYPE cursor_variable_name IS REF CURSOR
RETURN (return type);
Weak Ref Cursor: Do not have any return type
DECLARE
TYPE ref_cursor_name IS REF CURSOR;
Sys_RefCursor
Sys Ref cursor is an Oracle built in cursor variable.
12) How to Give Privilege to One Procedure In Package?
Answer:
GRANT EXECUTE ON SCHEMA.Package_1.Procedure_1 TO USER
13) What Is %Type & %Rowtype? What Is Main Use?
Answer:
% TYPE is the data type of a variable or a database column to that variable.
% ROWTYPE is the record type that represents a entire row of a table or view or columns
selected in the cursor.
The advantages are :
1) Need not know about variable's data type.
2) If the database definition of a column in a table changes, the data type of a variable
changes accordingly.
%TYPE is used to declare a field with the same type as that of a specified table's column:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE is used to declare a record with the same types as found in the specified
database table, view or cursor:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
14) How To Improve Performance?
Answer:
Make sure you have appropriate indexes.
Make sure you have up-to-date statistics on all the tables, using the subprograms in the
DBMS_STATS package.
Analyse the execution plans and performance of the SQL statements, using:
EXPLAIN PLAN statement.
15) BULK COLLECT
Answer
This keyword has the functionality of fetching the records in bulk rather than fetching one by
one.
It is a type of collection variable.
16) What Is Use Of Limit Clause In Bulk Collect? What Is Forall?
Answer
BULK COLLECT keyword has the functionality of fetching the records in bulk rather than
fetching one-by-one.
The main advantage of using BULK COLLECT is it increases the
performance by reducing the interaction between database and PL/SQL engine.
LIMIT:
The bulk collect concept loads the entire data into the target collection variable as a bulk.
This size limit can be easily achieved by introducing the ROWNUM condition in the
'SELECT' statement, whereas in the case of cursor this is not possible.
Oracle has provided 'LIMIT' clause that defines the number of records that needs to be
included in the bulkin case of cursor.
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;
FORALL:
The FORALL allows to perform the DML operations on data in bulk.
It is similar to that of FOR loop statement except in FOR loop things happen at the record-
level whereas in FORALL there is no LOOP concept. Instead the entire data present in the
given range is processed at the same time.
17) What Is Merge Statement? Give Me Syntax?
Answer
Oracle MERGE statement selects data from one or more source tables and updates or
inserts it into a target table.
18) What Is View? Use Of View's?
Answer
View is a virtual table which can be derived based on a query
Uses:
Readability and confidentiality
19) Which Will Fire Default First Statement Level Or Row Level Trigger?
Answer:
Oracle runs triggers of each type in a planned firing sequence when more than one trigger
is fired by a single SQL statement. First, statement level triggers are fired, and then row
level triggers are fired.
20) What is functional based index? Write syntax?
Answer:
CREATE INDEX members_last_name_fi ON members(UPPER(last_name));
A function-based index calculates the result of a function that involves one or more columns
and stores that result in the index.
A function-based index speeds up the query by giving the optimiser more chance to
perform an index range scan instead of full index scan.
21) How to update complex view?
Answer:
When can we update a view?
The view must include the PRIMARY KEY of the table based upon which the view has been
created.
It should not have any field made out of aggregate functions.
It must not have any DISTINCT clause in its definition.
It must not have any GROUP BY or HAVING clause in its definition.
It must not have any SUB-QUERIES in its definitions.
If the view you want to update is based upon another view, the later should be updatable.
Any of the selected output fields (of the view) must not use constants, strings or value
expressions.
When can we cannot update a view?
The SELECT statement which is used to create the view should not include the following
GROUP BY clause or ORDER BY clause.
DISTINCT keyword.
NOT NULL values.
An aggregate or analytic function.
A collection expression in a SELECT list.
A sub-query in a SELECT list.
A sub-query designated WITH READ ONLY.
The view should not be created using nested queries or complex queries.
The view should not be updated when it doesn’t contain a key preserved table.
view cannot update if view contains pseudo columns or expressions.
22) Can you alter procedure with in package?
Answer:
All objects in a package are stored as a unit, the ALTER PACKAGE statement
re compiles all package objects. You cannot use the ALTER PROCEDURE statement or
ALTER FUNCTION statement to recompile individually a procedure or function that is part
of a package.
23) Is it possible to open cursor which is in package in another procedure?
Answer:
Yes its possible.
create or replace package pkg_Util is
cursor c_emp is select * from employee;
r_emp c_emp%ROWTYPE;
end;
/
--Here is a different package that references the cursor
create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end;
24) What is substr()&instr()?
Answer:
INSTR and SUBSTR are string functions..
Position of a sub string in a main string.
SELECT INSTR('CLUBORACLE','L') RES FROM DUAL;
RES
---
2
Subsring from a main string.
SELECT substr('CLUBORACLE',3,2) RES FROM dual;
RES
----
UB
25) Difference between case and decode?
Answer:
CASE allows you to perform IF-THEN-ELSE logic in your SQL statements, similar to
DECODE.
Difference between CASE and DECODE is
CASE is a statement where as DECODE is a function.
CASE can be used in both SQL and PLSQL . But DECODE can be used only in SQL.
CASE is used in where clause But you cant use DECODE in where clause.
CASE is used in relational operators where as DECODE is used in equality operators.
26) Can you use sysdate in check constraints? If no, why?
Answer:
A check constraint cannot refer a function like SYSDATE.
You can get a different result every time you call it.
27) Difference between column level constraints & table level constraints?
Answer:
Column constraints and table constraints have the same function; the difference is in where
you specify them.
Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE,
CHECK, or FOREIGN KEY constraint definition.
Column-level constraints (except for check constraints) refer to only one column.
28) What is optimiser?
The database can execute a SQL statement in multiple ways, such as full table scans,
index scans, nested loops etc.
The optimiser generates a set of plans for the SQL statement based on available access
paths and hints.
The optimiser estimates the cost of each plan based on statistics in the data dictionary.
The optimiser compares the plans and chooses the plan with the lowest cost.
29) What is pivot operator?
Answer
PIVOT:
Pivot operator transposes an aggregated row of a table into a column.
The distinct row values become the columns in the output and aggregated column value
places itself under the appropriate pivoted column.
30) SQL loader
Answer:
SQL*Loader loads data from an external flat file into a table in the database.
The input data file stores delimited or raw data like CSV, tab-delimited, and pipe-delimited.
The parameter file stores the location of the input/output files.
The control file specifies on how data is loaded.
The parameter file specify the user id that will connect to the Oracle database.
The email.bad file stores invalid data.
The discard file contains records that did not match any record-selection criteria specified in
the control file.
31) What is Raise_application_error.
Answer:
It is a procedure of package DBMS_STANDARD that allows user to raise user_defined
error messages from database trigger or stored sub-program.
32) Explain mutating table error.
Answer:
It occurs when a trigger tries to update a row that it is currently being used. It is resolved by
using views or temporary tables, so database selects one and updates the other.
33) How many triggers can be applied to a table?
Answer:
A maximum of 12 triggers applied to one table
34) Difference between RANK() and Dense_Rank () function.
Answer:
Rank Function provides rank to each value in a column based on the column specified by
order by clause.
Dense Rank function:
Similar to RANK function however the DENSE_RANK function does not skip any ranks
when the ranks are similar in the preceding rows.
35) Can we avoid index by using hint?
Answer:
Yes we can avoid index using no_index optimizer hint. Oracle hints are also called as
optimiser hints.
optimizer hint is a code written with in an sql query which controls or instructs the optimizer.
NO_INDEX(<table_name> < index_name>): Instructs the optimizer to not to use the
named index in determining a plan.
SELECT /*+ NO_INDEX(emp emp_ix) */ empno, ename FROM emp, dept WHERE
emp.deptno = dept.deptno;
36) Can we delete primary key of one table without changing foreign key of second
table?
Answer:
No, you cannot delete the primary key from one table without removing all the
dependencies that other tables have on that key.
ON DELETE CASCADE:
When we create a foreign key using this ON DELETE CASCADE, it deletes the
referencing rows in the child table when the referenced row is deleted in the parent table
which has a primary key.
37) How to avoid Mutating error?
Answer:
If we insert a row in table X and a for each trigger executes a query on table x.Oracle
throws errors called mutating error.
Using compound trigger we can avoid this.
38) How to print all dates of particular month in a table (if it is not present in column)
select to_date('01.12.2019','dd.mm.yyyy')+level-1 from dual
connect by level <= TO_CHAR(LAST_DAY(to_date('01.12.2019','dd.mm.yyyy')),'DD')
39) How can we print 1 to 10 numbers in single query.
Answer:
select level from dual connect by level<=10
40) If the view is updated, is it updated in base table.
Answer:
Yes, base table is updated
drop table emp;
create table emp(id int,name varchar2(10),location varchar2(10));
insert into emp(id,name,location) values(1,'vishnu','chennai');
insert into emp(id,name,location) values(2,'priya','US');
drop view v1;
create view v1 as select id,location from emp;
update v1 set location='Bhuva' where id=2;
select * from emp;
1 vishnu chennai
2 priya Bhuva
41) What are collections in Oracle.
Answer:
A collection is an ordered group of elements having the same data type.
There are two types of collection:
1) Persistent.
2) Non Persistent.
Persistent collection are stored physically in database and can be accessed when needed.
where as Non Persistent collection are available only for one session.
PL/SQL provides three collection types.
Persistent collection:
Nested table
Variable-size array or Varray
Non Persistent collection:
Associative array
42) Difference between CHAR,VARCHAR,VARCHAR2
Answer:
VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2
can stores the 4000 bytes of characters.
VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not
occupy the space.
CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded
NULL and more over the VARCHAR2 is variable length.
No storage space is taken for null values.
43) Difference between Raise ,Raise_application_error and pragma exception_init in
oracle.
Answer:
RAISE is also a standard built-in procedure by Oracle but it is mostly used to raise user-
defined exception. It is also used to raise standard oracle exception. Without showing an
actual message of exception, RAISE will just raise an exception and fails the execution of
the code.
RAISE_APPLICATION_ERROR is a standard built-in procedure by Oracle that raises an
error with number and user understandable error message. Exception includes both ORA
error and Application error.
The pragma EXCEPTION_INIT associates an exception name with an Oracle error
number.Exception includes ORA error.
44) Example of predefined exception in pl/sql.
Answer:
NO_DATA_FOUND
ZERO_DIVIDE
TOO_MANY_ROWS
INVALID_CURSOR
CURSOR_ALREADY_OPEN.
45) What are INSTEAD of triggers?
Answer:
The INSTEAD OF triggers are the triggers written especially for modifying views, which
cannot be directly modified through SQL DML statements.
