Oracle PL/SQL Interview Questions and Answers
Oracle PL/SQL is used to speed up the process by decreasing the traffic with its block-structured feature. Certified Oracle PL/SQL professionals are in high demand in organizations. Here are the top 20 Oracle PL/SQL interview questions and answers to help you easily ace the interviews.
Oracle PL/SQL Interview Questions and Answers for Freshers
1. What is Oracle PL/SQL?
Procedural languages like Oracle PL/SQL combine features from interactive SQL with procedural programming language characteristics like conditional branching and iteration.
2. What is the difference between SQL and PL/SQL?
- SQL is a language designed specifically for managing and modifying relational databases. Its main functions include searching through, adding, updating, and removing data from databases.
- PL/SQL is a procedural language that builds upon SQL by incorporating loops, variables, and exception handling. Writing stored procedures, functions, and triggers in PL/SQL enables the creation of more intricate and reusable database logic.
3. Describe the basic PL/SQL structure.
The fundamental components of a PL/SQL block consist of:
The declaration section is where cursors, constants, and variables are defined.
The real PL/SQL code, including SQL statements and procedural logic, is written in the execution section.
Errors and exceptions that might arise during execution are handled under the exception handling section.
4. What is a trigger in PL/SQL?
A database object known as a trigger runs automatically in response to certain events on tables or views. It is used to apply the integrity constraint to database items. A database trigger is a PL/SQL software unit connected to a specific database table.
5. What are the uses of triggers?
Various uses of triggers include:
- Verify data changes
- Transparently record happenings
- Implement intricate corporate regulations.
- Continue to keep duplicate tables.
- Calculate the column values.
- Put in place intricate security authorizations
Every constant, variable, and parameter has a data type that determines its range of values and operations, format, and storage restrictions.
6. What does the trigger’s WHEN clause mean?
The WHEN clause outlines the requirements under which the trigger must fire.
7. When is it necessary to use a DECLARE block?
Anonymous PL/SQL blocks, like non-stored and stand-alone procedures, employ this statement. The statement in the stand-alone file should be used first when using them.
8. Why is WHERE CURRENT OF used in cursors?
When referring to the current row using an explicit cursor, we utilize this clause. Without specifically mentioning the row ID, this clause permits applying modifications and deleting the row that is presently being considered.
Syntax:
UPDATE table_name SET field=new_value WHERE CURRENT OF cursor_name
9. What is a PL/SQL table?
All that exists between PL/SQL tables and database tables is an object of type table modeling. They are a means of offering arrays for quicker processing, which are essentially temporary tables in memory.
These tables facilitate the transfer of massive volumes of data, which helps to speed up the process.
10. Why is SYS.ALL_DEPENDENCIES used?
The SYS.ALL_DEPENDENCIES variable determines all of the dependencies of procedures, packages, triggers, and functions that are available to the current user.
Name, dependency_type, type, referenced_owner, and other columns are returned.
11. Make a distinction between the cursors listed in the package requirements and the ones listed in the procedures.
- Since they have a local scope, the cursors specified in the procedures cannot be used by other procedures.
- Other methods can utilize and access the cursors that are defined in package specifications since they are handled with a global scope.
12. Define Join in PL/SQL.
Combining rows from two or more tables, views, or materialized views is known as a join query. When a query has more than one table in the FROM clause, the Oracle Database does a join. A join condition is present in the FROM or WHERE clause of the majority of these queries.
13. What is a view?
One or more tables can be joined to form a view. It is a virtual table with rows and columns, just like a real table, that is based on the result set of a SQL query. The construct VIEW statement can be used to construct a view.
14. What distinguishes the PL/SQL ROLLBACK FROM and ROLLBACK TO statements?
- To undo every modification made since the start of the transaction, use the ROLLBACK command.
- The transaction may only be undone up to a SAVEPOINT using the ROLLBACK TO command. The transaction is still ongoing even before the command is given because it cannot be turned back before the SAVEPOINT.
15. How can your PL/SQL code be debugged?
- To debug our code, we can use the DBMS_OUTPUT and DBMS_DEBUG statements:
- The output is printed to the standard console via DBMS_OUTPUT.
- The output is printed to the log file by DBMS_DEBUG.
Oracle PL/SQL Interview Questions and Answers for Experienced
16. Which virtual tables are accessible when the database trigger is running?
During the execution of the database trigger, the virtual tables THEN and NOW are accessible. THEN.column and NOW.column are the names of the table columns, respectively.
- The only available column for insert-related triggers is NOW.
- For the DELETE-related triggers, only the values in the THEN.column are accessible.
- For UPDATE triggers, both virtual table columns are supported.
17. To find the factorial of a number, write a PL/SQL block.
DECLARE
num INTEGER := 5; — Replace 5 with the desired number
factorial_result NUMBER := 1;
BEGIN
FOR i IN 1..num LOOP
factorial_result := factorial_result * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Factorial of ‘ || num || ‘ is: ‘ || factorial_result);
END;
18. In PL/SQL, how are nested tables created?
One sort of collection in PL/SQL is nested tables. They can be made at the schema level or in a PL/SQL block. These are comparable to a 1D array, with the exception that they have dynamic size extensions.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
name_of_table type_name;
Example of Nested Tables in PL/SQL:
DECLARE
TYPE deptname IS TABLE OF VARCHAR2(10);
TYPE budget IS TABLE OF INTEGER;
names deptname;
deptbudget budget;
BEGIN
names := deptname (‘Finance’, ‘Sales’, ‘Marketing’);
deptbudget := budget (89899, 67879, 98999);
FOR i IN 1 .. names.count LOOP
dbms_output.put_line(‘Department = ‘||names(i)||’, Budget = ‘ || deptbudget(i));
end loop;
END;
19. In PL/SQL, how will files be read and written?
Operating system text files can be read and written with the UTL_FILE package. It offers a constrained version of the operating system stream file I/O and may be used with server-side and client-side PL/SQL.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’,’myoutput’,’z’);
UTL_FILE.PUTF(file, ‘Value of func1 is %sn’,func1(2));
UTL_FILE.FCLOSE(file;
END;
20. Describe a possible issue that developers could run across while entering log data into a PL/SQL database table.
One common problem is excessive log writes that result in performance degradation. The primary application may become slower and resource contention may arise when log data is written to a database table.
To lessen these problems, developers must carefully plan and implement effective logging methods, take asynchronous logging into account, and make use of the right indexes.
21. How do you define a subquery? identify a few different PL/SQL subquery types.
A query wrapped in another query is referred to as a subquery in PL/SQL. Its objective is to get data intended for use in the context of the primary query or as a component of the dataset it generates.
The following are typical subquery types:
Scalar subquery: It returns a single value.
Correlated subquery: references columns from the outer query in the correlated subquery.
Nested Subquery: A subquery inside another subquery is called a nested subquery.
Multi-Row Subquery: Provides a result in many rows.
EXISTS Subquery: Verifies if rows are present.
IN Subquery: Verifies if a value corresponds to any value that a subquery has returned.
22. In PL/SQL, what is PRAGMA EXCEPTION_INIT?
A PL/SQL directive called PRAGMA EXCEPTION_INIT links an Oracle error code to a user-defined exception. Linking certain database failures to a custom exception makes error handling more accurate and illuminating and enables you to address them in a more controlled manner.
23. In PL/SQL, what are the functions of PLVrb and PLVcmt?
The PL/SQL utility package PLVrb (PL/Vision Row Builder) facilitates the construction of complex rows for use in dynamic SQL or as function return values.
By creating comments for procedures, functions, and triggers, the PL/SQL utility package PLVcmt (PL/Vision Commenter) assists developers in documenting their code.
Conclusion
There are many topics that a PL/SQL developer may come across in their job, and our list of PL/SQL interview questions and answers covers them all. Hone your skills with our Oracle PL/SQL training in Chennai.
