Top PL-SQL Interview Question and Answers for 2018
PL/SQL is an advanced version of SQL. There are given a top list of PL/SQL interview questions with answer.
Q.1-What is the basic structure of PL/SQL?
Ans:- Below is the basic structure of PLSQL
Declare
Declaration Section; --Optional
begin
Executable Section; --Mandatory
Exception
Exception Section; --Optional
end;
/
Q.2-What are uses of database Triggers ?
Ans:-We can use Triggers for following purpose
1) Auditing of DML operation on table
2) Applying business rules on table
Q.3-What are benefits of PLSQL package?
Ans:-Below are benefits of PLSQL packages
1) Encapsulation/hiding of business logic
2) Security
3) Performance improvement
4) Re-usability
5) Modular approach
Q.4:-What are the differences between Function and Procedure ?
Ans:- a) Functions are used for calculations while as Procedures are used for implement business logic
b) Function always return a value that is why returning value we can assign to variable but Procedure never return a value.
c) We can call function in select statement while as we can't call procedure in select statement.
d) Call method of procedure and function are different.
Q.5- What is cursor and why it is required ?
Ans:-In PLSQL program we can not use query that return multi-row.
To handle multi-row query in PLSQL program , we use cursor . Cursor is a pointer to memory area that hold the data of query which is attached with cursor.
Q.6- What are cursor attributes ?
Ans:-Below are the cursor attributes
1) %ISOPEN
2) %FOUND
3) %NOTFOUND
4) %ROWCOUNT
Note:-Explicit Cursor attributes are used with cursor name while Implicit cursor attributes are used with "SQL" keyword.
Example:- If c1 is the name of cursor then c1%isopen for Explicit and for Implicit sql%isopen.
Q.7- What is Ref Cursor ?
Ans:-Ref Cursor is a type that can hold a query. We can use variable of this type for assigning multiple queries.
Declaration of REF CURSOR:-
Strong REF CURSOR:- typeis ref cursor return ;
Weak REF CURSOR:- typeis ref cursor ;
Example :- type rc is ref cursor return number;
type rc1 is ref cursor ;
Assigning REF CURSOR type to variable:-
;
Example :- As rc & rc1 are the ref_cursor_type so these type can be assign to variable
rc_rec rc;
rc1_rec rc1 ;
Q.8- What are Collection Type in PLSQL ?
Ans:- Below Collection types are available in PLSQL
1) PLSQL Table or Associative Array
2) Nested Table
3) Varray or variable array
Q.9- What is PLSQL Table ?
Ans:-PLSQL Table is a type of Collection and is also known as Associative Array.
Q.10- What are Collection methods ?
Ans:- Below are the collection method
COUNT
DELETE
EXISTS
EXISTS(n)
EXTEND
FIRST
LAST
LIMIT
NEXT
PRIOR
PRIOR(n)
TRIM
Q.11- What is mutating table error ?
Ans:-When a trigger is going to perform DML operation on table where another DML is going on then trigger will not be able to perform action on that table and return with mutating table error.
Q.11- How can we resolve mutating table error ?
Ans:-There are different ways to handle mutating table error, some are below
1) Change row level trigger to statement level trigger
2) Make trigger to Autonomous Transaction.
3) Change before trigger to after trigger .
Q.12- What is Autonomous Transaction ?
Ans:- Autonomous Transaction is a independent transaction from calling environment means what you perform outside from autonomous transaction that does not affect to autonomous transaction.
Note :- We can make procedure and trigger to autonomous transaction by declaring "pragma autonomous_transaction" in declare section.
Q.13-What are different methods to trace the PL/SQL code ?
Ans:-Below are the different methods for tracing of PLSQL code
a) DBMS_APPLICATION_INFO
b) DBMS_TRACE
c) DBMS_SESSION
d) DBMS_MONITOR
e) trace and tkprof utilities
Q.14- What is Exception and what are types of Exception in PLSQL ?
Ans:- Exception is an error condition during a program execution. PL/SQL supports programmers to handle such conditions using EXCEPTION block in the program and take an appropriate action against the error condition.
Types of exception are
1) Pre defined Exception
2) User defined Exception
3) Un-named Exception
Q.15- What is the difference between "raise" and "raise_application_error"?
Ans:-raise:- raise statement use to raise a user defined exception, declared in declaration section of the pl/sql block,
Example:-
raise exception_name;
raise_application_error:-raise_application_error is use to display user defined error code and message for any error condition.
Example:-
RAISE_APPLICATION_ERROR(-20999, 'Eror_message');
Q.16- What are predefined Exceptions ?
Ans:-Below are the predefined Exceptions
1) ACCESS_INTO_NULL ORA-06530
2) CASE_NOT_FOUND ORA-06592
3) COLLECTION_IS_NULL ORA-06531
4) CURSOR_ALREADY_OPEN ORA-06511
5) DUP_VAL_ON_INDEX ORA-00001
6) INVALID_CURSOR ORA-01001
7) INVALID_NUMBER ORA-01722
8) LOGIN_DENIED ORA-01017
9) NO_DATA_FOUND ORA-01403
10) NOT_LOGGED_ON ORA-01012
11) PROGRAM_ERROR ORA-06501
12) ROWTYPE_MISMATCH ORA-06504
13) SELF_IS_NULL ORA-30625
14) STORAGE_ERROR ORA-06500
15) SUBSCRIPT_BEYOND_COUNT ORA-06533
16) SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
17) SYS_INVALID_ROWID ORA-01410
18) TIMEOUT_ON_RESOURCE ORA-00051
19) TOO_MANY_ROWS ORA-01422
20) VALUE_ERROR ORA-06502
21) ZERO_DIVIDE ORA-01476
Q.17- What is the difference between %type and %rowtype ?
Ans:- %type take a reference for datatype from one column while %rowtype take a reference for datatype of record.
Example:- vsal emp.sal%type;
emp_rec emp%rowtype;
Q.18- What are Cursor Exceptions ?
Ans:- Below are the Cursor Exceptions
a) cursor_already_open
b) invalid_cursor
Q.19-Tell me some predefined packages
Ans:- Below are the pre defined Oracle supplied packages
DBMS_OUTPUT, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT,
DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
Q.20- What is the difference between formal parameter and actual parameter ?
Ans:- Formal parameter:-Formal parameters are the parameters which are used on the time of definition/Creation of the procedure/function.
Actual parameter:-Actual parameters are the parameters which are used on the time of calling of the procedure/function.
Q.21-What is the maximum number of triggers can apply on a single table?
Ans:- 12
Ans:- Below is the basic structure of PLSQL
Declare
Declaration Section; --Optional
begin
Executable Section; --Mandatory
Exception
Exception Section; --Optional
end;
/
Q.2-What are uses of database Triggers ?
Ans:-We can use Triggers for following purpose
1) Auditing of DML operation on table
2) Applying business rules on table
Q.3-What are benefits of PLSQL package?
Ans:-Below are benefits of PLSQL packages
1) Encapsulation/hiding of business logic
2) Security
3) Performance improvement
4) Re-usability
5) Modular approach
Q.4:-What are the differences between Function and Procedure ?
Ans:- a) Functions are used for calculations while as Procedures are used for implement business logic
b) Function always return a value that is why returning value we can assign to variable but Procedure never return a value.
c) We can call function in select statement while as we can't call procedure in select statement.
d) Call method of procedure and function are different.
Q.5- What is cursor and why it is required ?
Ans:-In PLSQL program we can not use query that return multi-row.
To handle multi-row query in PLSQL program , we use cursor . Cursor is a pointer to memory area that hold the data of query which is attached with cursor.
Q.6- What are cursor attributes ?
Ans:-Below are the cursor attributes
1) %ISOPEN
2) %FOUND
3) %NOTFOUND
4) %ROWCOUNT
Note:-Explicit Cursor attributes are used with cursor name while Implicit cursor attributes are used with "SQL" keyword.
Example:- If c1 is the name of cursor then c1%isopen for Explicit and for Implicit sql%isopen.
Q.7- What is Ref Cursor ?
Ans:-Ref Cursor is a type that can hold a query. We can use variable of this type for assigning multiple queries.
Declaration of REF CURSOR:-
Strong REF CURSOR:- type
Weak REF CURSOR:- type
Example :- type rc is ref cursor return number;
type rc1 is ref cursor ;
Assigning REF CURSOR type to variable:-
Example :- As rc & rc1 are the ref_cursor_type so these type can be assign to variable
rc_rec rc;
rc1_rec rc1 ;
Q.8- What are Collection Type in PLSQL ?
Ans:- Below Collection types are available in PLSQL
1) PLSQL Table or Associative Array
2) Nested Table
3) Varray or variable array
Q.9- What is PLSQL Table ?
Ans:-PLSQL Table is a type of Collection and is also known as Associative Array.
Q.10- What are Collection methods ?
Ans:- Below are the collection method
COUNT
DELETE
EXISTS
EXISTS(n)
EXTEND
FIRST
LAST
LIMIT
NEXT
PRIOR
PRIOR(n)
TRIM
Q.11- What is mutating table error ?
Ans:-When a trigger is going to perform DML operation on table where another DML is going on then trigger will not be able to perform action on that table and return with mutating table error.
Q.11- How can we resolve mutating table error ?
Ans:-There are different ways to handle mutating table error, some are below
1) Change row level trigger to statement level trigger
2) Make trigger to Autonomous Transaction.
3) Change before trigger to after trigger .
Q.12- What is Autonomous Transaction ?
Ans:- Autonomous Transaction is a independent transaction from calling environment means what you perform outside from autonomous transaction that does not affect to autonomous transaction.
Note :- We can make procedure and trigger to autonomous transaction by declaring "pragma autonomous_transaction" in declare section.
Q.13-What are different methods to trace the PL/SQL code ?
Ans:-Below are the different methods for tracing of PLSQL code
a) DBMS_APPLICATION_INFO
b) DBMS_TRACE
c) DBMS_SESSION
d) DBMS_MONITOR
e) trace and tkprof utilities
Q.14- What is Exception and what are types of Exception in PLSQL ?
Ans:- Exception is an error condition during a program execution. PL/SQL supports programmers to handle such conditions using EXCEPTION block in the program and take an appropriate action against the error condition.
Types of exception are
1) Pre defined Exception
2) User defined Exception
3) Un-named Exception
Q.15- What is the difference between "raise" and "raise_application_error"?
Ans:-raise:- raise statement use to raise a user defined exception, declared in declaration section of the pl/sql block,
Example:-
raise exception_name;
raise_application_error:-raise_application_error is use to display user defined error code and message for any error condition.
Example:-
RAISE_APPLICATION_ERROR(-20999, 'Eror_message');
Q.16- What are predefined Exceptions ?
Ans:-Below are the predefined Exceptions
1) ACCESS_INTO_NULL ORA-06530
2) CASE_NOT_FOUND ORA-06592
3) COLLECTION_IS_NULL ORA-06531
4) CURSOR_ALREADY_OPEN ORA-06511
5) DUP_VAL_ON_INDEX ORA-00001
6) INVALID_CURSOR ORA-01001
7) INVALID_NUMBER ORA-01722
8) LOGIN_DENIED ORA-01017
9) NO_DATA_FOUND ORA-01403
10) NOT_LOGGED_ON ORA-01012
11) PROGRAM_ERROR ORA-06501
12) ROWTYPE_MISMATCH ORA-06504
13) SELF_IS_NULL ORA-30625
14) STORAGE_ERROR ORA-06500
15) SUBSCRIPT_BEYOND_COUNT ORA-06533
16) SUBSCRIPT_OUTSIDE_LIMIT ORA-06532
17) SYS_INVALID_ROWID ORA-01410
18) TIMEOUT_ON_RESOURCE ORA-00051
19) TOO_MANY_ROWS ORA-01422
20) VALUE_ERROR ORA-06502
21) ZERO_DIVIDE ORA-01476
Q.17- What is the difference between %type and %rowtype ?
Ans:- %type take a reference for datatype from one column while %rowtype take a reference for datatype of record.
Example:- vsal emp.sal%type;
emp_rec emp%rowtype;
Q.18- What are Cursor Exceptions ?
Ans:- Below are the Cursor Exceptions
a) cursor_already_open
b) invalid_cursor
Q.19-Tell me some predefined packages
Ans:- Below are the pre defined Oracle supplied packages
DBMS_OUTPUT, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT,
DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
Q.20- What is the difference between formal parameter and actual parameter ?
Ans:- Formal parameter:-Formal parameters are the parameters which are used on the time of definition/Creation of the procedure/function.
Actual parameter:-Actual parameters are the parameters which are used on the time of calling of the procedure/function.
Q.21-What is the maximum number of triggers can apply on a single table?
Ans:- 12
Q.22- Write a unique difference between a function and a stored procedure.
A function returns a value while a stored procedure doesn?t return a value.
Q.23-How exception is different from error?
Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.
Q.24- What is the main reason behind using an index?
Faster access of data blocks in the table.
Q.25-What are PL/SQL exceptions? Tell me any three.
- Too_many_rows
- No_Data_Found
- Value_error
- Zero_error etc.
Q.26-What are some predefined exceptions in PL/SQL?
A list of predefined exceptions in PL/SQL:
- DUP_VAL_ON_INDEX
- ZERO_DIVIDE
- NO_DATA_FOUND
- TOO_MANY_ROWS
- CURSOR_ALREADY_OPEN
- INVALID_NUMBER
- INVALID_CURSOR
- PROGRAM_ERROR
- TIMEOUT _ON_RESOURCE
- STORAGE_ERROR
- LOGON_DENIED
- VALUE_ERROR
- etc.
Q.27-What is a trigger in PL/SQL?
A trigger is a PL/SQL program which is stored in the database. It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.
Q.28-What is the maximum number of triggers, you can apply on a single table?
12 triggers.
Q.29-How many types of triggers exist in PL/SQL?
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
- BEFORE ALL ROW INSERT
- AFTER ALL ROW INSERT
- BEFORE INSERT
- AFTER INSERT etc.
Q.30-What is the difference between execution of triggers and stored procedures?
A trigger is automatically executed without any action required by the user, while, a stored procedure is explicitly invoked by the user.
Q.31-What happens when a trigger is associated to a view?
When a trigger is associated to a view, the base table triggers are normally enabled.
Q.32-What is the usage of WHEN clause in trigger?
A WHEN clause specifies the condition that must be true for the trigger to be triggered.
Q.33-How to disable a trigger name update_salary?
ALTER TRIGGER update_salary DISABLE;
Q.34-Which command is used to delete a trigger?
DROP TRIGGER command.
Q.35-what are the two virtual tables available at the time of database trigger execution?
Table columns are referred as THEN.column_name and NOW.column_name.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
Q.36-What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked and parameterized.
Q.37-What are the different schemas objects that can be created using PL/SQL?
- Stored procedures and functions
- Packages
- Triggers
- Cursors
Q.38- What do you know by PL/SQL Cursors?
Oracle uses workspaces to execute the SQL commands. When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. This area is identified by the cursor. It allows programmers to name this area and access it?s information.
Q.39- What is the difference between the implicit and explicit cursors?
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.
Explicit cursor is created for queries returning multiple rows.
Q.40-What will you get by the cursor attribute SQL%ROWCOUNT?
The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
Q.41-What will you get by the cursor attribute SQL%FOUND?
It returns the Boolean value TRUE if at least one row was processed.
Q.42- What will you get by the cursor attribute SQL%NOTFOUND?
It returns the Boolean value TRUE if no rows were processed.
Q.43- What do you understand by PL/SQL packages?
A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.
Q.44-What are the two different parts of the PL/SQL packages?
PL/SQL packages have the following two parts:
Specification part: It specifies the part where the interface to the application is defined.
Body part: This part specifies where the implementation of the specification is defined.
Q.45-Which command is used to delete a package?
The DROP PACKAGE command is used to delete a package.
Q.46-What are the advantages of stored procedure?
Modularity, extensibility, reusability, Maintainability and one time compilation.
Q.47-What are the cursor attributes used in PL/SQL?
%ISOPEN: it checks whether the cursor is open or not.
%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether cursor has fetched any row. If yes - TRUE.
%NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.
Q.48-What is the difference between syntax error and runtime error?
A syntax error can be easily detected by a PL/SQL compiler. For example: incorrect spelling etc. while, a runtime error is handled with the help of exception-handling section in a PL/SQL block. For example: SELECT INTO statement, which does not return any rows.
Q.49-Explain the Commit statement.
Following conditions are true for the Commit statement:
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
Q.50-Explain the Rollback statement?
The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement:
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
Q.50-Explain the SAVEPOINT statement.
With SAVEPOINT, only part of transaction can be undone.
Q.51-What is mutating table error?
Mutating table error is occurred when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables.
Q.52-What is consistency?
Consistency simply means that each user sees the consistent view of the data.
Consider an example: there are two users A and B. A transfers money to B's account. Here the changes are updated in A's account (debit) but until it will be updated to B's account (credit), till then other users can't see the debit of A's account. After the debit of A and credit of B, one can see the updates. That?s consistency.
Q.53-What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed.
A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
Q.54-How many types of cursors are available in PL/SQL?
There are two types of cursors in PL/SQL.
- Implicit cursor, and
- explicit cursor
0 comments:
Post a Comment