By Vinish Kapoor

Oracle PL/SQL Tutorials

Every Oracle PL/SQL tutorial on vinish.dev: blocks and cursors, procedures and packages, exceptions, files and BLOBs, email and HTTP, and practice programs.

  • 97 tutorials
  • 8 topics
  • 3 learning paths
  • Updated October 2026

Browse by topic

Learning paths

Ordered series you can follow from the first lesson to the last.

Beginner

PL/SQL Essentials

Your first block to procedures, packages, triggers, and debugging.

Start with: Hello World Program in Oracle Database 23ai PL/SQL

All 15 lessons
  1. Hello World Program in Oracle Database 23ai PL/SQL
  2. Understanding Variables and Constants in Oracle Database 23ai PL/SQL
  3. How to Use IF Statements in Oracle Database 23ai PL/SQL
  4. How to Use CASE Statement in Oracle Database 23ai PL/SQL
  5. How to Loop in Oracle Database 23ai PL/SQL
  6. How to Use Cursors in Oracle Database 23ai PL/SQL
  7. How to Use Explicit Cursors in PL/SQL (Oracle Database 23ai)
  8. How to Handle Exceptions in Oracle Database 23ai PL/SQL
  9. How to Create Procedures in Oracle Database 23ai PL/SQL
  10. How to Create Functions in Oracle Database 23ai PL/SQL
  11. How to Create Packages in Oracle Database 23ai PL/SQL
  12. How to Use Triggers in Oracle Database 23ai PL/SQL
  13. Using DBMS_OUTPUT to Debug PL/SQL Code
  14. Error Handling with Dbms_utility.format_error_backtrace
  15. Using PL/SQL UTL_FILE to Read and Write Files
Intermediate

Working with Files & BLOBs

Read and write files with UTL_FILE, move data in and out as CSV, Excel and JSON, and handle BLOBs.

Start with: Using PL/SQL UTL_FILE to Read and Write Files

All 21 lessons
  1. Using PL/SQL UTL_FILE to Read and Write Files
  2. utl_file.fopen Parameters in Oracle
  3. How to Check If File Exists in PL/SQL?
  4. Oracle PL/SQL: UTL_FILE.FCOPY Example
  5. How to Move File from One Directory to Another Using PL/SQL
  6. UTL_FILE.FREMOVE Example: Delete a File in Oracle
  7. Split Large Text/CSV File into Multiple Files in PL/SQL
  8. Import CSV File in Oracle Table Using Stored Procedure
  9. Oracle PL/SQL: Export Data from a Table to CSV
  10. Export Data into Excel from Oracle Table Using PL/SQL
  11. Export Data into JSON File in Oracle 11g Using PL/SQL
  12. How to Insert a File in Oracle Database?
  13. How to Get BLOB from File in PL/SQL?
  14. How to Save BLOB as File in PL/SQL?
  15. How to Get File From BLOB in Oracle?
  16. Get BLOB from the BFILE Column in Oracle
  17. Oracle PL/SQL: Check If BLOB or CLOB is Empty
  18. How to Check if File is Image Using PL/SQL?
  19. How to ZIP a File in PL/SQL?
  20. How to UNZIP a File in PL/SQL?
  21. Merge/Combine Multiple PDF Files into One PDF in Oracle Using PLPDF_TOOLKIT PL/SQL Package
Beginner

PL/SQL Practice Programs

Warm up with classic programs, from adding two numbers to Armstrong and Fibonacci.

Start with: PL/SQL Program To Add Two Numbers

All 14 lessons
  1. PL/SQL Program To Add Two Numbers
  2. PL/SQL Program to Swap two Numbers
  3. PL/SQL Program to Check Number is Odd or Even
  4. PL/SQL Program to Find the Greatest of Three Numbers
  5. PL/SQL Program for Reverse of a Number
  6. PL/SQL Program for Palindrome Number
  7. PL/SQL Program for Armstrong Number
  8. PL/SQL Program for Prime Number
  9. PL/SQL Program for Fibonacci Series
  10. PL/SQL Program to Find Factorial of a Number
  11. PL/SQL Program to Print Table of a Number
  12. PL/SQL Program to Reverse a String
  13. PL/SQL Program to Print Patterns
  14. How to Print 1 to 10 Without Using Loop in PL/SQL?

Latest tutorials

Books & free tools

Go deeper with the books, or try the free tools.

All tutorials by topic

PL/SQL Basics 14

Blocks, variables, IF, CASE, loops, cursors, and collections.

  • Hello World Program in Oracle Database 23ai PL/SQL

    Every programming journey starts with a "Hello World" program. It is the traditional way to test the environment, confirm that code compiles, and understand the basic…

  • How to Execute PL/SQL Block in Oracle

    If you are working with Oracle, you will often need to execute PL/SQL blocks to test logic, fetch data, or print results. Running a PL/SQL block is easy, and you can do…

  • Understanding Variables and Constants in Oracle Database 23ai PL/SQL

    When you write PL/SQL programs in Oracle Database 23ai, you often need storage for values that change during program execution and values that should never change.…

  • How to Use IF Statements in Oracle Database 23ai PL/SQL

    Conditional logic is one of the most essential elements of programming. In Oracle Database 23ai, PL/SQL supports multiple forms of IF statements that help developers…

  • Oracle IF Condition Example

    In Oracle PL/SQL, IF condition is used to perform a logical check on certain values. If the condition is TRUE or FALSE then executes the statements followed by that…

  • How to Use CASE Statement in Oracle Database 23ai PL/SQL

    The CASE statement in Oracle PL/SQL is a powerful alternative to IF statements when you want to evaluate multiple conditions more clearly and concisely. In Oracle…

  • How to Loop in Oracle Database 23ai PL/SQL

    Loops are one of the fundamental concepts in any programming language, allowing you to repeat a block of code multiple times until a condition is met. In Oracle Database…

  • Oracle FOR LOOP REVERSE Example

    In Oracle PL/SQL, FOR LOOP with REVERSE clause is used to repeat loop iteration in reverse order. The following are the syntax and examples for REVERSE FOR LOOP. Syntax…

  • Oracle WHILE LOOP Example

    In Oracle PL/SQL, WHILE LOOP statement executes the code written between WHILE LOOP and END LOOP until the condition is true. Below I am giving some examples for Oracle…

  • Oracle FOR LOOP SELECT Statement Example

    In this tutorial, I am giving an Oracle FOR LOOP SELECT statement example. You can use SELECT statement inside FOR LOOP in Oracle to loop through SELECT results using…

  • How to Use Cursors in Oracle Database 23ai PL/SQL

    In Oracle Database 23ai, PL/SQL is not limited to single-row queries. Many times you need to process multiple rows one by one—for example, when iterating through…

  • How to Use Explicit Cursors in PL/SQL (Oracle Database 23ai)

    Cursors in PL/SQL are used to handle query results that return more than one row. By default, Oracle manages cursors internally when you use simple SELECT INTO…

  • How to Fetch Data from Cursor in Oracle Using For Loop

    In Oracle PL/SQL, the For Loop offers one of the simplest ways to work with cursors. The key benefit when using a cursor in this format is that Oracle automatically…

  • Table Type in Oracle PL/SQL Example

    PL/SQL table types—also known as collections—function similarly to arrays in other programming languages. Using PL/SQL collections, you can process large volumes of data…

Procedures, Functions & Packages 17

Stored procedures, functions, packages, pipelined functions, and result sets.

Triggers, Dynamic SQL & Bulk Processing 6

Triggers, EXECUTE IMMEDIATE, DBMS_SQL, and BULK COLLECT.

Exceptions & Debugging 8

Raising and handling exceptions, ORA- and PLS- errors, DBMS_OUTPUT, and logging.

Files, BLOBs & Import/Export 21

UTL_FILE, BLOBs and CLOBs, ZIP files, PDFs, and CSV, Excel and JSON export.

  • Using PL/SQL UTL_FILE to Read and Write Files

    Working with files directly from the database can be an essential part of enterprise applications. Whether you need to generate reports, export data, or import…

  • utl_file.fopen Parameters in Oracle

    The utl_file.fopen function in Oracle is an essential tool for file handling on the server. It allows Oracle database code, such as PL/SQL procedures or functions, to…

  • How to Check If File Exists in PL/SQL?

    The following is a stored function example to check if a file exists in PL/SQL. PL/SQL Function Example To Check If a File Exists The below function takes two…

  • Oracle PL/SQL: UTL_FILE.FCOPY Example

    In Oracle PL/SQL, UTL_FILE.FCOPY procedure is used to copy a file. This article explains how to copy a file in PL/SQL using UTL_FILE.FCOPY procedure with syntax and…

  • How to Move File from One Directory to Another Using PL/SQL

    Sometimes, you may need to move a file from one directory to another within your Oracle database environment. Oracle provides a convenient way to accomplish this task…

  • UTL_FILE.FREMOVE Example: Delete a File in Oracle

    In Oracle PL/SQL, UTL_FILE.FREMOVE procedure is used to delete a file from the disk. This tutorial explains how to delete a file in Oracle PL/SQL using UTL_FILE.FREMOVE…

  • Split Large Text/CSV File into Multiple Files in PL/SQL

    Sometimes, you may encounter a situation where you have a very large text or CSV file that is cumbersome to open or process all at once. In such cases, it is often…

  • Import CSV File in Oracle Table Using Stored Procedure

    A CSV file (Comma-Separated Values file) is a plain text file in which individual fields are separated by commas. This format is commonly used for exchanging data…

  • Oracle PL/SQL: Export Data from a Table to CSV

    Learn how to use the UTL_FILE package to export data from a table to the CSV file in Oracle PL/SQL. In Oracle, the package UTL_FILE contains many procedures and…

  • Export Data into Excel from Oracle Table Using PL/SQL

    Exporting data from an Oracle table directly into an Excel file can be accomplished easily using PL/SQL and the xlsx_builder_pkg package. This utility allows you to…

  • Export Data into JSON File in Oracle 11g Using PL/SQL

    In this article, I am giving an example to export data into JSON file in Oracle 11g using PL/SQL. Follow These Steps to Export Data into JSON File in Oracle 11g Now you…

  • How to Insert a File in Oracle Database?

    In this tutorial, you will learn how to insert a file in the Oracle database. The type of file can be a PDF, an Image, or any document. I am using the BLOB data type in…

  • How to Get BLOB from File in PL/SQL?

    Below I am giving a function example to get the BLOB from a file in PL/SQL. PL/SQL Function to Get BLOB from a File The following function will read a file and will…

  • How to Save BLOB as File in PL/SQL?

    Here I am giving an example to save BLOB data as a file in PL/SQL. BLOB data you can get it from the table having BLOB column, or you can get it from a file on the disk.…

  • How to Get File From BLOB in Oracle?

    In this tutorial, you will learn how to get a file from BLOB column in Oracle. To give an example, I have created a table EXT_FILES in Oracle and following is the…

  • Get BLOB from the BFILE Column in Oracle

    In Oracle, the BFILE column is a locator or reference for the external file. It has the directory and filename information. Here I am giving an example of PL/SQL code to…

  • Oracle PL/SQL: Check If BLOB or CLOB is Empty

    In Oracle PL/SQL, to check if BLOB or CLOB is empty or not, use the dbms_lob.getlength() function or dbms_lob.compare() function. Here are the examples: 1. Using…

  • How to Check if File is Image Using PL/SQL?

    In this blog post, I am sharing a function with which you can check if a file is an image file or not using PL/SQL. The program will check the file by reading its header…

  • How to ZIP a File in PL/SQL?

    In this blog post, I am giving an example to ZIP a file in PL/SQL. I am using Alexandria PL/SQL Util Library to perform this task. The same library I used for the…

  • How to UNZIP a File in PL/SQL?

    In my previous post, I have given an example to ZIP a file using PL/SQL in Oracle, and in this post, I am explaining to how to extract a ZIP file, or unzip a file in…

  • Merge/Combine Multiple PDF Files into One PDF in Oracle Using PLPDF_TOOLKIT PL/SQL Package

    In this tutorial, I am giving an example to merge/combine multiple PDF files into one PDF file in Oracle using PLPDF_TOOLKIT PL/SQL package. Assume you have a table for…

Email, HTTP & Integration 8

Send and read email, call web services, 2FA, Java, Python, and AI agents.

Strings, Dates & Utilities 7

Split strings, dates and time zones, number formatting, validation, and encryption.

  • How to Convert Number to String in Oracle?

    In Oracle, you can convert a number to string using the TO_CHAR() function; however, you can simply assign a numeric value to a string variable without using any…

  • Oracle: Convert Currency Amount in Words Using PL/SQL

    Below I am giving an example of Oracle stored function to convert currency amount in words using PL/SQL. For example, the amount of $123.45 would translate as One…

  • How to Encrypt Password in Oracle?

    Here I am giving an example to encrypt the password in Oracle using the DBMS_CRYPTO package. To demonstrate this, I have created the following table to store user ID…

  • How to Validate Credit Card Number and Identify Its Type Using PL/SQL?

    Here I am giving an example of PL/SQL stored function to validate the Credit Card number and identify its type, whether it is a Visa, MasterCard or American Express…

  • How to Get Current Date in PL/SQL?

    The following are the examples to get the current date in PL/SQL. Examples To Get The Current Date in PL/SQL 1. Using Sysdate Function SET SERVEROUTPUT ON; DECLARE…

  • How to Split a String in PL/SQL?

    In this article, I am giving an example of a function which I mostly use to split a string in PL/SQL. Especially, when I am writing a program to import a delimited file.…

  • Get Current Local Time of Any Country in PL/SQL

    When we want to know the current local time in any country, we often use Google to search for this information. However, in Oracle PL/SQL, you can retrieve the current…

PL/SQL Practice Programs 16

Classic PL/SQL programs for practice and interviews: primes, Fibonacci, palindromes, and more.

  • PL/SQL Program To Add Two Numbers

    This is one of the most basic and common programs for beginners to write. It teaches you the fundamental structure of a PL/SQL program, including how to declare…

  • PL/SQL Program to Swap two Numbers

    Swapping the values of two variables (making a become b and b become a) is one of the most fundamental logic puzzles in programming. In PL/SQL, this is easily…

  • PL/SQL Program to Check Number is Odd or Even

    Determining if a number is odd or even is a fundamental programming exercise. In PL/SQL, this is very easy to do and is the perfect way to learn about conditional logic…

  • PL/SQL Program to Find the Greatest of Three Numbers

    A common task for new programmers is to compare several values to find the largest or smallest. In Oracle PL/SQL, you can easily find the greatest of three numbers using…

  • PL/SQL Program for Reverse of a Number

    Writing a program to reverse a number (for example, turning 12345 into 54321) is a classic programming challenge. It's a great way to learn how to use loops and basic…

  • PL/SQL Program for Palindrome Number

    A "palindrome" is a number (or word) that reads the same backward as it does forward. For example, 121, 535, and 7 are all palindrome numbers. Writing a program to check…

  • PL/SQL Program for Armstrong Number

    Writing a program to check if a number is an "Armstrong Number" (or a "narcissistic number") is a classic programming challenge. It's a great exercise to learn how to…

  • PL/SQL Program for Prime Number

    Writing a program to determine if a number is prime is a classic programming challenge. It's a great exercise to learn about loops, conditional logic (IF statements)…

  • PL/SQL Program for Fibonacci Series

    The Fibonacci series is a famous mathematical sequence where each new number is the sum of the two preceding ones. Writing a program to generate this sequence is a…

  • PL/SQL Program to Find Factorial of a Number

    Calculating a factorial is a common programming exercise that demonstrates how to use loops for repeated multiplication. In PL/SQL, you can easily write a program to…

  • PL/SQL Program to Print Table of a Number

    A common exercise for learning any new programming language is to print a multiplication table (e.g., the "5 times table"). This is a perfect task for learning how to…

  • PL/SQL Program to Reverse a String

    Writing a program to reverse a string (for example, turning "hello" into "olleh") is a classic programming challenge. It's a great way to learn how to use loops and…

  • PL/SQL Program to Print Patterns

    Printing patterns like triangles and pyramids is a classic programming exercise for learning how to use loops. In Oracle, you can do this using PL/SQL (Procedural…

  • How to Print 1 to 10 Without Using Loop in PL/SQL?

    Here I am giving an example to print 1 to 10 without using the loop in PL/SQL. You can print up to any number by changing the value 10 to any number, for example, 100.…

  • PL/SQL Program to Delete The Records From Table

    In this tutorial, you will learn how to delete the records from a table in Oracle using PL/SQL. In Oracle PL/SQL, we can delete the records using DELETE DML Statement…

  • PL/SQL Program to Print Employee Details

    In this tutorial, you will learn how to print employee details using PL/SQL program. PL/SQL Program to Print Employee Details In the following example, it will print the…