Technical Interview Questions Answers
What is the purpose of the PL/SQL language?
PL/SQL is an extension of SQL. SQL is non-procedural. PL/SQL is a procedural language designed by oracle to overcome the limitations that exist in SQL. The purpose of PL/SQL is to combine database language and procedural programming language. The basic unit in PL/SQL is called a block and is made up of three parts: a declarative part, an executable part and an exception-building part.
Say True or False. If False, explain why.
Routines written in PL/SQL can be called in Oracle call interface, Java, Pro*C/C++, COBOL etc.
True.
Say True or False. If False, explain why.
PL/SQL does not have data types or variables.
False. PL/SQL has all features of a structured programming language including data types, variables, subroutines, modules and procedural constructs.
State few notable characteristics of PL/SQL.
Block-structured language. Stored procedures help better sharing of application. Portable to all environments that support Oracle. Integration with the Oracle data dictionary.
Name few schema objects that can be created using PL/SQL?
Stored procedures and functions Packages Triggers Cursors
State some features or programming constructs supported by PL/SQL.
Variables and constants Embedded SQL support Flow control Cursor management Exception handling Stored procedures and packages Triggers
What are the three basic sections of a PL/SQL block?
Declaration section Execution section Exception section
What is wrong in the following assignment statement?
balance = balance + 2000;
Use of wrong assignment operator. The correct syntax is: balance := balance + 2000;
Write a single statement that concatenates the words ‘Hello’ and ‘World’ and assign it in a variable named greeting.
greeting := ‘Hello’ || ‘World’;
Which operator has the highest precedence among the following −
AND, NOT, OR?
NOT
Which of the following operator has the lowest precedence among the following −
**, OR, NULL ?
OR
What does the colon sign (: ) implies in the following statement?
:deficit := balance – loan;
The colon (: )sign implies that the variable :deficit is an external variable.
What is the purpose of %type data type? Explain with example.
It assigns a variable the same data type used by the column, for which the variable is created. For example, dcode := dept.detpno%type; The variable dcode is created with the same data type as that of the deptno column of the dept table.
What is the purpose of %rowtype data type? Explain with example.
It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable. For example emptype := emp%rowtype; name := emptype.empname;
What is a PL/SQL package?
A package is a file that groups functions, cursors, stored procedures, and variables in one place.
What is a trigger?
A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.
What are the PL/SQL cursors?
Oracle uses workspaces to execute the SQL commands. In other words, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this area and access it’s information. The 'Cursor' is the PL/SQL construct that allows the user to name the work area and access the stored information in it. The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time.
Say True or False. If False, explain why.
PL/SQL engine is part of Oracle Server.
True.
Say True or False. If False, explain why.
The BEGIN declaration starts the variable declaration sections of a PL/SQL block.
False. The BEGIN declaration starts the execution section.
Say True or False. If False, explain why.
The PL/SQL engine executes the procedural commands and passes the SQL commands for the Oracle server to process.
True.
Say True or False. If False, explain why.
PL/SQL supports the CREATE command.
False. PL/SQL doesn’t support the data definition commands like CREATE.
What is returned by the cursor attribute SQL%ROWCOUNT?
It returns the number of rows that are processed by a SQL statement.
What is returned by the cursor attribute SQL%FOUND?
It returns the Boolean value TRUE if at least one row was processed.
What is returned by the cursor attribute SQL%NOTFOUND?
It returns the Boolean value TRUE if no rows were processed.
Which command/commands allow iteration a use of loops in a PL/SQL block?
LOOP command, FOR.. LOOP command, WHILE command.
What is the difference in execution of triggers and stored procedures?
A trigger is automatically executed without any action required by the user, whereas, a stored procedure needs to be explicitly invoked.
What are the uses of triggers?
Basically triggers are used to create consistencies, access restriction and implement securities to the database. Triggers are also used for − Creating validation mechanisms involving searches in multiple tables Creating logs to register the use of a table Update other tables as a result of inclusion or changes in the current table.
Say True or False. If False, explain why.
Triggers can be associated to a view.
True.
Say True or False. If False, explain why.
When a trigger is associated to a view, the base table triggers are normally disabled.
False. When a trigger is associated to a view, the base table triggers are normally enabled.
Say True or False. If False, explain why.
A trigger can perform the role of a constraint, forcing an integrity rule.
True.
Say True or False. If False, explain why.
A trigger can execute the COMMIT, ROLLBACK, or SAVEPOINT commands.
A trigger cannot execute the COMMIT, ROLLBACK, or SAVEPOINT commands.
What is the use of a WHEN clause in a trigger?
A WHEN clause specifies the condition that must be true for the trigger to be triggered.
Say True or False. If False, explain why.
Statement level triggers are triggered only once.
True.
What is the purpose of the optional argument [OR REPLACE] in a CREATE TRIGGER command?
The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.
Say True or False. If False, explain why.
INSTEAD OF is a valid option only for triggers in a table.
False. INSTEAD OF is a valid option only for views. INSTEAD OF trigger cannot be specified in a table.
Write a statement to disable a trigger named update_marks.
ALTER TRIGGER update_marks DISABLE;
Which command is used to delete a trigger?
Use the DROP TRIGGER statement to remove a database trigger from the database. The trigger must be in your own schema or you must have the DROP ANY TRIGGER system privilege.
Which command is used to delete a procedure?
DROP PROCEDURE command.
What is the difference between a function and a stored procedure?
A function returns a value and a stored procedure doesn’t return a value.
How do you declare a user-defined exception?
User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax − <exception_name> EXCEPTION;
What do you understand by explicit cursors?
Explicit cursors are defined explicitly using the CURSOR statement, with a general syntax − CURSOR cursor_name [(parameters)] IS query_expression; It allows processing queries that return multiple rows.
What are the steps that need to be performed to use an explicit cursor? Discuss briefly.
The steps that need to be performed on explicit cursor are −
DECLARE − assigns a name to the cursor and defines the structure of query within it.
OPEN − executes the query, whereby the rows returned by the query are available for fetching.
FETCH − assigns values from the current row (cursor position) into specified variables.
CLOSE − releases the memory space.
PL/SQL packages usually have two parts. What are these two parts?
PL/SQL packages have two parts −
Specification part − where the interface to the application are defined.
Body part − where the implementation of the specification are defined.
Which command(s) are used for creating PL/SQL packages?
CREATE PACKAGE command is used for creating the specification part. CREATE PACKAGE BODY command is used for creating the body part.
How do you refer to the types, objects and subprograms declared within a package?
The types, objects, and subprograms declared within a package are referred to using the dot notation as − package_name.type_name package_name.object_name package_name.subprogram_name
Say True or False. If False, explain why.
PL/SQL allows subprogram overloading feature within a package.
True.
Which command is used to delete a package?
The DROP PACKAGE command.
What is the difference between implicit and explicit cursors?
Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created. The main difference between the implicit cursor and explicit cursor is that an explicit cursor needs to be defined explicitly by providing a name while implicit cursors are automatically created when you issue a select statement. Furthermore, multiple rows can be fetched using explicit cursors while implicit cursors can only fetch a single row. Also NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors, as opposed to implicit cursors. In essence, implicit cursors are more vulnerable to data errors and provide less programmatic control than explicit cursors. Also, implicit cursors are considered less efficient than explicit cursors.
Say True or False. If False, explain why.
The %NOTFOUND attribute returns true when the cursor is not created explicitly.
False. The %NOTFOUND attribute returns true when the last row of the cursor is processed and no other row is available.
Say True or False. If False, explain why.
The %ROWCOUNT attribute returns the total number of rows returned by the FETCH command.
True.
