PL-SQL

What is Nested Blocks Oracle? In PL/SQL, each block can be nested into another block. They are referred as a nested block. Nested blocks are very common when we want to perform the certain process, and at the same time, the code for these process should be kept in a separate container (block). Nested block…

What is Dynamic SQL? Dynamic SQL is a programming methodology for generating and running statements at run-time. It is mainly used to write the general-purpose and flexible programs where the SQL statements will be created and executed at run-time based on the requirement. Ways to write dynamic SQL PL/SQL provides two ways to write dynamic…

What is Object Type in PL/SQL? Object-Oriented Programming is especially suited for building reusable components and complex applications. They are organized around “objects” rather than “actions” i.e. the programs are designed to work and interact with the entire object rather than the single action. This concept allows the programmer to populate and manipulate the details…

What is Trigger in PL/SQL? TRIGGERS are stored programs that are fired by Oracle engine automatically when DML Statements like insert, update, delete are executed on the table or some events occur. The code to be excecuted in case of a trigger can be defined as per the requirement. You can choose the event upon…

What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram (procedure/function) into a single element. A Package is compiled and stored as a database object that can be used later. Components of Packages PL/SQL package has two components. Package Specification Package Body Package Specification Package specification consists of a declaration…

What are TCL Statements in PL/SQL? TCL stands for Transaction Control Statements. It will either save the pending transactions or roll back the pending transaction. These statements play the vital role because unless the transaction is saved the changes through DML statements will not be saved in the database. Below are the different TCL statements….

What is BULK COLLECT? BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once. Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching one-by-one. This BULK COLLECT can be used in ‘SELECT’ statement to populate the records in bulk or…

What is CURSOR in PL/SQL? A Cursor is a pointer to this context area. Oracle creates context area for processing an SQL statement which contains all information about the statement. PL/SQL allows the programmer to control the context area through the cursor. A cursor holds the rows returned by the SQL statement. The set of…

In this tutorial, we are going to learn how to use SQL in PL/SQL. SQL is the actual component that takes care of fetching and updating of data in the database whereas PL/SQL is the component that processes these data. Further, in this article, we will also discuss how to combine the SQL within the…