50 Top PL/SQL Interview Questions and Answers in 2022

Posted in /   /  

50 Top PL/SQL Interview Questions and Answers in 2022

Saumya Rastogi
Last updated on September 21, 2022

    Today’s world has witnessed the overwhelming production of data every day. Around 2.5 quintillions of data are generated each day, and a study has stated that more than 90% of the world’s data is produced in just the last two to three years.

    The immense generation of data has resulted in the need for a suitable and well-structured set of query languages. A query language is a computer language that helps us process a large amount of data and retrieve specific and meaningful data. One of the globally used query languages is PL/SQL.

    So, let us begin with what exactly PL/SQL is and what its uses are. We will also highlight career opportunities in PL/SQL that you need to know. Lastly, we will go through frequently asked PL/SQL interview questions along with their elaborated answers.

    What is PL/SQL?

    Procedural Language extensions to the Structured Query Language is the full form of PL/SQL. It is an exceptional query language as it combines the data manipulation feature of SQL and the processing power offered by procedural languages.

    Furthermore, the principal aim of PL/SQL is to process all SQL statements smoothly by enhancing a database’s security, sturdiness, and portability. In the early 90s, Oracle developed PL/SQL to improve the Structured Query Language’s (SQL’s) capabilities.

    PL/SQL incorporates a wide array of features similar to other programming languages, like collections, iterative and conditional statements, procedures, triggers, arrays, functions, etc.

    Want to learn and master PL/SQL? You can get started here immediately.

    Career Opportunities in PL/SQL

    Individuals wishing to get a job in PL/SQL should have a strong background in SQL, C programming language, databases, and text editors. However, it is easy to learn these skills quickly. A PL/SQL developer is responsible for developing efficient SQL queries to enhance a database’s performance and analyze the available data to provide robust solutions to end-users.

    Here is a list of the skills required to become a PL/SQL developer:

    • In-depth understanding of the Oracle database and its tools.
    • Thorough knowledge of procedures, C programming language, query writing and execution, and triggers.
    • Firm understanding of software development life cycle (SDLC).
    • Experience in designing and implementing ETL processes.
    • Ability to tune SQL queries’ performance.
    • Knowledge of creating, designing, and implementing data models.

    While you work as a PL/SQL developer, you will get an opportunity to work with various domains of Oracle. In addition, while developing client-side queries as a PL/SQL developer, you will also get the chance to manage SQL server-based queries. You can even transform your PL/SQL career into data-level or solution-level architects, as you possess a sound understanding of a PL/SQL code‘s functionalities and features.

    Furthermore, you can switch to an analyst role, where you need to work with analytical tools to produce meaningful business insights. Therefore, pursuing a career in the PL/SQL domain offers a wide variety of job roles. Though a PL/SQL developer is the primary role, there are many other roles, such as data analyst, SQL developer, ETL developer, PL/SQL administrator, and PL/SQL performance optimization developer, that you can switch to.

    50 Top PL/SQL Interview Questions and Answers

    Here are the most important PL/SQL interview questions along with their detailed answers, categorized into three levels: Beginner, Intermediate, and Advanced.

    Beginner-Level PL/SQL Interview Questions

    1. Define PL/SQL.

    Answer: PL/SQL stands for Procedural Language extensions to the Structured Query Language. It is an extended version of SQL, enabling us to use functions, procedures, control structures, etc., to design and create database applications. Furthermore, PL/SQL is a powerful query language that combines SQL and procedural languages’ data manipulation and processing features.

    Alternatively, we can say that PL/SQL uses SQL to instruct the compiler about ‘what to do.’ Further, it uses a procedural approach to guide the compiler on ‘how to do.’ PL/SQL also supports object-oriented concepts, loops, and conditional statements analogous to other database languages.

    2. State some differences between PL/SQL and SQL.

    Answer: Oracle developed PL/SQL to overcome some significant disadvantages of SQL. The following table shows dissimilarities between PL/SQL and SQL:

    SQL PL/SQL
    It is a natural language used to communicate with databases and stands for Structured Query Language. It is an extension of SQL and is a Procedural Language/Structured Query Language.
    SQL does not support procedural features, like looping and condition testing. As PL/SQL is a procedural language, it supports conditional and looping statements.
    The database server executes a single SQL query at a time, consuming a lot of time and effort. In PL/SQL, the database server accepts a complete block of statements and executes all of them simultaneously. Therefore, it requires less time and also eliminates network traffic.
    SQL does not incorporate any error handling procedure. You can use customized error handling procedures in PL/SQL.
    SQL does not have variables. PL/SQL consists of variables and data types.
    It is an object-oriented language. It is an application-oriented language.
    The primary objective of using SQL is to write and execute queries, DDL and DML command. PL/SQL supports functions, triggers, program blocks, and packages.

    3. List out significant features of PL/SQL.

    Answer: Some of the most notable features of PL/SQL are:

    • It supports processing multiple queries in one go through a single PL/SQL command.
    • As PL/SQL is a procedural language, it supports looping, branching, and decision making.
    • A code, once developed in PL/SQL, can be stored as different units, like functions, triggers, procedures, and packages. In addition, we can use these units for developing other applications.
    • PL/SQL incorporates an exception handling block that supports exception handling .
    • Furthermore, we can validate and check the data for errors and bugs in PL/SQL before performing manipulations.
    • Finally, the code written using PL/SQL is portable. We can execute that code on any operating system or computer hardware having an Oracle engine.

    4. Explain different kinds of data types in PL/SQL.

    Answer: Data types in computer programming tell us about the type of a variable’s value and what operations can be performed on it. PL/SQL has four different types that are as follows:

    • Scalar Data Type

    This kind of data type incorporates a single or an atomic value. Therefore, it does not have internal components. Examples of Scalar Data Types are VARCHAR2, CHAR, DATE, LONG, NUMBER, and BOOLEAN.

    • Composite Data Type

    This kind of data type involves internal components. We can use these internal components and manipulate them easily. Examples of this kind of data type are RECORD, TABLE, and VARRAY.

    • Reference Data Type

    As its name suggests, a reference data type involves a pointer that refers to other data items or program items. An example of Reference Data Type is REF CURSOR.

    • Large Object Data Type

    Large Object (LOB) Data Type also involves pointers that refer to large objects. These objects are isolated from other program items, such as images, text, video clips, etc. Examples of LOB Data Types are Binary LOB, Character LOB, NCHAR LOB, and Binary File (BFILE).

    5. What are %ROWTYPE and %TYPE data types?

    Answer: The PL/SQL’s %TYPE attribute provides a table column’s or a variable’s data type. We use %TYPE when we want to declare variables that hold the values of a table column. The PL/SQL’s %ROWTYPE attribute declares a variable as a record. However, that variable has the same structure as a row in a table. A record in PL/SQL is similar to the row in a table.

    6. Describe PL/SQL packages.

    Answer: A PL/SQL package is an object schema that categorizes logically connected subprograms, items, and types. It usually contains subprograms, exceptions, variables, cursors, constants, procedures, and functions.

    The Oracle database stores compiled PL/SQL packages in it. A PL/SQL package has two major components - package specification and package body. The package specification component defines public objects.

    As these objects are public, we can access or fetch them from outside the package. In addition, if the package specification consists of cursors and subprograms, then the package body should contain queries and code for their implementation.

    The next component of the PL/SQL package is the package body, containing subprograms’ and cursors’ implementation. It involves queries for the execution of cursors and code for subprograms. Furthermore, the package body allows us to declare private variables. In addition, it involves a dedicated section to handle exceptions.

    7. Explain PL/SQL’s basic structure.

    Answer: As PL/SQL is a block-structured language, its programs are written and divided into logical code blocks. The structure of the PL/SQL block is:

    DECLARE
    <declaration section>
    BEGIN
    <executable commands>
    EXCEPTION
    <exceptional handling>
    END;

    Each code block in PL/SQL program consists of three parts:

    • Declarations:

    This part of the PL/SQL block is optional and is used to define all variables, subprograms, and other elements that are to be involved in a PL/SQL program. While defining any element, we must begin with the ‘ DECLARE ’ keyword.

    • Executable Commands:

    The next part of the PL/SQL block starts with the keyword ‘BEGIN’. In addition, this section of the PL/SQL block ends with the ‘END’ keyword. This part of the PL/SQL block is mandatory. All executable PL/SQL statements or commands are present between these two keywords. However, there should be at least one executable statement or command. If we don’t want to execute anything, it should contain the NULL command.

    • Exception Handling:

    The last part of the PL/SQL block is exceptional handling. Like the declarations part, this part is also optional to use. It involves one or more exceptions that manage errors occurring in the PL/SQL program. Make sure to add this part before the ‘END’ keyword.

    8. Define a PL/SQL cursor and list its types.

    Answer: A pointer to the memory area or context area, which consists of all SQL statements and information about processing these statements, is called a PL/SQL cursor. A PL/SQL cursor helps us to fetch and process multiple rows at a time. Alternatively, a PL/SQL cursor selects multiple rows from a database , retrieves the data from the rows, and processes each row individually. Implicit cursor and explicit cursor are two different cursors in PL/SQL.

    • Implicit Cursor:

    If we execute any SQL statement, like INSERT, DELETE, UPDATE, and SELECT, PL/SQL creates a cursor without defining it. Such a type of cursor is called the implicit cursor.

    • Explicit Cursor:

    We define an explicit cursor for queries that return two or more rows from a database. However, we need to follow four steps for declaring an explicit cursor:

    • Declaring the cursor
    • Opening the cursor
    • Retrieving row using the cursor
    • Closing the cursor

    9. Explain triggers in PL/SQL.

    Answer: In general, we know that trigger means activate. Triggers in PL/SQL are previously developed or stored programs. An Oracle engine activates or invokes triggers automatically when any database-related events occur. Below are certain events when an Oracle engine invokes or fires triggers:

    • A database operation, like SHUTDOWN, SERVERERROR, LOGOFF, STARTUP, and LOGON.
    • A data manipulation language (DML) statement, like UPDATE, INSERT, SELECT, and DELETE.
    • A data definition language (DDL) statement, like CREATE, ALTER, and DROP.

    10. List and explain types of PL/SQL blocks.

    Answer: There are two kinds of PL/SQL blocks, as listed below:

    • Anonymous Block

    Anonymous blocks are those that do not have names assigned to them. We cannot store these blocks as database objects in the server. Also, we need to develop and use anonymous blocks in the same session.

    • Named Block

    PL/SQL blocks with unique names are known as named blocks. We can store named blocks in the server as database objects. Moreover, it is possible to use named blocks until they are present in the server as database objects.

    11. Explain the terms SAVEPOINT, COMMIT, and ROLLBACK.

    Answer: COMMIT When we use any DML command in the PL/SQL program, it makes changes to the data present in the database buffer. However, the actual database does not get affected and remains unchanged. We use the COMMIT statement to save transaction changes or changes made to the rows after using any DML command to the database permanently.

    Syntax: COMMIT;

    ROLLBACK If we wish to undo any change made in the current transaction, we can use the ROLLBACK statement. This statement stops the current transaction from running and undoes modifications made during the transaction. For example, consider that we have deleted a wrong row from the database, and we need to restore that deleted row. In such a scenario, we must use the ROLLBACK statement.

    Syntax: ROLLBACK;

    SAVEPOINT The SAVEPOINT statement is ideal for longer transactions. This statement divides a longer transaction into smaller sections and marks points in a transaction. Unlike the ROLLBACK statement, the SAVEPOINT statement enables only a part of a transaction to roll back.

    Syntax: SAVEPOINT <SAVEPOINT_NAME>;

    12. State the difference between syntax errors and runtime errors.

    Answer: Runtime errors in PL/SQL are the errors the exception-handling section of the PL/SQL block handles. One of the examples of the runtime error is the SELECT statement that does not return a single row from the database. All locks and changes made to the transactions before using SAVEPOINT are preserved. Syntax errors in PL/SQL can be a spelling mistake or any syntax written in the wrong format. The PL/SQL compiler identifies syntax errors from the PL/SQL program.

    13. Define constraining and mutating tables.

    Answer: The general meaning of mutating is change. Therefore, a mutating table is a table that gets modified when we use insert, update, or delete statements, i.e., DML statements. On the other hand, a table that we require to access and read for a referential integrity constraint is referred to as a constraining table.

    14. Explain the way of writing comments in PL/SQL.

    Answer: Comments in computer programming help improve the readability of the code. In addition, comments tell what actually the code is for and how it functions. Adding comments in a PL/SQL program does not affect its functionality. There are different methods for writing single-line and multi-line comments in PL/SQL.

    • Single-line comments

    We can write single-line comments using the ‘--’ symbol before the actual comment.

    • Multi-line comments

    To write multi-line comments in PL/SQL, we use the ‘/* */’ symbol. The syntax is: /* comment lines */ . Below is an Instance of PL/SQL’s Single-line and Multi-line Comments:

    DECLARE
    num NUMBER(5); --it is a local variable
    BEGIN
    num := &p_num; /*it is a host variable
    used only in the program body*/
    END;

    15. Define formal parameters and actual parameters.

    Answer: Formal parameters are those that are declared in the procedure header and are called in the procedure body. To understand formal parameters, let us see one example.

    CREATE OR REPLACE FUNCTION square_area (side NUMBER)
    RETURN NUMBER IS

    Here, side NUMBER is a format parameter. Parameters that are present in the procedure call or function call statement are actual parameters. Below is an example of an actual parameter:

    BEGIN
    DBMS_OUTPUT.PUT_LINE(square_area (5));
    END;
    /

    16. Explain three different modes of a parameter.

    Answer: Parameters in PL/SQL have three distinct modes, as explained below:

    • IN Parameters:

    The IN parameter enables us to pass values to the called procedure. We can initialize IN parameters to default values. Furthermore, we cannot assign any other values to IN parameters, as they are constant.

    • OUT Parameters:

    Unlike IN parameters, OUT parameters are not initialized. We cannot use OUT parameters in expressions. Parameters that return values to the caller are OUT parameters. It is indispensable to specify this type of parameter.

    • IN OUT Parameters:

    IN OUT parameters are the combination of IN parameters and OUT parameters. Parameters that pass values to the procedure and then return values to the caller are IN OUT parameters. However, we need to assign values to these parameters, and they function like initialized variables.

    17. State and describe PL/SQL exceptions.

    Answer: Any error taking place in the PL/SQL program causes it to terminate unexpectedly. Therefore, the PL/SQL program involves an error-handling code that handles errors occurring in the PL/SQL block. This error-handling code is present in the EXCEPTION section of the PL/SQL block. Following are the three different kinds of PL/SQL exceptions:

    • Undefined Exceptions: Very rare errors that occur with no names defined.
    • Pre-defined Exceptions: Common errors taking place in a PL/SQL block with pre-defined names.
    • User-defined Exceptions: Errors that violate business rules but do not result in runtime errors.

    18. Explain predefined exceptions in PL/SQL.

    Answer: Predefined exceptions involve rare errors that occur with no default names. Below are predefined exceptions in PL/SQL:

    • NO_DATA_FOUND: The SELECT statement involves only a single row and returns no data.
    • INVALID_CURSOR: This exception is raised when an illegal operation takes place.
    • TOO_MANY_ROWS: The SELECT statement uses a single row and returns multiple rows as output.
    • ZERO_DIVIDE: This exception is thrown when a PL/SQL code attempts to divide by a zero.

    19. Define expressions. What are the different kinds of expressions in PL/SQL?

    Answer: An expression in PL/SQL is a series of variables and literals. These literals and variables are separated using operators. Alternatively, an expression is a combination of operators and operands. In addition, we use operations in PL/SQL to compare, manipulate, and calculate data. Operands in a PL/SQL expression can be a function call, constant, or variable, whereas operators are ‘+’ and ‘*.’ There are four kinds of expressions used in PL/SQL that are as follows:

    • Boolean Expression: Ex. ‘code’ LIKE ‘co%e’.
    • Date Expression: Ex. SYSDATE>TO_DATE(‘02-JUL-2022’, “dd-mm-yy”).
    • Numeric Expression: Ex. 10*20+5.
    • String Expression: Ex. LENGTH(‘CODE CHALLENGE’ || ‘CH’).

    20. Explain PL/SQL records.

    Answer: A record in PL/SQL is a kind of data structure used for holding data items. It is a group of several pieces of data or values. In addition, it involves various data fields analogous to a row in a database’s table. All values or pieces of data in a record are of the same types and related to each other. For instance, consider that we need to keep a record of books available in a library, where each book has attributes, such as Author, ID, and Title. The best data structure in PL/SQL to store all these fields of books available in a library is known as a record. In the PL/SQL block’s Declaration segment, we define records. Records in PL/SQL are of three types:

    • Table-based Record: We use the table name with the %ROWTYPE attribute to define table-based records.

    Syntax:

    DECLARE 
     record_name table_name%ROWTYPE;

    Example:

    DECLARE
     r_address address%ROWTYPE;

    In the above example, we heated a record named r_address analogous to the address table.

    • Cursor-based Record: We use an explicit cursor with the %ROWTYPE attribute to define a cursor-based record.

    Syntax:

    DECLARE
     Record_name cursor_name%ROWTYPE;

    Example:

    DECLARE
     CURSOR c_address IS
     SELECT house_no, street_name, city_name
     FROM address;
     r_address c_address%ROWTYPE;

    In the above example, first, we have declared an explicit cursor c_address. This cursor retrieves data from the address table’s columns house_no, street_name, city_name. After creating an explicit cursor, we declared a record named r_address, analogous to the c_address cursor.

    • Programmer-defined Record: We use a programmer-defined record to create a record whose structure does not depend on the existing ones. First, we will define a record type containing the record’s structure. Later, we will declare that record using its record type.

    Syntax: Defining a record type:

    TYPE record_type IS RECORD(
     field_name1 data_type1 [[NOT NULL] := | DEFAULT default_value];
     field_name2 data_type2 [[NOT NULL] := | DEFAULT default_value];
     ...
    );

    Declaring a record depending upon the above record type:

    record_name record_type;

    Example:

    DECLARE
    TYPE r_employee_contact_t
    IS
    RECORD
    (
    employee_name employees.name%TYPE,
    first_name contacts.first_name%TYPE,
    last_name contacts.last_name%TYPE
    );
    r_employee_contacts r_employee_contact_t;
    BEGIN
    NULL;
    END;

    Intermediate-Level PL/SQL Interview Questions

    21. Can you explain the difference between ROWTYPE and ROWTYPE TO?

    We use the ROlLBACK command to roll back or undo all the changes from the beginning of a transaction. On the other hand, we use the ROLLBACK To command to roll back or undo the changes till the last SAVEPOINT.

    22. Can you tell the use of SYS.ALL_DEPENDENCIES?

    SYS.ALL_DEPENDCIES describes the dependencies between procedures, packages, functions, package bodies, and triggers, that the current user can access.

    23. What do you know about the keywords SYSDATE and USER?

    SYSDATE: It returns the current date and time on the local database server.

    Syntax: SYSDATE

    USER: It retrieves the user id of the current session.

    Syntax: USER

    Both these commands do not take any arguments.

    24. How will you assign the name to an unnamed PL/SQL Exception block?

    We can assign the name to an unnamed PL/SQL exception block using the pragma called EXCEPTION_INIT.

    Syntax: DECLARE exception_name EXCEPTION;

    PRAGMA EXCEPTION_INIT (exception_name, error_code);

    BEGIN

    // PL/SQL Logic

    EXCEPTION WHEN exception_name

    THEN

    // Steps to handle exception

    END;

    25. State the use of WHERE CURRENT OF in cursors.

    We use the WHERE CURRENT OF clause when we want to update or delete the records that are referenced by the SELECT FOR UPDATE statement. This means that this clause enables you to delete or update the records the cursor fetches it recently. Syntax: UPDATE table_name SET set_clause WHERE CURRENT OF cursor_name; or DELETE FROM table_name WHERE CURRENT OF cursor_name;

    26. How will you debug the PL/SQL code?

    To debug the PL/SQL code, we have to use the following two statements:

    DBMS_OUTPUT: It prints the output of the PL/SQL code to the standard console.

    DBMS_DEBUG: It prints the output of the PL/SQL code to the log file.

    27. Which command will you use to delete a PL/SQL package?

    To delete a PL/SQL package, we use the DROP PACKAGE command. DROP PACKAGE [BODY] schema_name.package_name;

    28. Can you tell in which statements do the outcomes of the execution of DML statements get saved?

    The following are the four different cursor attributes where the outcomes of the execution of DML statements get saved:

    • SQL%FOUND

    It returns true when the DML statement processes at least one row.

    • SQL%NOTFOUND

    It returns true when the DML statement does not process any row.

    • SQL%ROWCOUNT

    It returns the number of rows the DML statement processes.

    • SQL%ISOPEN

    It returns true if the cursor is open.

    29. Enumerate functions that are available for manipulating character data.

    The following are the functions available for manipulating character data:

    • LEFT: It returns the specified number of characters from the left part of the string.
    • RIGHT: It returns the specified number of characters from the right part of the string.
    • SUBSTRING: It returns the specified number of characters from the specific start point in the string.
    • TRIM: It trims all the whitespaces present on the left of the string.
    • RTRIM: It trims all the whitespaces present on the right of the string.
    • UPPER: It converts all the characters in a string to the upper case.
    • LOWER: It converts all the characters in a string to the lower case.

    30. Can you tell about the PL/SQL cursor exceptions?

    The following are the two different PL/SQL cursor exceptions:

    • CURSOR_ALREADY_OPEN
    • INVALID_CURSOR

    31. What do you know about INSTEAD OF triggers?

    When we want to update a view that we cannot do it directly through SQL DML statements, we use the INSTEAD OF triggers.

    32. Enlist different packages available in PL/SQL for developers.

    The following are different packages available in PL/SQL for developers:

    • DBMS_ALERT: It alters an application when anything in a database changes using a trigger.
    • DBMS_OUTPUT: It displays the output of PL/SQL blocks, triggers, packages, and subprograms.
    • UTL_FILE: It allows PL/SQL programs to read and write OS text files.
    • UTL_SMTP: It allows PL/SQL programs to send emails over SMTP.
    • DBMS_PIPE: This package allows different sessions to communicate over a named pipe.
    • UTL_HTTP: This package helps PL/SQL programs to make HTTP callouts.
    • HTF and HTP: It lets PL/SQL programs to generate HTML tags.

    33. Differentiate between SGA and PGA.

    The following table highlights the differences between SGA and PGA:

    SGA PGA
    SGA stands for System Global Area. PGA stands for Program Area.
    It stores data and control information about a single Oracle database instance. It stores data and control information about a single Oracle process.
    A shared memory region is available for components. There is no shared-memory region.
    Example: SQL areas and cached data blocks. Example: SQL work area and session memory.

    34. Enlist some major advantages of PL/SQL packages.

    The following are the remarkable advantages of PL/SQL packages:

    • PL/SQL packages provide containers for subprograms, which makes code management easy.
    • They follow a top-down design approach and separate implementations and specifications.
    • When we need to change any subprogram, there is no need for its dependent objects and recompiling the entire package.
    • PL/SQL packages support specifying accessibility. Therefore, they maintain the security code.

    Advanced-Level PL/SQL Interview Questions

    35. Can you explain how procedures and functions differ from each other?

    The following table describes the differences between a procedure and a function:

    Procedure Function
    A procedure is pre-compiled and saved with a unique name. When you call any procedure with its name, it gets executed immediately since it is pre-compiled. A function is compiled every time it is called for execution.
    It does not return any value. It returns a value.
    The primary of a procedure is to execute complex business logic. The primary purpose of a function is computation.
    We cannot call a procedure inside the SELECT statement. We can embed a function in the SELECT statement.
    It supports exception handling using the try-catch block. It does not support exception handling.
    A procedure can call any function. A function cannot call any procedure.

    36. Differentiate between a procedure and a trigger.

    The following table highlights the differences between a procedure and a trigger:

    Procedure Trigger
    A stored procedure in PL/SQL is a block of PL/SQL code to perform a certain task. A trigger is a stored procedure that executes automatically when a particular event occurs.
    It can take input as a parameter. It cannot take input as a parameter.
    A procedure cannot be inactive. You can activate or deactivate triggers as per your needs.
    Creation: CREATE PROCEDURE Creation: CREATE TRIGGER

    37. Enlist various constraints that PL/SQL supports.

    Constraints are rules that limit or restrict the type of data that goes into a table. PL/SQL supports the following constraints:

    • NOT NULL
    • CHECK
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY

    38. How will you use the DECLARE statement in PL/SQL?

    We use the DECLARE statement in PL/SQL at the beginning of any stand-alone files containing an anonymous block of code that is not a stored procedure. For example:

    DECLARE
    n1 NUMBER(5);
    n2 NUMBER(8);
    BEGIN
    -- logic
    END;
    
    

    39. What do you know about SQLCODE and SQLERRM?

    For exceptions in any PL/SQL code that we cannot handle explicitly, we use SQLCODE and SQLERRM. Both are globally defined variables. SQLCODE returns the error code, while SQLERRM returns the error message.

    40. Is it possible in PL/SQL to accept the input from the user during runtime? If yes, how?

    Yes, it is possible to accept the input from the user during runtime. We can do it using the ACCEPT keyword.

    41. How will you run a query faster?

    There is a keyword called ROWID, using which we can run a query faster. Basically, it is a logical address of a row, which contains the block number, file number, and row number. It significantly reduces the I/O time and runs a query faster.

    42. How will you trace the PL/SQL code?

    We can trace the PL/SQL code using the following DBMS_* methods:

    • DBMS_APPLICATION_INFO
    • DBMS_TRACE
    • DBMS_SESSION and DBMS_MONITOR

    43. Which method will you use to restrict the string length in PL/SQL?

    We need to use CHAR(NUMBER) to restrict the string length in PL/SQL. For example: CHAR(10), restricts the string length to 10. If the string length is less than the specified number, it pads the string with whitepaces.

    44. Do you about NVL()?

    NVL() in PL/SQL lets you replace the NULL value with a more meaningful alternative.

    Syntax:

    NVL(e1, e2)

    In the above expression, NVL() accepts two parameters: e1 and e2. If e1 evaluates to NULL, it returns the value of e2. But if e1 is not null, NVL() returns the value of e1 only.

    45. What is the maximum number of triggers that you can apply on a table?

    The maximum number of triggers that we can apply on a table is 12.

    46. What do you know the error ORA-03113?

    ORA-03113 is an 'end-of-file on communication channel' error. This error occurs when the connection between the client and the server breaks. In other words, the client process connected to an Oracle database reports this error. Moreover, this error represents "I cannot communicate with the Oracle shadow process".

    47. Can you state the use of the '||' operator?

    We use the '||' operator to concatenate multiple strings.

    48. Write a query to display the records that have the maximum salary from the Employee table.

    Select * from emp where sal= (select max(sal) from emp)

    49. Write the syntax for creating a function in PL/SQL.

    The following is the syntax for creating a function in PL/SQL:

    CREATE function_name
    RETURN return_datatype
    {IS | AS}
    DECLARE
    VARIABLE DATATYPE;
    BEGIN
    function_body
    END function_name;

    50. How will you disable a trigger in PL/SQL?

    The following is the syntax to disable a trigger in PL/SQL:

    ALTER TRIGGER TRIGGER_NAME DISABLE;

    Conclusion

    Procedural Language extensions to the Structured Query Language is one of the most potent and vigorous query languages used globally. It offers various benefits, such as high-level security, object-oriented programming support, access to predefined SQL packages, and support for building PL/SQL Server Pages and web applications.

    Additionally, PL/SQL offers a broad spectrum of career opportunities. Though PL/SQL does not develop or enhance itself and is obsolete, there is no better option for Oracle than this. It is the core language used with the Oracle database. Therefore, it would be highly beneficial to pursue a career in PL/SQL development.

    This post consists of PL/SQL interview questions and answers that are popularly asked in interviews. If you are a fresher and appearing for a PL/SQL interview, getting familiar with these questions and answers will help you ace your interview.

    Prepare some more real-world questions and practical answers to ace the PL/SQL developer interview by purchasing the course here.

    People are also reading:

    FAQs


    The major advantage of PL/SQL over SQL is that PL/SQL executes the statements in bulk which improves the processing speed.

    PL/SQL is a simple database programming language used for managing and manipulating data stored in Oracle databases.

    Though PL/SQL is not used much these days, it is not obsolete. It is still used in Oracle databases.

    Popular job roles that need knowledge of PL/SQL are data analyst, SQL developer, ETL developer, PL/SQL administrator, and PL/SQL performance optimization developer.

    To become a PL/SQL developer, you need to have some experience working with Oracle databases, an in-depth understanding of SDLC, procedures, C programming language, query writing and execution, and triggers, knowledge of designing and implementing ETL processes, and creating, designing, and implementing data models.

    Leave a Comment on this Post

    0 Comments