PL/SQL (Procedural Language for SQL) is Oracle's proprietary extension to standard SQL. It combines the data manipulation power of SQL with the procedural capabilities of a programming language. PL/SQL allows developers to write complex database-centric applications by incorporating variables, conditions, loops, error handling, and modular programming constructs like procedures and functions. This language is tightly integrated with Oracle databases, enabling efficient data processing and transaction management.
In Oracle, For Loop is the easiest way to work with Cursor. It will open the cursor, fetch and closes by itself. Below are the examples to how to fetch data from Cursor in Oracle using For Loop. For Loop Cursor…
In Oracle utl_file.fopen is a function, which opens a file on the server by returning its file handle. Function utl_file.fopen parameters in Oracle are as follows: utl_file.fopen Syntax UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize…
Below are the few quick examples to execute PL SQL block in Oracle. 1. Execute PL SQL Block in SQL Plus In SQL Plus, just put the forward slash (/) at the end of PL SQL block and press enter to…
An example is given here to raise application error (raise_application_error) in PL/SQL stored procedure if the certain condition does not match. The program will raise an exception if the commission (comm) is null in EMP table for given employee number…
In Oracle, you can execute a function with parameters via the following ways: 1. Execute The Function Using Select Statement SELECT get_emp_job (7566) FROM DUAL; Output GET_EMP_JOB(7566) ---------------------------------------- MANAGER 1 row selected. 2. Execute The Function Using PL/SQL Block SET…
Below is an example of a stored procedure in Oracle with IN OUT parameter. It will take employee number (i_empno) as IN parameter and will return employee name (o_ename) as OUT parameter. Stored Procedure IN-OUT Parameter Example CREATE OR REPLACE…
Executing a procedure inside a package in Oracle is just by giving package reference before the procedure. For example, your procedure name is Extract_Emp_Data in the package EMP_INTERFACE then execute it as follows: Execute a Procedure Inside a Package in…
In Oracle, you first need to declare the procedure in a package specification and then create the full procedure in the package body. Because if you will try to create procedure only in the package body without its declaration in…
CSV file is a comma delimited file in which fields are separated by the comma. In this article, I am giving an example to import CSV file into oracle table using stored procedure. The stored procedure in Oracle is a…
An example is given below to export data into Excel from Oracle table using PL SQL. You can do this task with the help of xlsx_builder_pkg package, which you can download from GITHUB using the following link click here. After…
PL SQL table types are actually collections, like an array. With the help of PL SQL collections, you can process bulk data efficiently. In this post, I will teach you very basic and most frequently used commands for PL SQL…
I am giving an example below to move a file from one directory to another using PL SQL in Oracle. To move a file from one location to another in Oracle, the database directory object for source directory and target directory must…
Sometimes it happens that you have a very large text or CSV file to process, but first you want to make smaller files of that large file. Because that large file may take too much time to process or open.…
Usually we used to do Google to check current local time of any country. In this post I am giving an example, how you can get current local time of any country in PL You can get the list of…