On this page, PL/SQL domain experts have compiled the greatest PL/SQL interview questions and answers. These interview questions will help you understand the importance of PL/SQL as well as the concepts underlying database triggers, the compilation procedure, packages, code tracing, functions, procedures, joins, and views in PL/SQL. SQL knowledge is required for the majority of employment roles because RDBMS is one of the most extensively utilised databases to date. This article on SQL Interview Questions addresses the most typical SQL questions (Structured Query Language). With the aid of this guide, you can learn everything there is to know about SQL, Oracle, MS SQL Server, and MySQL databases. If you are interested in Digital Marketing or Graphic Designing and want to learn these interesting courses then click on the links mentioned Digital Marketing Course and Graphic Designing course
Source: Safalta.com
Download these FREE Ebooks:
1.
2.
Website Planning and Creation
Top PL/SQL Interview Questions And Answers
1. What is PL/SQL?
Iteration and conditional branching are two procedural programming language features that are both included in Oracle PL/SQL, a language that is used for interactive SQL.
2. What exactly is a trigger and how can it be used?
A database object known as a trigger is one that automatically runs when certain events occur on tables or views. Applying the integrity constraint to database objects is done using it.
A database trigger is a piece of PL/SQL code connected to a specific database table. It's employed for:
- Data auditing modifications
- Keep transparent event logs
- Apply intricate business regulations
- Upkeep duplicate tables
- Calculate column values
- Put in place sophisticated security authorizations
3. What Types of Data are Available in PL/SQL?
In PL/SQL, there are numerous sorts of data types. As follows:
- Scalar: A scalar data type is an unstructured, one-dimensional data type. Scalar data types include CHAR, DATE, LONG, VARCHAR2, NUMBER, and BOOLEAN as some examples.
- Composite: The data type called composite is made up of many data types that are simple to update and have internal parts that may be used and changed collectively. RECORD, TABLE, VARRAY, and so on are a few examples.
- Reference: Pointers, or values that relate to other programmes or data components, are stored in the reference data type. A reference data type example is REF CURSOR.
- Large Object: Locators for large out-of-line items like video clips, graphic graphics, and other such items are kept in the large object data type. The large object data type includes the following examples: BLOB, BFILE, CLOB, and NCLOB.
4. What Constitutes a Trigger's Basic Components?
A trigger has three main components. As follows:
- A motivating phrase or circumstance
- A limitation
- An event
5.
How is the PL/SQL compilation process done?
Compilation involves the production of P-code, binding, and syntax verification. In PL/SQL code, syntax checking looks for compilation problems. After all, errors have been corrected, a storage address is assigned to the variables holding the data. Binding is the name given to this procedure. The P-code is a collection of instructions for the PL/SQL engine. The P-code for named blocks is preserved in the database and used the following time it is executed.
6. Describe the join.
A query that joins rows from two or more tables, views, or materialised views is called a join. When more than one table is included in the FROM clause of a query, the Oracle Database performs a join. In either the FROM or WHERE clause, the majority of these queries have at least one join condition.
7.
What is a view?
By connecting one or more tables, a view is produced. It is a virtual table with columns and rows, much like a real table, that is built using the results of a SQL statement. The CREATE VIEW statement allows for the creation of views.
8. What Components Make Up a PL/SQL Package?
An example of a PL/SQL package is:
- Table and record type statements in PL/SQL
- Functions and processes
- Cursors
- Tables, scalars, records, and other types of variables as well as constants
- names of exceptions and conventions for connecting an error number to an exception
- Cursors
9. What is your knowledge of the commands SAVEPOINT, ROLLBACK, and COMMIT?
COMMIT: During the current transaction, the COMMIT command saves changes to a database permanently.
At the conclusion of a transaction, the ROLLBACK command is used to reverse all changes made since the transaction's inception.
SAVEPOINT: During the processing of a transaction, the SAVEPOINT command saves the current point with a special name.
10. What Advantages Do PL/SQL Packages Offer?
PL/SQL packages offer a number of advantages. Here are a few of them:
- The freedom to decide whether to keep the information private or public is provided through enforced information hiding.
- Create from the top down: Before putting the modules into practice, we can design the user interface for the code that is concealed within the package.
- In terms of object persistence, all PL/SQL objects in the application treat objects stated in a package specification as global data. In one module, we can alter the package, and in another, we can reference those modifications.
- Object-oriented Design: The package allows programmers full control over the modules and data structures it contains.
- Providing a certain degree of transaction integrity is a guarantee.
- Performance Enhancement: All programme objects stored in the database are constantly being checked for authenticity by the RDBMS. Additionally, it improves the efficiency of packages.
11. How does exception handling work?
Runtime errors are handled by a process called exception handling. It is modifiable in PL/SQL. The exception block that PL/SQL offers raise the exception, making it easier for the programmer to identify and fix the problem. The program's error handling code is included when an error happens. In PL/SQL, two distinct exception types are defined.
- Individual exception
- Exception declared by the system
12. List a Few Known Exceptions
Predefined exceptions can take the following forms:
- A SELECT statement that returns 'NO DATA FOUND' for a single row.
- SELECT query returning too many rows from a single row
- INVALID CURSOR: A mistake was made in the cursor action
- ZERO DIVIDE: An attempt at zero division
13.
What Kinds of SQL Statements Are There?
These are the five different categories of SQL statements:
DDL: A database structure or schema can be created with the use of the data definition language (DDL). The five different categories of DDL commands in SQL are CREATE, DROP, ALTER, RENAME, and TRUNCATE.
DML: You can add, edit, and delete data from a database instance using the data manipulation language (DML). DML is in charge of doing many types of modifications to database data. Three fundamental commands—INSERT, UPDATE, and DELETE—allow the database application and the user to add data and information.
The commands GRANT and REVOKE in the data control language (DCL) are used to grant rights and permissions. Other permissions regulate the database system's parameters.
TCL: Commands in the Transaction Control Language (TCL) handle database transactions. COMMIT, ROLLBACK, and SAVEPOINT are a few of the TCL commands.
DQL: Data from the database is retrieved using the Data Query Language (DQL). It only supports the SELECT command.
14. What are the various PL/SQL code tracing techniques?
A key method for gauging the performance of the code during runtime is code tracing. The various approaches of code tracing include:
DBMS APPLICATION INFO
DBMS TRACE
DBMS MONITOR and DBMS SESSION
tkprof and trcsess utilities
15. What Kinds of Parameters Does PL/SQL Support?
In PL/SQL, there are three different kinds of parameters. These are what they are:
IN: You can transmit values to the procedure that is being called using the IN argument. You can use the default settings for the IN parameter. It operates consistently and cannot be altered.
OUT: The caller receives a value from the OUT argument. Since the OUT parameter is an uninitialized variable, expressions cannot use it.
IN OUT: The IN OUT parameter gives a method its initial values and then returns to the caller the changed values. Give a value to this parameter and treat it like an initialised variable.
16. How do PL/SQL Records work?
Records in PL/SQL are a grouping of values. In other words, PL/SQL records are a collection of numerous pieces of data, each of which is of a simpler type and can be linked to another field.
In PL/SQL, the following three record types are supported:
- Table-based records
- records produced by software
- Records based on a cursory search
17. The purpose of an index in a table
In order to provide quick access to rows in a table, we employ an index. An index enables faster access to data for operations that only return a tiny portion of a table's records.
18. What Sets Functions, Procedures, and Packages Apart in PL/SQL?
PL/SQL functions' primary function is to compute and return a single value. The functions must return a specific value in the return type that is stated in their specifications.
Procedures: A return statement in a procedure can simply halt its execution and send the caller back. Procedures do not have a return type and should not return any values. Otherwise, procedures are essentially comparable to functions with the exception that they can return numerous values.
Packages are schema objects that group PL/SQL types, items, and subprograms that are logically connected. Packages can also be thought of as a collection of variables, record TYPE statements, procedures, and functions. Modularity is provided by packages, which facilitates the creation of applications. Information is concealed from unauthorised users using packages.
19. Why Do We Use Database Links?
To establish communication between several databases or environments, such as test, development, and production, database connections are employed. Database links, which are read-only, can also be used to retrieve more information.
20. What exactly is a stored procedure?
A named PL/SQL block or series of statements that carry out one or more particular tasks is referred to as a stored procedure. It is comparable to a procedure in other programming languages. It can be frequently executed because it is kept in the database. It can be nested, called, and parameterized and is kept as a schema object.
21. What does it mean when a procedure is overloaded?
Procedure overloading is the practice of repeating the name of a procedure with various data types and parameters in several locations.
22. What are expressions used for?
A string of literals and variables that are separated by operators make up an expression. In PL/SQL, operators are utilised for data manipulation, comparison, and computation. Operators and operands are the two constituent parts of an expression.
23. Which Cursor Attributes Do a Saved DML Statement's Results When It Is Executed?
The output of the statement is stored in four cursor attributes. The four characteristics are:
SQL% FOUND
SQL% NOTFOUND
SQL% ROWCOUNT
SQL% ISOPEN
24. What is a cursor? Why is it necessary?
When a SQL statement is performed, a cursor is a momentary work area that is established in system memory. The row of data accessible by a select statement and its information are both contained in a cursor. The data is retrieved from the database and temporarily stored in this work area for manipulation. Although a cursor can hold many rows, it can only process one row at a time. To handle rows independently for queries, a cursor is needed.
25. What Kinds of Cursors Are There?
Two categories of cursors exist:
Implicit Cursors: When PL/SQL executes a SQL statement, it automatically creates a cursor without asking for one; these cursors are known as implicit. The following statements use implicit cursors:
INSERT
UPDATE
DELETE
SELECT
Explicit Cursor: For queries that return several rows, a programmer declares and names an explicit cursor. An explicit cursor is a SELECT statement that is expressly defined in the declaration section of the current block or in a package specification. The PL/SQL instructions that are used for explicit cursors are as follows:
OPEN
FETCH
CLOSE
26. What does the command function called Open Cursor mean?
The following actions are carried out when a cursor is opened using the OPEN cursor command:
- Set aside a section of memory for processing
- Use the SELECT statement.
- Using the memory addresses, give input variables values.
- The active set of rows that satisfy the selection criteria should be noted.
- Put the cursor immediately before the first row of the active set.
27. How Can a Trigger Be Removed?
You must use the command DROP TRIGGER to remove a trigger.
28. What Benefits Do Store Procedures Offer?
Numerous benefits of stored procedures can support the development of advanced database systems. The following is a list of some benefits of stored procedures:
- Improved efficiency
- Increased productivity
- Easy of use
- Higher scalability
- Interoperability
- Advance protection
- Replication
29. What are the Different Types of Schema Objects that PL/SQL Can Create?
The types of schema objects produced by PL/SQL are numerous. Following are a few of them:
- Procedures, packages, and functions that are stored
- Tables of objects, types of objects, and object views
- Triggers for databases
- Link databases
- Cursors
- Table
- View
30. Why are Implicit Records Beneficial?
Since they do not require hard-coded descriptions, implicit records are useful. Implicit records are built on top of database table records, so whenever those records change, the implicit records will also immediately update.
31. What are the Different PL/SQL Commenting Methods?
Comments in PL/SQL improve readability by outlining the goal and function of individual lines of code. In PL/SQL, there are two sorts of comments available. These are what they are:
Single-line Comments: A single-line comment begins with a double hyphen (- -) and continues through the end of the line.
Multi-line Comments: Multi-line comments can stretch across many lines and begin with a slash-asterisk (/*) and end with an asterisk-slash (*/).
32. What is %TYPE?
To declare a column in a table that contains the value of that column, use the %TYPE property. The table column's data type matches that of the variable's data type.
33.
What is %ROWTYPE?
The structure of the records in a table is declared in a variable using the %ROWTYPE attribute. The columns of the table and the variable share the same data type.
34. Explain the differences between temporary and permanent tablespaces.
Sort structures and other transient objects are kept in a temporary tablespace, whilst the actual objects of the database are kept in a permanent tablespace.
35.
What a number of triggers may be used on a table?
A table may have up to 12 triggers added to it.
36. What Is a Mutating Table Error?
When a trigger tries to modify an active record, it encounters a mutating table error. Views or temporary tables can be used to solve it so that the database chooses one and updates the other.
37. What can you do with the PLVtab when you display the contents of PL/SQL tables?
When you display the contents of PL/SQL tables, PLVtab gives you the ability to accomplish the following:
- Select whether to show or hide the row numbers for the table values.
- Display a prefix before each table row.
- Display or hide the table's header
38. How do you save or insert a message into a table?
You can load individual messages by using the add text procedure to load them into a table, or you can load groupings of messages from a database table by using the load from the DBMS process.
39. How do pseudo columns function and what are they? Pseudo columns: How Do They Fit Into Procedure Statements?
Although they aren't actual table columns, pseudo columns behave like them. In SQL queries, pseudo columns are utilised to retrieve certain data. Pseudo columns cannot be used directly in a procedural language, despite the fact that PL/SQL understands them as a component of SQL statements. The following pseudo columns are employed:
- NEXTVAL and CURRVAL
- LEVEL
- ROWID
- ROWNUM
40. What use does the || Operator serve?
The || operator is used to combine the strings. Both DBMS OUTPUT.put line and select statements use the || operator.
41. Why are Sqlcode and Sqlerrm crucial for pl/SQL developers, and what are the differences between them?
SQLCODE returns the value of the error number for the most recent error found. The most recent error message is returned by the SQLERRM function. The errors that occurred in the code can be reported or saved in the error log database using them in exception handling. These are especially crucial for the WHEN OTHERS exemption.
42. How does raising application error work?
By using this procedure, stored subprograms can transmit user-defined error messages. By notifying failures to your programme, you can stop throwing unhandled exceptions. Both the executable portion and the exceptional section contain it.
43. How can you tell if an update statement was executed or not in PL/SQL?
You can tell whether an UPDATE statement successfully updated any data by looking at the SQL% NOTFOUND attribute. This variable returns TRUE if the most recent execution of a SQL statement had no impact on any rows.
44. Describe the Day-to-Day PL/SQL Activities.
- Make database objects, such as tables, synonyms, and sequences.
- Implement company regulations; develop functions and procedures.
- Put restrictions, triggers, and other business rules in place.
- Make cursors to manipulate data
45. When a cursor is open, how may a PL/SQL block be located?
The cursor status variable %ISOPEN can be utilised to locate the PL/SQL block.
46. What are your PL/SQL skills for pragma exception init?
An exception name and an Oracle error number are linked together by the pragma exception init command in PL/SQL. This makes it possible to define a unique handler for any internal exception and refer to it by name.
47. What distinguishes a stored procedure from a stored function in PL/SQL?
The following are the main distinctions between stored procedure and stored function:
- While returning the value in a saved function is necessary, doing so in a stored procedure is not.
- While a saved function can only contain either an input parameter or an output parameter, a stored procedure can have both input and output parameters.
- In contrast to stored functions, where handling exceptions is not allowed, stored procedures can.
48. How can I display records from an employee table that has the highest salary?
Select * from emp where sal= (select max(sal) from emp)
49. How Do You Disable a Trigger Syntax?
DISABLE TRIGGER NAME IN ALTER TRIGGER;
50. How can I show the highest salary from a table of employees?
To show the highest wage from an employee table, use the following code:
from emp, select max(sal);
51. What Command is Executed to Remove a Package?
DROP PACKAGE is the command used to remove a package.
52. What is the best way to show the second-highest salary from an employee table?
Select max(sal) from emp where sal is not in ( select max(sal) from emp
53. How does PL/SQL allow you to view user-defined functions and procedures?
User-defined functions and procedures are kept in the table USER SOURCE. The function and method names must be supplied in uppercase in order to be examined (in select command). To view the source code of a user-defined function or method, use the command:
'PROCEDURE NAME' as the name; selecting text from user source;
54. Describe the join.
Depending on the relationship between the fields of the tables, the join keyword is used to query data from various tables. In Joins, Keys are crucial.
55. What does the DBMS OUTPUT package in PL/SQL do?
The DMS OUTPUT package is used to display the PL/SQL output on the screen. DBMS OUTPUT includes functions like get line, put Line, new line, and a lot more. The data in the line is displayed using the put line operation, which is a component of the DBMS OUPUT package.
56. Describe a view.
- The data are contained in a view, which is a virtual table.
- The memory required by a view is not necessary.
- On many tables, views may be built.
57. How is a stored procedure executed?
Executing a saved procedure involves two steps:
- Use the keyword EXECUTE. Additionally, the word EXEC may be used.
- PL/SQL block that calls the procedure's name.
EXECUTE procedure_name;
Or
Exec procedure_name;
58. What distinguishes the ANY and ALL operators?
Value is compared to each value that the subquery received using the ALL operator.
- > ALL means the value is higher than the maximum.
- ALL indicates values below the minimum.
- ALL is equivalent to condition NOT IN.
Value is compared to each value that the subquery returned using the ANY operator. ANY operator is a synonym for SOME.
- > ANY means more than just the bare minimum.
- ANY indicates a value that is less than the maximum.
- The IN operator is the same as = ANY.
59. How Can a Function Be Created?
Below is the syntax for writing a CREATE function:
SUBSTITUTE function name
return datatype RETURN
{IS | AS}
DECLARE
VARIETYPE OF DATA;
BEGIN
function body
function name END;
60. How can an Init.ora file be changed to a Spfile?
By making a spfile from the pfile command, one can change from an Init.ora file to a Spfile.
61.
Describe a subquery.
What types does it have?
An internal query is known as a subquery. The inner query is referred to as the subquery, and the outer query is referred to as the main query. First, a subquery is conducted, and its output is provided to the main query.
Two categories of subquestions exist:
- Correlated
- Non-correlated
62. How does PL/SQL read and write files?
Using the UTL FILE package, one can read and write text files used by the operating system. Both client-side and server-side PL/SQL are supported, and it offers a constrained variant of the operating system's stream file I/O.
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;
63. In PL/SQL, how do you create nested tables?
Nested tables are one of the collection types in PL/SQL. They can be added at the schema level or in a PL/SQL block. These resemble a 1D array, with the exception that their size can be dynamically increased.
The following syntax must be used to build a nested table:
TYPE type_name IS TABLE OF element_type [NOT NULL];
name_of_table type_name;
An example for creating nested tables in PL/SQL is as follows:
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;
/
