2023's Most Frequently Asked PL/SQL Interview Questions - IQCode
PL/SQL: An Introduction to Oracle's Procedural Language
PL/SQL is an acronym for Procedural Language extensions to SQL, and was created by Oracle to address the limitations of SQL for building and handling critical applications in a more comprehensive way. The advantages of PL/SQL over SQL are many, and include:
* Decision-making, looping, and branching capabilities that were not possible with SQL. * Faster execution due to reduced query traffic, which means that PL/SQL statements are processed as blocks and passed to the server engine. * Built-in error checking for data manipulation.
PL/SQL is a block-structured language that combines the power of SQL with procedural statements that can be passed as blocks to the Oracle engine for faster processing. Here are some common basic interview questions about PL/SQL:
1. What are the key features of PL/SQL?
Understanding PL/SQL Tables
PL/SQL tables, also known as index-by tables, are a type of collection that enable you to hold data in a table format in a program. They are unbounded and may have up to 2147483647 elements. You can index the elements using binary integers or strings using the key-value pairs. PL/SQL tables are identical to arrays, but they have extendable memory, allowing them to be resized dynamically when data is added or removed.
Basic Structure in PL/SQL
PL/SQL follows a standard structure which includes a declaration section, an executable section, and an exception-handling section.
The declaration section is where variables, constants, cursors, exceptions, and other program objects are defined.
The executable section is where the main business logic of the program resides, including control structures and SQL statements.
The exception-handling section is where exceptions are caught and handled gracefully, avoiding program termination.
Below is an example of a basic PL/SQL structure:
-- Declaration Section
-- Executable Section
x := 10;
DBMS_OUTPUT.PUT_LINE('x is: ' || x);
-- Exception-handling Section
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
What is a PL/SQL Cursor?
A PL/SQL cursor is a pointer to a result set returned by a SELECT statement. It allows the developer to move through the rows of the result set one at a time and perform operations on each row. Cursors can be used to iterate through large result sets or to retrieve data from tables with complex join conditions. Cursors are often used in stored procedures and functions to process large amounts of data efficiently in a procedural manner.
Understanding the Purpose of "WHERE CURRENT OF" Clause in Cursors
The "WHERE CURRENT OF" clause is a feature in SQL language that allows the developer to update or delete a row that is currently being pointed to by a cursor. This clause is used in combination with a cursor that has already been declared and opened in the code.
For instance, if we have a cursor that is traversing through a particular result set of a table and we want to update or delete the record that the cursor is currently pointing to, then we can use the "WHERE CURRENT OF" clause along with the UPDATE or DELETE statement to perform the desired operation.
Overall, the "WHERE CURRENT OF" clause helps in building more dynamic and flexible applications by enabling the developers to manipulate the data in a cursor in real-time.
Assigning a Name to an Unnamed Exception Block in PL/SQL
In PL/SQL, an unnamed exception block can be assigned a name by using the keyword "EXCEPTION" followed by the name of the block. This makes it easier to identify the block when viewing debug information.
Here's an example:
DECLARE -- exception block with no name BEGIN ... EXCEPTION -- assign name to exception block WHEN OTHERS THEN NULL; END block_name;
In this code block, the unnamed exception block is assigned the name "block_name" using the EXCEPTION keyword. The WHEN OTHERS THEN clause indicates that this block will handle any exceptions that are not handled by previous exception blocks.
Assigning names to exception blocks can help with code readability and organization, especially when working with larger code bases.
What are Triggers and When Should You Use Them?
Triggers in programming are automatic actions executed in response to certain events or changes in the system. They are commonly used in databases to enforce business rules, maintain data consistency, and automate repetitive tasks.
Some instances when triggers can be used include:
1. Enforcing constraints: Triggers can be used to enforce constraints on columns in a table, such as ensuring that a value is within a certain range or that it must be unique.
2. Auditing changes: Triggers can be set up to capture changes made to data, such as recording who made the change and when.
3. Automatic updates: Triggers can be used to update other tables when a change is made in a related table.
4. Sending notifications: Triggers can be set to send notification emails or messages when certain conditions are met.
Overall, triggers can be a powerful tool for automating tasks and maintaining data integrity in a database system.
When is a "declare" block mandatory?
In PL/SQL, a "declare" block becomes mandatory when you need to declare variables or cursor names for their use in the code further down. It is also useful for declaring exceptions that might occur in the program. Therefore, whenever you need to define variables or cursor names to use in your program, or need to handle errors that may occur in your program, you will need to create a "declare" block.
Writing Comments in PL/SQL Code
In PL/SQL, there are two ways to write comments:
- Single-line comments: To write a single-line comment, use two hyphens (--) followed by the comment text.
- Multi-line comments: For writing comments that span multiple lines, use /* to begin the comment and */ to end the comment.
Here's an example of PL/SQL code with comments:
-- This is a single-line comment
/* This is a multi-line comment */ BEGIN -- code here END;
Purpose of WHEN clause in triggers
In database management systems, a trigger is a set of instructions that are automatically executed in response to certain events. The WHEN clause in a trigger specifies a condition that must be met in order for the trigger code to be executed. In other words, the WHEN clause allows us to control the circumstances under which the trigger code should be invoked.
For example, suppose we have a table "Orders" and we want to create a trigger that automatically updates the "Total" column whenever a new order is added. We can use the WHEN clause to ensure that the trigger only executes when the "Amount" column of the new order is greater than zero:
CREATE TRIGGER update_total AFTER INSERT ON Orders WHEN (NEW.Amount > 0) BEGIN UPDATE Orders SET Total = Total + NEW.Amount WHERE OrderID = NEW.OrderID; END;
In this example, the trigger code is wrapped in a BEGIN-END block, which allows for multiple SQL statements to be executed. The UPDATE statement inside the trigger adds the new order amount to the existing total for that order.
Overall, the WHEN clause is a powerful tool that enables us to create flexible triggers that respond to specific events in the database.
PL/SQL Execution Architecture
As a intermediate PL/SQL developer, you should have an understanding of the PL/SQL execution architecture. When PL/SQL blocks or subprograms are executed, they go through the following phases:
1. Parsing Phase: The PL/SQL code is checked for syntax errors and properly tokens are created. 2. Compilation Phase: Correct syntax is compiled into bytecode and if any errors occur they are reported. 3. Execution Phase: Once compiled, PL/SQL Code is executed. 4. Exception Handling Phase: During execution phase, if any exception or error occurs exception handling block will be triggered. If the exception is not handled in the inner-most block then the call stack is unwound.
The above four phases of PL/SQL Execution Architecture take place when PL/SQL blocks or subprograms are executed.
Purpose of SYSDATE and USER Keywords
In Oracle databases, the SYSDATE and USER keywords are utilized to retrieve the current system date and the username associated with the current session. These keywords are commonly used in SQL statements to record important audit trails and track system activities. For example, the SYSDATE keyword can be employed to timestamp when a record was created or updated, while the USER keyword can be used to record which user made the change. In summary, the SYSDATE and USER keywords are essential for increasing the efficiency and security of Oracle database management.
Differentiating between Implicit Cursor and Explicit Cursor
In Oracle, a cursor is a tool used to retrieve and manipulate results returned by a SELECT statement. There are two types of cursors: implicit cursors and explicit cursors.
Implicit Cursor: An implicit cursor is created automatically by Oracle whenever a DML statement (INSERT, UPDATE, DELETE) is executed. It is used to retrieve the number of rows affected by the DML statement.
sql DECLARE v_count NUMBER; BEGIN UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10; v_count := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' rows.'); END;
Here, the implicit cursor SQL%ROWCOUNT is used to get the number of rows updated by the UPDATE statement.
Explicit Cursor: An explicit cursor is created by the programmer to handle the result of a SELECT statement. It must be explicitly declared, opened, fetched, and closed. Explicit cursors provide more control over the retrieval of data than implicit cursors.
sql DECLARE CURSOR c_emp IS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 10; v_emp_id employees.employee_id%TYPE; v_last_name employees.last_name%TYPE; v_salary employees.salary%TYPE; BEGIN OPEN c_emp; LOOP FETCH c_emp INTO v_emp_id, v_last_name, v_salary; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_last_name || ' ' || v_salary); END LOOP; CLOSE c_emp; END;
Here, an explicit cursor c_emp is created to select employee_id, last_name, and salary from the employees table where department_id is 10. The cursor is opened, the data is fetched into variables, and a loop is executed to print the data. Finally, the cursor is closed.
Difference between SQL and PL/SQL
SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases. It is used to extract and manipulate data in a database by writing queries.
PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL and is used to write scripts that are executed in a database. It is used to create programs, stored procedures, and triggers in a database.
The main difference between the two is that SQL is a declarative language, while PL/SQL is a procedural language. SQL is used to retrieve and manipulate data in a database, while PL/SQL is used to write programs that automate the database management process.
Another difference is that PL/SQL allows for variables, loops, and conditional statements, while SQL does not. PL/SQL also allows for error handling and exception handling, while SQL does not.
SELECT * FROM Employees WHERE Age > 30;
DECLARE emp_count NUMBER; BEGIN SELECT COUNT(*) INTO emp_count FROM Employees WHERE Age > 30; DBMS_OUTPUT.PUT_LINE('There are ' || emp_count || ' employees over the age of 30.'); END;
Importance of %TYPE and %ROWTYPE Data Types in PL/SQL
In PL/SQL, using the %TYPE and %ROWTYPE data types can greatly improve the readability and maintainability of code.
The %TYPE data type allows variables to inherit the data type and size from a previously defined variable or column in the database table. This technique can make code more efficient and less prone to errors.
The %ROWTYPE data type allows for the creation of record variables that closely match the structure of a table row. This can simplify coding when referencing values in a table and can also aid in debugging.
Various Functions for Manipulating Character Data
In programming, there are several functions available for manipulating character data. These functions can be used to modify or extract specific parts of a string. Some of the commonly used functions for character manipulation in programming languages like Python, Java, and C++ include:
- returns the length of a string.
- converts all characters in a string to uppercase.
- converts all characters in a string to lowercase.
- removes any whitespace characters from the beginning and end of a string.
- splits a string into a list of substrings based on a delimiter.
- joins a list of strings into a single string using a separator.
- replaces all occurrences of a substring in a string with another substring.
These functions can be very useful when working with character data in a program.
Explanation of Rollback and Rollback To Statements in PL/SQL
In PL/SQL, the
statement is used to undo the changes made in a transaction. This means that any changes made since the beginning of the transaction will be removed or undone.
On the other hand, the
statement is used to undo the changes made up to a specified savepoint within the transaction. A savepoint is a marker within a transaction that indicates a point to which a transaction can be rolled back.
To summarize, the main difference between
statements in PL/SQL is that
reverses all the changes made in a transaction, while
reverses only the changes made up to a specified savepoint within the transaction.
Understanding the Purpose of SYS.ALL_DEPENDENCIES
The SYS.ALL_DEPENDENCIES is a system table in Oracle that stores information about dependencies between objects in the database. It is used to query dependencies between objects, such as procedures, functions, triggers, views, and packages. This information can be helpful in understanding the impact of changes to database objects and in troubleshooting errors related to dependencies. By querying SYS.ALL_DEPENDENCIES, developers can gain insights into the structure of their database and ensure that all dependencies are properly maintained.
Virtual Tables Available During Execution of Database Trigger
During the execution of a database trigger, there are several virtual tables available, including "inserted" and "deleted," which contain the new and old values for the affected rows. These tables are used to access the data being modified by the trigger and make any necessary changes. Other virtual tables include "updated," "trigger\_metadata," and "trigger\_nesteD\_level." By using these virtual tables, triggers can perform complex operations on the database and maintain data integrity.
Difference between Cursors declared in Procedures and Package Specifications
In Oracle PL/SQL, Cursors can be declared either in procedures or in package specifications. The main differences between them are as follows:
- A Cursor declared in a procedure is local to that procedure, whereas a Cursor declared in package specifications is global and can be accessed by all the procedures/functions of that package.
- The Cursor declared in a procedure can only be used within that procedure’s block, whereas the Cursor declared in package specifications can be used in any block within that package.
- In Package Specifications, the Cursor can be declared with its type and other attributes outside the procedures or functions, making it easier to maintain and modify when needed.
- Package specification Cursors are loaded into memory when the package is first compiled, whereas, in procedure, the Cursor is loaded into memory every time the procedure is called.
In summary, Cursors declared in package specifications are more useful when we need to use them across multiple procedures or functions within that package. Cursors declared in procedures are more suitable when we need a Cursor for a specific task within a single procedure.
PL/SQL Advanced Interview Questions
In PL/SQL, COMMIT, ROLLBACK, and SAVEPOINT statements are used to manage the transactions within a program. A COMMIT statement is used to make all the changes made to the database permanent. A ROLLBACK statement is used to undo all the changes made since the last COMMIT statement was executed. A SAVEPOINT statement is used to set a point in the transaction from where the ROLLBACK statement can be executed to undo the changes made from that point.
Debugging PL/SQL code
To debug PL/SQL code, you can make use of the following tools and techniques:
1. DBMS_OUTPUT package: It is a built-in package in Oracle that enables you to display output values and debug information from PL/SQL code. You can use the `PUT_LINE` procedure to display values on the screen.
2. SQL Developer debugger: SQL Developer is a free tool from Oracle that provides a debugger for PL/SQL code. You can set break points, step through code, view and change variable values, and much more.
3. PL/SQL Developer debugger: PL/SQL Developer is a third-party tool that supports debugging of PL/SQL code. It is a paid tool, but it provides a lot of features to make debugging easier.
4. Exception handling: You should always include exception handling in your PL/SQL code. This helps you to catch errors and take appropriate action. You can also use `RAISE_APPLICATION_ERROR` to raise an error manually.
By using these tools and techniques, you can easily debug your PL/SQL code and ensure that it is free of errors.
Mutating Tables vs. Constraining Tables
In database management systems, a mutating table is a table that is currently being modified by an insert, update or delete operation. On the other hand, a constraining table is a table that is referenced by a foreign key constraint.
The main difference between a mutating table and a constraining table is that a mutating table cannot be read from or modified by a trigger in the same transaction, whereas a constraining table can be read or modified by a trigger.
In simpler terms, when a trigger tries to modify a mutating table, it throws an error because the table is currently being modified. However, when a trigger tries to modify a constraining table, it is allowed to do so because the table is not currently being modified.
Cursor Attributes for Saving DML Statement Execution Outcomes
In which cursor attributes are the outcomes of executing a DML statement saved?
Not applicable as this is a question related to database concepts and not programming.
This question pertains to cursor attributes in a database and how they are used to save the outcomes of executing a DML (Data Manipulation Language) statement. It is not related to programming code.
Can a column with number data type have a scale larger than the precision?
Yes, it is possible to define a number column with a scale larger than the precision. However, it is not recommended to do so as it may result in inaccurate data storage and retrieval.
For instance, defining a column as "COLUMN_NAME NUMBER(10,100)" means that the column can store numbers with a maximum precision of 10 digits and a scale of 100 decimal points. Similarly, "COLUMN_NAME NUMBER(10,-84)" means that the column can store numbers with a maximum precision of 10 digits to the left of the decimal point and a scale of 84 decimal points to the right of the decimal point.
However, it is important to note that defining such columns can lead to storage and performance issues. Therefore, it is advisable to plan the precision and scale of number columns carefully and avoid defining them in such a way that the scale is larger than the precision.
PL/SQL Program to Calculate Average Using While Loop
sum NUMBER:= 0;
count NUMBER:= 0;
DBMS_OUTPUT.PUT_LINE('Enter numbers (Enter 0 to stop): ');
/* Take input until user enters 0 */
WHILE n != 0 LOOP
n := &input_value; /* Input from user */
sum := sum + n;
count := count + 1;
/* Calculate average if user entered at least one number */
IF count > 0 THEN
avg := sum/count;
DBMS_OUTPUT.PUT_LINE('Average of entered numbers is: '||avg);
DBMS_OUTPUT.PUT_LINE('No numbers entered!');
In this PL/SQL program, we are using a while loop to take input from the user until they enter 0. The program then calculates the average of the numbers entered by the user using the sum and count variables. Finally, the average is displayed using DBMS_OUTPUT.PUT_LINE function.
Writing a PL/SQL Procedure for Selecting Records from Database with Filters
To select specific records from the database using certain parameters as filters, the following PL/SQL procedure can be used:
sql CREATE OR REPLACE PROCEDURE select_records ( p_param1 IN data_table.param1%TYPE, p_param2 IN data_table.param2%TYPE, p_param3 IN data_table.param3%TYPE) IS BEGIN SELECT * FROM data_table WHERE param1 = p_param1 AND param2 = p_param2 AND param3 = p_param3; END;
In this procedure, `data_table` is the name of the table we want to query from. `param1`, `param2`, and `param3` are the names of the columns in that table that we want to filter our query by. The procedure takes in three parameters: `p_param1`, `p_param2`, and `p_param3`, which correspond to the values we want to filter for parameters 1, 2, and 3 respectively.
To use this procedure, you would simply call it from within a PL/SQL block or function with the appropriate values for each parameter. The procedure will then return all records from `data_table` where the values for `param1`, `param2`, and `param3` match the corresponding parameter values passed in.
Note that this is just a basic example and can be expanded upon as needed, depending on the specific requirements for selecting records from the database.
Counting Sundays between two inputted dates using PL/SQL
DECLARE start_date DATE; end_date DATE; num_of_sundays NUMBER := 0; BEGIN -- Assign inputted start and end dates start_date := TO_DATE('01-JAN-2022','DD-MON-YYYY'); end_date := TO_DATE('31-DEC-2022','DD-MON-YYYY'); -- Loop from start date to end date, increment by 1 day each time FOR i IN start_date..end_date LOOP -- Check if current day is a Sunday IF TO_CHAR(i, 'DAY') = 'SUNDAY' THEN -- Increment count of Sundays num_of_sundays := num_of_sundays + 1; END IF; END LOOP; -- Display number of Sundays between start and end dates DBMS_OUTPUT.PUT_LINE('Number of Sundays between ' || start_date || ' and ' || end_date || ' is: ' || num_of_sundays); END;
Explanation of code: - The code declares three variables: start_date, end_date, and num_of_sundays. - The inputted start and end dates are assigned to start_date and end_date respectively. - A loop is initiated to iterate from start_date to end_date, incrementing by 1 day each time. - For each iteration, the current date is checked to see if it is a Sunday using TO_CHAR function. - If the current day is a Sunday, the count of Sundays (num_of_sundays) is incremented. - After the loop is completed, the code outputs the number of Sundays between the start and end dates.
PL/SQL Code Block to Increment Employee Salary
DECLARE v_salary employees.salary%TYPE; BEGIN -- Get the current salary of employee ID 102 SELECT salary INTO v_salary FROM employees WHERE employee_id = 102; -- Increment the salary by 1000 v_salary := v_salary + 1000; -- Update the employee's salary UPDATE employees SET salary = v_salary WHERE employee_id = 102; -- Commit the changes COMMIT; DBMS_OUTPUT.PUT_LINE('Employee 102 salary has been increased by 1000.'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee with ID 102 not found.'); END;
The above PL/SQL code block fetches the current salary of the employee with ID 102 from the
table, increments it by 1000 and updates it in the same table. A commit is made to make the changes permanent. In case the employee ID 102 is not found in the table, an exception is caught and an appropriate message is displayed.
PL/SQL Code to Check if a String is a Palindrome or Not
DECLARE word VARCHAR2(50); -- declare a variable to store the input string word_len NUMBER; -- declare a variable to store the length of the string i NUMBER; -- declare a counter variable j NUMBER; -- declare another counter variable is_palindrome BOOLEAN := TRUE; -- set a boolean variable to store the result of the check, initially true BEGIN word := 'racecar'; -- assign the input string value to the variable word_len := LENGTH(word); -- get the length of the string FOR i IN 1..FLOOR(word_len/2) LOOP -- loop through the first half of the string j := word_len-i+1; IF SUBSTR(word,i,1) <> SUBSTR(word,j,1) THEN -- check if the characters at opposite ends of the string are not equal is_palindrome := FALSE; EXIT; -- if not equal, set the boolean variable to false and exit the loop END IF; END LOOP; IF is_palindrome THEN -- print the appropriate message based on the boolean value DBMS_OUTPUT.PUT_LINE(word || ' is a palindrome'); ELSE DBMS_OUTPUT.PUT_LINE(word || ' is not a palindrome'); END IF; END;
The above PL/SQL code checks if a given input string is a palindrome or not. It first declares the necessary variables such as the input string variable, length variable, and two counter variables. A boolean variable is also declared and initialized to true to store the result of the check.
The code then assigns the input string value to the variable and gets the length of the string. It then loops through the first half of the string and checks if the characters at opposite ends of the string are equal. If they are not equal, the boolean variable is set to false and the loop is exited.
Finally, the code prints the appropriate message based on the boolean value. If the boolean value is true, the input string is a palindrome. If the boolean value is false, the input string is not a palindrome.
Convert Number Digits to Words using PL/SQL
DECLARE num NUMBER := 1234; -- number to be converted to words digit NUMBER; word VARCHAR2(20); result VARCHAR2(100); BEGIN while num > 0 LOOP digit := num MOD 10; -- converting digit to word format CASE digit WHEN 0 THEN word := 'Zero'; WHEN 1 THEN word := 'One'; WHEN 2 THEN word := 'Two'; WHEN 3 THEN word := 'Three'; WHEN 4 THEN word := 'Four'; WHEN 5 THEN word := 'Five'; WHEN 6 THEN word := 'Six'; WHEN 7 THEN word := 'Seven'; WHEN 8 THEN word := 'Eight'; WHEN 9 THEN word := 'Nine'; END CASE; result := word || ' ' || result; -- appending word to result string num := num / 10; -- removing last digit from number END LOOP; DBMS_OUTPUT.PUT_LINE(result); -- outputting result END;
This PL/SQL program takes a number to be converted to words as input. Then, it utilizes a loop to extract each digit of the input number, converts it to the corresponding word format, and appends it to a result string. Finally, the resulting string is outputted using the DBMS_OUTPUT package.
Finding the Sum of Digits of a Number using PL/SQL
DECLARE num NUMBER := 1234; sum NUMBER := 0; rem NUMBER := 0; BEGIN WHILE num > 0 LOOP rem := MOD(num, 10); sum := sum + rem; num := TRUNC(num / 10); END LOOP; DBMS_OUTPUT.PUT_LINE('The sum of digits is ' || sum); END;
This PL/SQL program uses a while loop to find the sum of digits of a given number. The given number is stored in the variable "num" and the sum is stored in the variable "sum". Inside the while loop, the remainder of the number is found using the MOD function and added to the sum. Then the number is divided by 10 and truncated to remove the last digit. The process continues until all digits have been processed. The output is printed using the DBMS_OUTPUT.PUT_LINE function.
Conclusion on PL/SQL
In this article, we have discussed various concepts and features of PL/SQL, a procedural language extension to SQL. We have understood that PL/SQL is used to enhance the capabilities of SQL and is preferred by database developers for ease of coding complex tasks.
Additionally, we have covered essential topics like control structures, procedures, functions, and exceptions, which have played a vital role in building efficient and robust applications over time.
Overall, PL/SQL serves as a powerful tool for managing and processing data in large and complex databases. Knowing and implementing PL/SQL in your projects can make you a valuable asset in the IT industry, specifically in database management and development roles.