PL/SQL is an important skill for anyone working with Oracle databases. It allows developers to write complex queries and procedures using SQL and procedural logic together. If you’re preparing for a job that involves Oracle development or database administration, knowing PL/SQL is often a must. In interviews, you may be asked about stored procedures, triggers, exception handling, and performance tuning.
This page offers a list of commonly asked PL/SQL interview questions with simple and clear answers. Whether you’re a beginner or an experienced professional, these questions will help you build confidence and improve your preparation. PL/SQL is widely used in industries like finance, healthcare, and government, making it a valuable skill to master.
So, take the time to review these questions, understand how PL/SQL works, and get ready to showcase your knowledge in your next interview.
Answer: Intersect is a product of two tables that lists only matching rows.
Answer:
- SYSDATE: It returns the current time & date on the local database server. The syntax is SYSDATE. If we have to extract a part of the date, then we use the TO_CHAR function.
- USER: The USER returns the user_id of the current session.
Answer: ROWID is a row’s logical address that is not a physical column. It consists of file row number, block number, & file number in the data block. Thus, it minimizes the I/O time by retrieving the row, which results in a faster query.
Answer: When closing the cursor, you disable it so that the cursor no longer has an active set associated with it. You cannot fetch records & destroys the cursor result set.
Answer: A control file represents a small binary file that records a database’s physical structure, including:
- Database name
- Locations of the associated data files & online redo log files
- Timestamp of the database creation
Answer: Consistency implies that data will not be reflected to other users till the data is commit, which helps to maintain consistency.
Answer: Here are the differences between the Anonymous blocks & sub-programs:
-
- [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]
Anonymous Blocks Sub-programs Anonymous blocks are unnamed programming blocks to obtain functionalities. Sub-programs are named programming subprograms that can be reused. One needs to compile anonymous blocks again and again. Sub-programs are only compiled once. Anonymous blocks are not stored in databases. Sub-programs are stored in the database. You cannot invoke anonymous blocks in other applications. You can invoke sub-programs in other applications. The anonymous block never returns a value. Functions always return a value. Anonymous blocks cannot take parameters. Procedures & functions can take input & output parameters.
- [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=””]
Answer: Autonomous transactions enable you to leave the calling transaction context. It performs an independent transaction & returns to the calling transaction without affecting its state. The autonomous transaction does not have any link to the calling transaction, so only committed data can be shared by both transactions. You can define the following types of PL/SQL blocks as an autonomous transaction:
- Stored procedures & functions
- Local procedures & functions defined in a PL/SQL declaration block
- Packaged procedures & functions
- Type methods
- Top-level anonymous blocks
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]
| SGA | PGA |
|---|---|
| It stands for System Global Area | It stands for Program Global Area |
| SGA contains data & control information for an Oracle database instance. | PGA contains data & control information exclusively for the single Oracle process. |
| It has a shared memory region for all the components. | It has a non-shared memory region. |
| Some examples of SGA are SQL areas & cached data blocks. | Some examples of PGA are SQL work area & session memory. |
Answer: Predefined functions are organized into two separate libraries:
- I/O functions are stored in the iostream header
- Math functions are stored in the cmath header
Answer: Polymorphism is a feature of (OOP) Object-Oriented Programming. It means the ability to create a function, variable, or object with several forms. In Oracle, procedural programming also supports polymorphism in the form of a program unit overloading inside the member function type, package, etc.
Question 32 : Is it possible to execute two queries simultaneously in a Distributed Database System?
Answer: Yes, one can execute two queries simultaneously as one query is always independent of the second query in the distributed database system based on the two-phase commit.
Answer: Raise_application_error is a procedure of the package DBMS_STANDARD that enables issuing user_defined error messages from a stored subprogram or database trigger.
Answer: A PL/SQL package includes the following:
- Procedures & Functions
- Cursors
- PL/SQL table & record TYPE statements
- Variables (scalars, tables, records) & constants
- Exception names & pragmas to relate an error number with an exception
Answer: PL/SQL packages provide several benefits as follows:
- Enforced Information Hiding: It provides the liberty to choose whether to keep data public or private.
- Top-down design: You can design an interface to the code hidden in a package before actually implementing the modules themselves.
- Object persistence: Objects declared in the package specification acts like global data for all PL/SQL objects in an application. You can modify a package in the module then reference the changes to another module.
- Object-oriented design: The package provides developers a stronghold on how to use the data structures & modules inside a package.
- Guaranteeing transaction integrity: It gives a level of transaction integrity.
- Performance improvement: The RDBMS automatically tracks program objects’ validity stored in a database & enhances the package’s performance.
Answer: Tracing code is a crucial technique to test the code performance during runtime. There are different methods in PL/SQL to trace the code, as follows:
- DBMS_ TRACE
- tkprof utilities and trcsess
- DBMS_ APPLICATION_INFO
- DBMS_SESSION and DBMS_MONITOR
Answer:
- PLVprs– It is the lowest level of string-parsing functionality that prove to be useful in different situations. It is the generic string-parsing extensions to PL/SQL.
- PLVprsps– It is the highest-level package to parse PL/SQL source code.
Answer: PL/SQL provides a robust & effective plugin “PLVexc” for exception handling. The plugin supports four different types of exception handling actions such as:
- Halt processing
- Continue processing
- Record & then continue
- Record & then halt processing
Answer: The PL/SQL table is closest to the arrays in PL/SQL. To access PL/SQL table, you need to declare the table type first; then, you need to declare the PL/SQL table itself. However, PLVtab eliminates the need to define your PL/SQL table type & provides easy access to PL/SQL data table.
Answer: Below are the key characteristics of PL/SQL:
- PL/SQL enables access & sharing of the same subprograms by multiple applications.
- PL/SQL is known for its code portability, as codes can be executed on any Oracle-based operating system.
- Users can write customized error handling routines with PL/SQL.
- PL/SQL improves transaction performance with the integration to the Oracle data dictionary.