PL/SQL (Procedural Language/SQL) is a key programming language used in Oracle databases to manage data and perform complex operations. It’s used by developers and DBAs to write stored procedures, create triggers, and build custom business logic. If you have an upcoming interview for a role that involves database programming or Oracle systems, knowing PL/SQL is a big plus. Employers want to see if you understand how to write efficient code, handle exceptions, and maintain data integrity.
This page brings you a collection of important PL/SQL interview questions and answers to help you get ready. You’ll find practical questions that test your knowledge and help you prepare for real technical interviews.
Whether you’re just starting out or already have experience, this guide will strengthen your understanding and give you the confidence to answer clearly and effectively. Start preparing today to increase your chances of landing your next tech job.
Answer: Schema objects are logical data storage structures that don’t have a one-to-one correspondence to physical files on the disk that stores their data or information. However, a database logically stores a schema object within a database’s tablespace. The data of each object contains one or more tablespace data files. Following are some schema objects created using PL/SQL:
- Constraints
- Views
- Database links
- Database triggers
- Dimensions
- Sequences
- External procedure libraries
- Indexes & index types
- Tables & index-organized tables
- Java resources, Java classes, & Java sources
- Materialized views & materialized view logs
- Object tables, object views, & object types
- Operators
- Synonym
- Clusters
- Tables & index-organized tables
- Stored functions, procedures, & packages
Answer: A transaction ends completely after the ROLLBACK statement or command completely undo/change the transaction & release all the locks. On the other hand, a transaction is active & keeps running after the ROLLBACK TO command as it undoes or changes only a part of the transaction up to the given SAVEPOINT.
Answer: Constraints help to prevent & limit invalid data entry or deletion in case of dependencies. Constraints enforce rules at the table level so they can either be created at the same time as the table is created or after the table is created. Constraint defined for a specific table is viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define a constraint at the table level except the NOT NULL defined only at the column level. There are five types of constraints:
- Unique Key Constraint
- Primary Key Constraint
- Foreign Key Constraint
- Not Null Constraint
- Check Key Constraint
Answer: [fusion_table fusion_table_type=”1″ fusion_table_rows=”” fusion_table_columns=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]
[/fusion_table]
| TRIGGERS | CONSTRAINT |
|---|---|
| Triggers are stored as separate objectsTriggers are stored as separate objects. | The constraints on a table are stored with the table definition. |
| Triggers are fired after constraints upon an event. | Constraints are fired once the table is deployed. |
| Triggers perform table to table faster comparison. | Constraints performmemory location to table comparison, which is slow; leading to low performance. |
| Triggersare for the entire table. | Constraints are for one column of a table. |
| Triggers arestored procedures that get executed automatically; thus,they don’t check for data integrity. | Constraints prevent invalid & duplicate data entries. |
Answer: PL/SQL records refer to a group of multiple pieces of data or information, each of which is simpler & could be related to one another as fields. PL/SQL supports three types of records:
- Table based records
- Programmer based records
- Cursor based records
Answer: IN OUT parameter passes the initial value to a subprogram & then returns an updated value to the caller. It can be assigned a value that can be read.The actual parameter corresponding to the IN OUT formal parameter should be a variable, not an expression or constant.
Answer: An error end-of-file on a communication channel ORA-03113 implies a broken connection between the server channels & the client. It could be a timeout due to which the connection gets lost. You can troubleshoot the error by checking the connectivity &pinging the server.
Answer: SYS.ALL_DEPENDENCIES represents all the dependencies between procedures, packages, triggers, functions accessible to a user. It shows columns like type, dependency_type, name, referenced_owner, etc.
Answer: Triggers enables you to perform these tasks:
- Triggers enables you to perform these tasks:
- Validate input data
- Enforce business rules
- Generate a unique value for the newly inserted row on a different file
- Write to other files for audit trail
- Query from another file for cross-referencing
- Access system functions
- Replicate data to separate files forobtaining data consistency
Answer: In PL/SQL, statements are put together into units called blocks. PL/SQL blocks can include variables, constants, loops,exception handling, SQL statements, conditional statements, etc. Blocks in PL/SQL can also build a function, procedure, or package. PL/SQL blocks are classified into two types:
- Anonymous blocks: These PL/SQL blocks are without the header or labels & they don’t form the body of a function, procedure, or triggers.
- Named blocks: PL/SQL blocks withlabels or header are called Named blocks. These blocks can either be subprograms (functions, procedures, packages) or Triggers.
Answer: A table currently being modified by the DML statement or defining triggers in the table is called a Mutating table. A table that may need to be read from for the referential integrity constraint is called a Constraining table.
Answer: [fusion_table fusion_table_type=”1″ fusion_table_rows=”” fusion_table_columns=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]
[/fusion_table]
| Actual Parameters | Formal Parameters |
|---|---|
| When a function is called/invoked, the values passed in the function call are called the actual parameters or arguments. | The parameter used in a function definition statement containing data type on its time of declaration is called a formal parameter. |
| Actual parameters are the expressions or variables referenced in a parameter list of a subprogram call. | Formal parameters are the expressions or variables referenced in a subprogram specification’s parameter list. |
| Actual Parameters are in the calling subprogram. | Formal Parameters are in the called subprogram. |
| There is no need to specify the datatype in the actual parameters. | There is a need to define the datatype of receiving value in the formal parameters. |
| The parameters written in the function call are called actual parameters. | The parameters written in the function definition are called formal parameters. |
| Actual Parameters can be variable names or constant values. | Formal Parameters can be a function’s local variables in which they are used in a function header. |
Answer: The three modes of a parameter are IN, OUT, IN OUT. Let’s understand each of these parameters individually:
- IN parameters: These parametersenable you to pass values to a procedure called & initialized to default values. IN parameters acts like a constant &cannot be assigned a value.
- OUT parameters: It returns a valueto a caller& must be specified. OUT parameters act like an uninitialized variable&cannot be used in an expression.
- IN OUT parameters: IN OUT parameters passes initial values to a procedure & return updated values to the caller. It acts like an initialized variable & should be assigneda value.
Answer: An implicit cursor always has a false %ISOPEN attribute because the implicit cursor is opened for the DML statement &is closed immediately after the DML statement execution.
Answer: The cursor declared in the procedures is treated as local; therefore, it cannot be accessed by the other procedures. The cursor declared in a package specification is treated as global; hence it can be accessed by the other procedures.
Answer: An INSTEAD OF trigger means a trigger that enables you to skip an UPDATE, DELETE, or INSERTstatement to a view or table &executes other statements defined in the trigger instead. Thus, the actual operation of delete, insert, or update does not occur at all.
Answer: Expressions are denoted by a sequence of variables & literals separated by operators. In PL/SQL, operations are used to compare, manipulate, & calculate some data. An expression is a composition of ‘Operands’ & ‘Operators.’
- Operands: These are an argument to the operators. Operands can be a function call, variable, substring, Array element, or constant.
- Operators: These define the actions to be performed.
Answer: A forward declaration refers to the declaration of an identifier (representing an entity like a type, constant, variable, or function) for which the programmer does not yet have a complete definition.
Answer: The WHERE CURRENT OF clause is mainly used in the UPDATE & DELETE statements. It states the most recent row fetched from a table should be deleted or updated. You should declare a cursor with the FOR UPDATE clause to use the feature. Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.
Answer:
- ROWID: ROWID is a pseudocolumn in the table that stores & returns row address in a HEXADECIMAL format with the database tables. It is the permanent unique identifier for every row in a database.
- ROWNUM: For every row returned by the query, the ROWNUM pseudocolumn returns a number specifying an order in which the Oracle selects the row or a set of joined rows from the table. For instance, the first row selected has a ROWNUM 1; similarly, the second one has 2, & so on.