Advertise with Us!
We have a variety of advertising options which would give your courses an instant visibility to a very large set of developers, designers and data scientists.View Plans
PL-SQL Interview Questions
PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL.
Amongst other questions during an interview, it is also important to know how SQL and PL/SQL are different. Some benefits of PL/SQL over SQL are:
- Supports procedural processing, conditional statements, looping and other features similar to high-level languages.
- Multiple statements can be sent to the database server at once in the form of a procedure, hence saving time and network traffic.
- Customized error handling is possible.
- Fully portable.
Apart from the above benefits, PL/SQL has some more attractive features like functions, procedures, triggers, cursors that make it one of the most versatile relational databases.
A bit of background
PL/SQL 1.0 was released in 1992 with Oracle 6 as an optional extension. It was with PL/SQL 2.0 that stored procedures, functions, user-defined record types, tables, packages and extensions like DBMS_* were introduced. The latest PL/SQL version is 11.0, which was released with Oracle 11g and came with advanced features such as native compilation, PL/SQL function result cache, and minute dependency tracking.
PL-SQL Interview Questions & Answers
Below are some common basic and advanced interview questions and answers –
Question: What is PL/SQL?
Answer: A procedural language where code can be executed in blocks. It is an extension to SQL.
Question: What are the differences between PL/SQL and SQL?
|SQL is a query language to interact with the database.||It is an extension of SQL which supports procedures, functions and many more features.|
|Supports only simple queries that can perform insert, update, delete on tables.||Can perform complex tasks like a high-level programming language, for example, while loop, if-else statements, etc…|
|SQL statements can be executed only one at a time, thereby making it a time-consuming process.||The entire block of statements is sent to the database server at once to be executed, saving time and increasing efficiency.|
|No provision for error handling.||Customized error handling is possible.|
Question: What is the basic structure of PL/SQL?
[DECLARE] --declaration statements (optional) BEGIN --execution statements [EXCEPTION] --exception handling statements END;
Question: Define cursor and its use.
Answer: A cursor is a pointer to a memory area assigned by Oracle to process SQL statements. The cursor is used to hold records returned by the SQL query. There are 2 types of cursors – implicit and explicit.
Question: Why do we use database triggers? Give the syntax of a trigger.
Answer: The trigger is a stored procedure that is automatically invoked when an event happens. The event could be: insert, update, delete, etc… Syntax –
create trigger [trigger_name] [before | after] on [table_name] [for each row] [trigger_body]
Question: How do you compile PL/SQL code?
Answer: Firstly, syntax check is performed. When the developer corrects any syntax errors, Oracle binds all the variables holding data with a storage address. Finally, the p-code generation process takes place.
Question: Explain exception handling in PL/SQL.
Answer: PL/SQL offers customized exception handling. When an error occurs, an error handling code is included in the program itself. There are 3 types of exceptions –
- Pre-defined exceptions – common errors that are already defined. Example – NO_DATA_FOUND
- Undefined exceptions – the errors that do not have predefined names.
- User-defined exceptions – handled by the code written by the user.
Question: Tell about a few data types in PL/SQL.
Answer: There are many data types –
- Scalar types – primitive data types like CHAR, DATE, LONG, VARCHAR2 etc…
- Composite – these are made up of other data types and can be easily updated. Example, RECORD, TABLE etc…
- Reference data types like CURSOR
- Large object types – BLOB, CLOB etc…
Question: What is the difference between %TYPE and %ROWTYPE? Give an example.
|The attribute that declares a variable of the same data type as of a table column.||The attribute that declares a variable of type RECORD having the same structure as a table row. The row is the RECORD that contains fields having the same data types and names as the columns of a table or view.|
DECLARE studentId students.student_id%TYPE;
DECLARE stud_rec students.%ROWTYPE;
Question: What constitutes a PL/SQL package?
Answer: Packages are schema objects that place functions, procedures, variables, etc… in one place. Packages should have –
- Package specifications
- Package body
Question: List some schema objects that are created using PL/SQL.
Answer: Database links, triggers, stored procedures, functions and packages, views, synonyms, external procedure libraries, sequences, etc…
Check here: Basic SQL Commands Checklist
Question: Explain the difference between procedure and function.
|The function is compiled every time
it is called for execution.
|Procedures are pre-compiled and saved. They execute the pre-compiled code whenever called.|
|Can be called from SQL statements.||Can not be called from SQL statements.|
|The function has to return a value.||Need not return any value.|
|Generally used for computation purpose.||Used for executing complex business logic.|
|Can return multiple values using other methods, otherwise, return only a single value.||Can return multiple values|
|Returns scalar data types.||Returns an int by default.|
|A stored procedure can not be called from a function||The procedure can call any function|
|Functions can be embedded in a select statement||Inside a select statement, a procedure cannot be called.|
|Exception handling is not possible||Try/catch block can be defined inside a procedure|
Question: Explain the difference between procedure and trigger.
|Called explicitly by a user, trigger or an application||Executed by the DBMS whenever an event occurs in the database.|
|Can have parameters||Doesn’t have parameters|
|Cannot be inactive||Can be enabled or disabled on need basis|
|Creation – CREATE PROCEDURE||Creation – CREATE TRIGGER|
Question: What are the different types of cursors in PL/SQL?
Answer: There are two types of cursors –
- Implicit cursor – PL/SQL applies implicit cursors for INSERT, UPDATE, DELETE and SELECT statements returning a single row.
- Explicit cursor – created by a programmer for queries returning more than one row. Syntax–
CURSOR is SELECT statement; OPEN ; FETCH INTO ; CLOSE ;
Question: What are the different types of constraints?
- Not NULL
- Primary key
- Foreign key
Question: What are the differences between triggers and constraints?
|Stored as separate objects||A constraint on a table is stored along with the table definition|
|Triggers are fired upon an event; hence they are fired after constraints||Constraints are fired as soon as the
the table is used.
|Perform table to table
comparison, hence faster
|Performs memory location to table the comparison which is slow leading to low performance.|
|Trigger is for the entire table||The constraint is for a column of the table|
|They are just stored procedures that get automatically executed, hence don’t check for data integrity.||Prevent duplicate and invalid data entries|
Question: Explain different types of PL/SQL blocks.
Answer: Block is any group of PL/SQL code like SQL statements, loops, variables, constants etc… There are 2 types of blocks –
- Anonymous blocks – these do not have a header or name.
- Named blocks – these blocks have header or label. They can be stored procedures, functions, triggers or packages.
Question: Explain PL/SQL Records.
Answer: Records contain a set of data of various data types that can be related to each other as fields. Three types of records that are supported in PL/SQL are table-based records, programmer-based records, and cursor-based records.
Question: Explain the difference between commit and savepoint.
COMMIT – is used to make the database changes permanent. All the save points are erased and the transaction ends. Once committed, a transaction cannot be rolled back.
SAVEPOINT – is used to set points during a transaction to which a programmer can roll-back later. it is helpful when there is a series of transactions that can be divided into groups having savepoint.
Question: What is the difference between actual and formal parameters?
Answer: The parameters that are used to call a procedure are called as actual parameters. Example –
get_student_details(stud_name, dob); -- here stud_name and dob are actual parameters.
The variables declared in a procedure header used in the body are called formal parameters. Example –
PROCEDURE get_student_details (dob DATE) IS – here stud_name is a formal parameter.
Question: How is a DECLARE statement used?
Answer: DECLARE is used as the first statement for stand-alone files that consist of anonymous block of code which are not stored procedures, functions or triggers. Example –
DECLARE num1 NUMBER(2); num2 NUMBER(3); BEGIN -- logic goes here END;
Question: Tell us about SQLCODE and SQLERRM.
Answer: SQLCODE and SQLERRM are used to trace exceptions that are not explicitly handled in the program. These are globally defined variables. SQLCODE returns the error code while SQLERRM returns the corresponding error message.
Question: What is rollback? How is it different from rollback to statement?
Answer: Rollback erases all the database changes including all the savepoints. It ends a transaction.
‘Rollback to’ rollbacks the changes up to the savepoint mentioned in the code. The transaction will still be active.
Question: What is IN OUT parameter?
Answer: IN OUT parameter mode passes a value to a subprogram and returns an updated value.
Question: Is it possible to accept user inputs during runtime? How?
Answer: Yes, it is possible. Use ACCEPT keyword to take inputs from the user. Example –
ACCEPT age number prompt ‘Enter your age:’
Question: Give a simple way to run a query faster.
Answer: By using ROWID. It is not a physical column but the logical address of a row. It contains the block number, file number and row number thereby reducing I/O time hence making query execution faster.
Question: What are some of the pre-defined exceptions in PL/SQL?
Answer: ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, DUP_VAL_ON_INDEX etc…
Question: How do you trace the PL/SQL code?
Question: You can trace through DBMS_* methods like
- DBMS_SESSION and DBMS_MONITOR
Question: How to restrict string length in PL/SQL?
Answer: Use CHAR (NUMBER) to get fixed length for a variable. Example – CHAR (10). If the length of the string is less than the specified number, it will be padded with white spaces.
Question: What is the purpose of the UTL_FILE package in PL/SQL?
Answer: By using this package, developers can get the code read and write files to and from the computer. For doing this, the developer will need access grant from DBA user.
Question: What are DBMS_OUTPUT and DBMS_DEBUG?
Answer: Both can be used for debugging the code. DBMS_OUTPUT prints the output to console whereas DBMS_DEBUG prints it to a log file.
Question: List some cursor attributes in PL/SQL.
- %ISOPEN: Check if the cursor is open
- %ROWCOUNT: Get the number of rows that are updated, deleted or fetched.
- %FOUND: Checks if the cursor has fetched any row, returns Boolean.
- %NOT FOUND: Checks if the cursor has fetched any row. Returns Boolean.
Question: What is the purpose of NVL?
Answer: NVL lets the programmer substitute a value for a NULL value. Example –
NVL (occupation, ‘default’)
Question: On a table, how many triggers can be applied?
Answer: 12 is the maximum number.
Question: How can we achieve consistency using PL/SQL?
Answer: We can achieve consistency by setting the appropriate isolation level. For example, to give read consistency, the isolation level can be set to READ COMMITTED.
Question: Write a simple procedure to select some records from the database using some parameters.
Answer: Example code –
CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10) AS BEGIN SELECT * FROM customers WHERE age = @age AND city = @city; END;
Question: Explain the error ORA-03113.
Answer: The error end-of-file on communication channel ORA-03113 means that there is a broken connection between the client and server channels. It could be a timeout because of which the connection was lost. You can troubleshoot by pinging the server and checking the connectivity.
Question: Can you use IF statement inside a SELECT statement? How?
Answer: Yes, we can do so using the DECODE keyword in versions 9 and above. Example –
SELECT day_of_week, DECODE (number, 0, 'Sunday', 1, 'Monday', 2, 'Tuesday', 3, 'Wednesday', 4, 'Thursday', 5, 'Friday', 6, 'Saturday', 'No match') result FROM weekdays;
Question: What is SYS.ALL_DEPENDENCIES?
Answer: SYS.ALL_DEPENDENCIES describes all the dependencies between packages, procedures, functions, triggers accessible to the current user. It shows columns like name, type, dependency_type, referenced_owner, etc…
People are also Reading: