By Vinish Kapoor
Oracle PL/SQL Tutorials
Every Oracle PL/SQL tutorial on vinish.dev: the language, collections, packages and triggers, the built-in packages from DBMS_SCHEDULER to UTL_HTTP, and practice programs.
- 254 tutorials
- 16 topics
- 4 learning paths
- Updated October 2026
Browse by topic
- PL/SQL Basics18
- Collections, Records & Types8
- Procedures, Functions & Packages24
- Triggers8
- Dynamic SQL & Bulk Processing9
- Exceptions & Debugging11
- Fixing PL/SQL Errors (ORA-, PLS-)24
- Files, LOBs & Import/Export29
- Web, HTTP, Email & Integration22
- Jobs, Queues & Messaging16
- JSON, XML & Text Search15
- Security & Encryption6
- Performance, Tuning & Code Quality16
- Schema, Metadata & Session Utilities18
- Strings, Dates & Utilities14
- PL/SQL Practice Programs16
Learning paths
Ordered series you can follow from the first lesson to the last. Your progress is saved in this browser.
Beginner · 15 lessons
PL/SQL Essentials
Your first block to procedures, packages, triggers, and debugging.
·
- Hello World Program in Oracle Database 23ai PL/SQL
- Understanding Variables and Constants in Oracle Database 23ai PL/SQL
- How to Use IF Statements in Oracle Database 23ai PL/SQL
- How to Use CASE Statement in Oracle Database 23ai PL/SQL
- How to Loop in Oracle Database 23ai PL/SQL
- How to Use Cursors in Oracle Database 23ai PL/SQL
- How to Use Explicit Cursors in PL/SQL (Oracle Database 23ai)
- How to Handle Exceptions in Oracle Database 23ai PL/SQL
- How to Create Procedures in Oracle Database 23ai PL/SQL
- How to Create Functions in Oracle Database 23ai PL/SQL
- How to Create Packages in Oracle Database 23ai PL/SQL
- How to Use Triggers in Oracle Database 23ai PL/SQL
- Using DBMS_OUTPUT to Debug PL/SQL Code
- Error Handling with Dbms_utility.format_error_backtrace
- Using PL/SQL UTL_FILE to Read and Write Files
Tip: Ctrl-click a lesson to open it in a new tab. It still counts toward your progress.
Intermediate · 18 lessons
Beyond the Basics
Cursor parameters, collections, exception handling, package design, caching, FORALL, compound triggers, and compilation.
·
- How to Pass Parameters to Cursors in PL/SQL
- How to Use SQL% Cursor Attributes in PL/SQL
- How to Use Associative Arrays in PL/SQL
- How to Use Nested Tables in PL/SQL
- How to Use Varrays in PL/SQL
- How to Use Qualified Expressions in PL/SQL
- How to Use SQLCODE in PL/SQL Exception Handlers
- How to Name Oracle Errors with PRAGMA EXCEPTION_INIT
- How Exceptions Propagate in PL/SQL
- How to Overload Subprograms in PL/SQL Packages
- How to Use AUTHID in PL/SQL
- How to Use DETERMINISTIC Functions in PL/SQL
- How to Cache Function Results with RESULT_CACHE in PL/SQL
- How to Use FORALL for Bulk DML in PL/SQL
- How to Write Compound Triggers in Oracle
- How to Fix the Mutating Table Error in Oracle (ORA-04091)
- How to Use Conditional Compilation in PL/SQL
- How to Compile PL/SQL Natively and Wrap Source Code
Tip: Ctrl-click a lesson to open it in a new tab. It still counts toward your progress.
Intermediate · 21 lessons
Working with Files & BLOBs
Read and write files with UTL_FILE, move data in and out as CSV, Excel and JSON, and handle BLOBs.
·
- Using PL/SQL UTL_FILE to Read and Write Files
- utl_file.fopen Parameters in Oracle
- How to Check If File Exists in PL/SQL?
- Oracle PL/SQL: UTL_FILE.FCOPY Example
- How to Move File from One Directory to Another Using PL/SQL
- UTL_FILE.FREMOVE Example: Delete a File in Oracle
- Split Large Text/CSV File into Multiple Files in PL/SQL
- Import CSV File in Oracle Table Using Stored Procedure
- Oracle PL/SQL: Export Data from a Table to CSV
- Export Data into Excel from Oracle Table Using PL/SQL
- Export Data into JSON File in Oracle 11g Using PL/SQL
- How to Insert a File in Oracle Database?
- How to Get BLOB from File in PL/SQL?
- How to Save BLOB as File in PL/SQL?
- How to Get File From BLOB in Oracle?
- Get BLOB from the BFILE Column in Oracle
- Oracle PL/SQL: Check If BLOB or CLOB is Empty
- How to Check if File is Image Using PL/SQL?
- How to ZIP a File in PL/SQL?
- How to UNZIP a File in PL/SQL?
- Merge/Combine Multiple PDF Files into One PDF in Oracle Using PLPDF_TOOLKIT PL/SQL Package
Tip: Ctrl-click a lesson to open it in a new tab. It still counts toward your progress.
Beginner · 14 lessons
PL/SQL Practice Programs
Warm up with classic programs, from adding two numbers to Armstrong and Fibonacci.
·
- PL/SQL Program To Add Two Numbers
- PL/SQL Program to Swap two Numbers
- PL/SQL Program to Check Number is Odd or Even
- PL/SQL Program to Find the Greatest of Three Numbers
- PL/SQL Program for Reverse of a Number
- PL/SQL Program for Palindrome Number
- PL/SQL Program for Armstrong Number
- PL/SQL Program for Prime Number
- PL/SQL Program for Fibonacci Series
- PL/SQL Program to Find Factorial of a Number
- PL/SQL Program to Print Table of a Number
- PL/SQL Program to Reverse a String
- PL/SQL Program to Print Patterns
- How to Print 1 to 10 Without Using Loop in PL/SQL?
Tip: Ctrl-click a lesson to open it in a new tab. It still counts toward your progress.
Latest tutorials
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Handle ORA-01476: Divisor Is Equal to Zero in PL/SQL Loops
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-04098: Trigger Is Invalid and Failed Re-Validation in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-06519: Active Autonomous Transaction Detected and Rolled Back in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-14551: Cannot Perform a DML Operation Inside a Query in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-06504: PL/SQL: Return Types of Result Set Variables or Query Do Not Match in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-04068: Existing State of Packages Has Been Discarded in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-06508: PL/SQL: Could Not Find Program Unit Being Called in Oracle
- Fixing PL/SQL Errors (ORA-, PLS-)
How to Fix ORA-04063: Package Body Has Errors in Oracle
Books & free tools
Go deeper with the books, or try the free tools.
- Book
Oracle Database 26ai SQL and PL/SQL
700 pages, 66 chapters, 677 examples with their real output, from first query to production PL/SQL.
- Free tool
Oracle PL/SQL API Package Generator
Generate a complete PL/SQL table API package (insert, update, delete, get) from your table definition.
- Free tool
Write Code Online
A minimalist, distraction-free online code editor and notepad for focusing on the code itself.
- Roadmap
Oracle Database Learning Roadmap
A structured path from SQL basics to advanced PL/SQL and database features. Tick off topics as you go.
- Book
Oracle APEX 26.1 API by Example
528 pages, 1,850+ APIs: every APEX PL/SQL package (APEX_JSON, APEX_MAIL, APEX_EXEC…) with working examples.
- Book
AI Applications with Oracle Database 26ai and APEX 26.1
409 pages, 29 chapters, 237 examples: vectors, semantic search, RAG, and LLM calls from SQL and PL/SQL.
All tutorials by topic
PL/SQL Basics 18
Blocks, variables, subtypes, IF, CASE, loops, and cursors.
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…
How to Define Subtypes in PL/SQL
Give PL/SQL types meaningful names with built-in limits, such as a 0 to 100 percentage, and let the language enforce them on every assignment.
How to Use FOR Loop Iteration Controls in PL/SQL
Step by any amount, cover several ranges, filter iterations, and loop over collection values, indexes, or pairs in one PL/SQL FOR loop.
How to Use SQL% Cursor Attributes in PL/SQL
Find out how many rows an UPDATE or DELETE changed, or whether it changed any, with the implicit cursor attributes of PL/SQL.
How to Pass Parameters to Cursors in PL/SQL
Reuse one explicit cursor for many similar queries by giving it parameters with defaults, passed by position or by name.
How to Return Implicit Results from PL/SQL
Return one or more result sets from a PL/SQL block or procedure to the client with DBMS_SQL.RETURN_RESULT, without OUT cursor parameters.
Collections, Records & Types 8
Associative arrays, nested tables, varrays, qualified expressions, object types, and ANYDATA.
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…
How to Use Associative Arrays in PL/SQL
Store values under string or integer keys with associative arrays, use them as fast in-memory lookups, and handle missing keys safely.
How to Use Nested Tables in PL/SQL
Work with unbounded lists in PL/SQL with nested tables, handle gaps after deletions, and pass lists of values to SQL with TABLE().
How to Use Varrays in PL/SQL
Keep small ordered lists with a natural maximum, such as a top three, in varrays, and see how they compare with the other collections.
How to Use Qualified Expressions in PL/SQL
Build whole records and collections in a single expression, with values by position, by name, by index, or computed by an iterator.
How to Use Object Type Inheritance in Oracle
Create a subtype that inherits from a supertype, override its methods, and test and narrow values with IS OF and TREAT.
How to Use ANYDATA in Oracle
Keep numbers, strings, dates, and objects in one column with ANYDATA, and read each value back according to its type.
How to Describe Types at Run Time with ANYTYPE
Find out at run time what type a value has, its name, and the name and type of each of its attributes.
Procedures, Functions & Packages 24
Procedures, functions, packages, overloading, result caching, SQL macros, and table functions.
How to Write Polymorphic Table Functions with DBMS_TF
Write one table function that works on any table, with output columns decided when the query is parsed, using DBMS_TF.
How to Write Table SQL Macros in Oracle
Build parameterized views and reusable query shapes with functions marked SQL_MACRO(TABLE), which Oracle expands into the calling query.
How to Write Scalar SQL Macros in Oracle
Reuse an expression across queries with a function marked SQL_MACRO(SCALAR), which Oracle expands into plain SQL when the query is parsed.
How to Use PRAGMA SERIALLY_REUSABLE in PL/SQL
Reset package variables with every database call using PRAGMA SERIALLY_REUSABLE, and see how it differs from normal session-long state.
How to Overload Subprograms in PL/SQL Packages
Give package functions one name for different kinds of arguments with overloading, and avoid the ambiguous versions that cause PLS-00307.
How to Restrict Callers with ACCESSIBLE BY in PL/SQL
Protect internal helper code by listing the only program units allowed to call it with ACCESSIBLE BY, checked when callers compile.
How to Use AUTHID in PL/SQL
Decide whether a PL/SQL unit runs with its owner's or its caller's privileges with AUTHID, and when each choice is the right one.
How to Speed Up SQL Calls with PRAGMA UDF
Cut the cost of switching between SQL and PL/SQL for functions called from queries with PRAGMA UDF, and know the alternatives.
How to Cache Function Results with RESULT_CACHE in PL/SQL
Speed up frequently called lookup functions by caching their results for all sessions with RESULT_CACHE, invalidated automatically.
How to Use DETERMINISTIC Functions in PL/SQL
Declare pure PL/SQL functions DETERMINISTIC so Oracle can skip repeated calls and allow them in function-based indexes and virtual columns.
How to Create Packages in Oracle Database 23ai PL/SQL
Oracle Database 23ai offers powerful features for modern application development, and packages are at the center of it. A package in PL/SQL is a way to group related…
How to Create Functions in Oracle Database 23ai PL/SQL
Functions in Oracle Database 23ai are stored PL/SQL programs that accept parameters, perform operations, and return a single value. Unlike procedures, which may or may…
How to Create Procedures in Oracle Database 23ai PL/SQL
In Oracle Database 23ai, procedures are a core part of PL/SQL programming. They allow you to group multiple SQL and PL/SQL statements into a single reusable block of…
Oracle Pipelined Function Example
This Oracle tutorial shows how to create Pipelined function. Follow these steps: 1. Create a Type Object For Oracle pipelined function, we must have a type object…
Oracle Autonomous Transaction Example
In Oracle, an autonomous transaction can commit or rollback the data in the same session without committing or rolling back in the main transaction. PRAGMA (compiler…
How To Truncate Table In Oracle Procedure?
In Oracle, TRUNCATE command is a DDL statement, so you can not execute it directly in Oracle Procedure. To execute DDL commands in Oracle procedure, use EXECUTE…
How to Use Global Temporary Table in Oracle Procedure?
In Oracle, global temporary tables known as session tables and we use global temporary tables to keep the data temporarily in it while processing. The below is an…
Table Type in Oracle Stored Procedure Example
The following is an example of Oracle stored procedure, in which table type collection is used to process the data. Process Data Using Table Type in Oracle Stored…
How to Create Function in PL/SQL?
To create a function in PL/SQL, use CREATE OR REPLACE FUNCTION statement. Below are the syntax details and an example. Syntax CREATE [OR REPLACE] FUNCTION function_name…
How to Return ResultSet from Stored Procedure in Oracle?
In this tutorial, I am giving an example to return resultset from a stored procedure in Oracle using sys_refcursor. Follow these steps to return resultset from a stored…
How to Execute Function in Oracle With Parameters
In Oracle, you can execute functions that accept parameters in several ways. Functions are commonly used to return a single value based on the input you provide. Below…
Stored Procedure in Oracle Example With IN OUT Parameter
In Oracle PL/SQL, a stored procedure can have different types of parameters: IN, OUT, and IN OUT. The IN parameter is used to pass a value into the procedure, while the…
How to Execute a Procedure Inside a Package in Oracle
To execute a procedure that is defined within a package in Oracle, you simply reference the procedure using the package name followed by the procedure name. This makes…
How to Create a Procedure Inside a Package in Oracle
When working with Oracle PL/SQL, it is important to understand the correct way to define procedures within packages. In Oracle, every procedure or function that is part…
Triggers 8
Row, statement, compound, INSTEAD OF, and system triggers, and the mutating table error.
How to Use Triggers in Oracle Database 23ai PL/SQL
Triggers in Oracle Database 23ai are powerful database objects that automatically execute when specific database events occur. They are closely tied to tables, views, or…
Oracle Trigger WHEN Clause Example
In this article, you will learn how to use WHEN clause in Oracle trigger to make the trigger fire on the specified condition. Below is an example. You can test this…
Oracle Before Insert OR Update Trigger Example
The following is an Oracle BEFORE INSERT OR UPDATE Trigger example to perform validations while inserting or updating the records in EMP table. Oracle BEFORE INSERT OR…
How to Write Statement-Level Triggers in Oracle
Run trigger logic once per statement instead of once per row, to forbid operations such as deletes or to log batch changes.
How to Write Compound Triggers in Oracle
Combine statement-level and row-level trigger logic in one compound trigger with shared variables, to read once, check each row, and report once.
How to Write INSTEAD OF Triggers in Oracle
Make views over joins and aggregates updatable with INSTEAD OF triggers that turn DML on the view into the changes you choose on its tables.
How to Fix the Mutating Table Error in Oracle (ORA-04091)
Understand why a row trigger cannot query its own table, and fix ORA-04091 with a compound trigger that reads the data before the rows change.
How to Write Logon Triggers in Oracle
Record who connects with AFTER LOGON triggers and keep a history of schema changes with AFTER DDL triggers, and manage your triggers.
Dynamic SQL & Bulk Processing 9
EXECUTE IMMEDIATE, DBMS_SQL, BULK COLLECT, FORALL, and parallel chunked updates.
How to Update Large Tables in Chunks with DBMS_PARALLEL_EXECUTE
Run a large UPDATE as many small committed chunks in parallel jobs, and resume the chunks that fail.
How to Convert DBMS_SQL Cursors to Ref Cursors (TO_REFCURSOR)
Turn a DBMS_SQL cursor into a ref cursor to return it, or a ref cursor into a DBMS_SQL cursor to describe its columns.
How to Fetch Arrays with DBMS_SQL.DEFINE_ARRAY
Fetch the rows of a dynamic query in batches into collections, the DBMS_SQL way of doing BULK COLLECT with a LIMIT.
How to Bind Arrays with DBMS_SQL.BIND_ARRAY
Bind collections to placeholders in dynamic SQL so one execution inserts or updates a row for every element.
How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS
Learn the names, types, and nullability of a query's columns at run time, and write PL/SQL that prints any query.
How to Use FORALL for Bulk DML in PL/SQL
Replace slow row-by-row loops with FORALL, which sends one DML statement for a whole collection, and see how much faster it is.
Oracle PL/SQL: Dynamic SQL Example Using Execute Immediate
In this tutorial, I am giving an Oracle PL/SQL program example to demonstrate Dynamic SQL using EXECUTE IMMEDIATE statement. Oracle PL/SQL EXECUTE IMMEDIATE Dynamic SQL…
Oracle Dynamic SQL Example to Insert a Record Using DBMS_SQL
In this tutorial, I am giving an Oracle dynamic SQL example to insert a record using the DBMS_SQL package. Through dynamic SQL, you can parse any DML and DDL statement…
Oracle PL/SQL Bulk Collect With Save Exceptions Example
In my previous posts, I have given examples for Bulk Collect operations in Oracle. Here is an example of Bulk Collect with Save Exceptions to handle errors during bulk…
Exceptions & Debugging 11
Raising and handling exceptions, error codes and stacks, ORA- and PLS- errors, and logging.
How to Format Oracle Error Messages with UTL_LMS
Read Oracle error messages in other languages and build your own messages from templates with %s and %d placeholders.
How to Read the Call Stack with UTL_CALL_STACK
Find where your PL/SQL code is running and where an error came from, as separate values with subprogram names inside packages.
How Exceptions Propagate in PL/SQL
Follow an exception through blocks and procedures until a handler catches it, avoid the declaration-error trap, and log and re-raise.
How to Name Oracle Errors with PRAGMA EXCEPTION_INIT
Handle any Oracle error by name with PRAGMA EXCEPTION_INIT, and give your own RAISE_APPLICATION_ERROR errors names that callers can catch.
How to Use SQLCODE in PL/SQL Exception Handlers
Find out which error a PL/SQL handler caught with SQLCODE and SQLERRM, and target common errors with predefined exception names.
How to Handle Exceptions in Oracle Database 23ai PL/SQL
In Oracle Database 23ai PL/SQL, errors can occur during code execution due to invalid data, missing rows, constraint violations, or runtime issues. Exception handling…
Error Handling with Dbms_utility.format_error_backtrace
Error handling is a crucial part of writing reliable PL/SQL code. While Oracle provides exception handling mechanisms using BEGIN…EXCEPTION…END, developers often face…
Using DBMS_OUTPUT to Debug PL/SQL Code
Debugging is one of the most important tasks for any developer working with PL/SQL code. Oracle provides a very simple yet powerful package called DBMS_OUTPUT that…
How to Create Log File in Oracle Using PL/SQL?
You have a PL/SQL program, and you want to log information in a text file of each step of your code. Here is an example to create a log file in Oracle using PL/SQL.…
How to Raise Exception in PL/SQL?
You can raise an error in PL/SQL using user-defined exception. Steps to Raise An Exception in PL/SQL Declare user-defined exception in the declare section of PL/SQL…
PL/SQL - Raise Application Error Example
In PL/SQL, you can use the raise_application_error procedure to throw custom error messages when a specific condition is not met in your code. This is especially helpful…
Fixing PL/SQL Errors (ORA-, PLS-) 24
What common PL/SQL runtime and compile errors mean and how to fix them: NO_DATA_FOUND, cursors, collections, PLS-00103, and more.
How to Fix ORA-01001: Invalid Cursor in Oracle
Avoid fetching from or closing a cursor that is not open, and see the reworded ORA-01001 message of Oracle AI Database 26ai.
How to Fix ORA-01403: No Data Found in Oracle PL/SQL
Understand why SELECT INTO raises NO_DATA_FOUND, handle it where a missing row is normal, and watch for it in functions called from SQL.
How to Fix ORA-01422: Exact Fetch Returns More Than Requested Number of Rows in Oracle
Make SELECT INTO return exactly one row with the full key or a stated rule, or read all rows with BULK COLLECT.
How to Handle ORA-01476: Divisor Is Equal to Zero in PL/SQL Loops
Keep a PL/SQL batch running when one row divides by zero, by handling ZERO_DIVIDE per row or using NULLIF in SQL.
How to Fix ORA-04063: Package Body Has Errors in Oracle
Find the compile errors behind ORA-04063 in USER_ERRORS, fix the package body, and catch invalid objects at deploy time.
How to Fix ORA-04068: Existing State of Packages Has Been Discarded in Oracle
Reproduce ORA-04068 with two sessions, see why package state is discarded after a recompile, and design packages that avoid it.
How to Fix ORA-04098: Trigger Is Invalid and Failed Re-Validation in Oracle
Find why a trigger is invalid in USER_ERRORS, fix it so statements on the table work again, or disable it until it is fixed.
ORA-06502: PL/SQL Numeric or Value Error - Complete Guide to Character String Buffer Solutions
Have you ever encountered the frustrating ORA-06502 error while working with PL/SQL code, only to spend hours trying to figure out why your character string operations…
How to Fix ORA-06504: PL/SQL: Return Types of Result Set Variables or Query Do Not Match in Oracle
Match the columns of a ref cursor's query to the record it is fetched into, and let strong cursor types catch mismatches early.
How to Fix ORA-06508: PL/SQL: Could Not Find Program Unit Being Called in Oracle
Read the error that comes with ORA-06508, such as ORA-04067, and restore or recompile the program unit that could not be run.
How to Fix ORA-06511: PL/SQL: Cursor Already Open in Oracle
Avoid opening an explicit cursor twice by checking %ISOPEN, closing cursors after errors, or using cursor FOR loops.
What ORA-06512: At Line Means in Oracle and How to Find the Error
Read an Oracle error stack from the top, follow ORA-06512 to the code line that raised the error, and keep the location when handling errors.
How to Fix ORA-06519: Active Autonomous Transaction Detected and Rolled Back in Oracle
End autonomous transactions with COMMIT or ROLLBACK before the routine returns, and see why the logged row survives the caller's rollback.
How to Fix ORA-06530: Reference to Uninitialized Composite in Oracle
Initialize object type variables with a constructor before setting their attributes, and check for NULL objects passed in.
How to Fix ORA-06531: Reference to Uninitialized Collection in Oracle
Initialize nested tables and varrays before EXTEND or COUNT, with a constructor or BULK COLLECT, and empty them with DELETE.
How to Fix ORA-06532: Subscript Outside of Limit in Oracle
Keep varray indexes within the declared maximum size by checking COUNT against LIMIT, or use a nested table that can grow.
How to Fix ORA-06533: Subscript Beyond Count in Oracle
Extend nested tables and varrays before assigning new elements, check COUNT or EXISTS before reading, and see how associative arrays differ.
How to Fix ORA-14551: Cannot Perform a DML Operation Inside a Query in Oracle
Call functions that change data from PL/SQL, split reading from writing, or log with an autonomous transaction when needed.
How to Fix PLS-00103: Encountered the Symbol When Expecting One of the Following in Oracle
Read the PLS-00103 message, find the real mistake just before the reported position, and fix the common PL/SQL syntax slips.
How to Fix PLS-00201: Identifier Must Be Declared in Oracle
Resolve PLS-00201 by correcting the name, creating the missing object, or granting access to another schema's object directly.
How to Fix PLS-00302: Component Must Be Declared in Oracle
Find why a record field or package subprogram is not visible, from misspellings to procedures missing from the package spec.
How to Fix PLS-00306: Wrong Number or Types of Arguments in Call in Oracle
Match procedure and function calls to their parameter lists with USER_ARGUMENTS, and make calls clearer with named notation.
Resolve PLS-00323 Error in Oracle
The reason for the PLS-00323 error in Oracle is a mismatch in procedure or function declaration in the package specification and the package body. To resolve this take…
How to Fix PLS-00905: Object Is Invalid in Oracle
Find why a stored procedure became invalid, read its compile errors in USER_ERRORS, and recompile it once the cause is fixed.
Files, LOBs & Import/Export 29
UTL_FILE, BLOBs and CLOBs with DBMS_LOB, compression, ZIP and PDF files, 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…
How to Read a Text File Line by Line with UTL_FILE.GET_LINE
Read a text file on the database server one line at a time, detect the end of the file, and split CSV lines into fields.
How to Work with Temporary LOBs in PL/SQL (DBMS_LOB)
Build large text and binary values in PL/SQL with temporary LOBs: create, write, append, read, copy, trim, and free them.
How to Load Files into CLOBs with DBMS_LOB.LOADCLOBFROMFILE
Copy text and binary files from the database server into CLOB and BLOB values, with character set conversion for text.
How to Convert CLOB to BLOB with DBMS_LOB.CONVERTTOBLOB
Encode CLOB text into BLOB bytes in the character set you choose, decode it back, and catch characters that do not convert.
How to Write a CLOB to a File with DBMS_LOB.CLOB2FILE
Save a large text value to a file on the database server in one call, such as a CSV export built with LISTAGG.
How to Edit SecureFile LOBs with DBMS_LOB.FRAGMENT_ Procedures
Insert, delete, replace, and move text in the middle of a SecureFile LOB without rewriting everything after it.
How to Compress Data with UTL_COMPRESS
Shrink text-like BLOB and RAW data to a fraction of its size in gzip format, and see what is not worth compressing.
How to Compress Data in Pieces with UTL_COMPRESS.LZ_COMPRESS_ADD
Build a compressed BLOB row by row as data is read, without holding all of it uncompressed in one variable first.
Web, HTTP, Email & Integration 22
UTL_HTTP, network ACLs, email, TCP, the PL/SQL Web Toolkit, JavaScript, Java, and Python.
How to Read Request Data with OWA_UTIL
Read the query string, headers, and other request data in PL/SQL web procedures, and test them without a web server.
How to Generate HTML with the PL/SQL Web Toolkit (HTP)
Write HTML pages from PL/SQL procedures with HTP and HTF, and test them in a SQL session by printing the page buffer.
How to Exchange Values with JavaScript in DBMS_MLE
Send PL/SQL values into JavaScript, compute with them, and read the results back into PL/SQL variables.
How to Run JavaScript Dynamically with DBMS_MLE
Evaluate JavaScript source at run time inside the database, run SQL from JavaScript, and find where JavaScript errors happened.
How to Store Credentials with DBMS_CREDENTIAL
Store user names and passwords in the database under a name, so PL/SQL code and jobs never contain the password itself.
How to Send E-mail with UTL_MAIL
Install UTL_MAIL and send e-mail from PL/SQL in one call, with copies, a MIME type, and a text or binary attachment.
How to Open TCP Connections with UTL_TCP
Connect to any host and port from PL/SQL, write and read text lines, and speak simple protocols such as HTTP by hand.
How to Call HTTPS Endpoints with UTL_HTTP.SET_WALLET
Trust HTTPS servers from PL/SQL with the operating system's certificate store or an Oracle wallet, set with SET_WALLET.
How to Send a POST Body with UTL_HTTP.WRITE_TEXT
Send JSON to a REST API with a POST request from PL/SQL, read the result, and handle requests the server rejects.
How to Read HTTP Responses with UTL_HTTP.BEGIN_REQUEST
Call web services with full control: request headers, status codes, response headers, bodies, authentication, and redirects.
How to Call a Web Page with UTL_HTTP.REQUEST
Send a GET request and get the response body in one call, from SQL or PL/SQL, and read JSON results in the same query.
How to Look Up Host Names with UTL_INADDR
Find the IP address of a host name, or the name of an address, as the database server resolves them.
How to Allow Network Access with DBMS_NETWORK_ACL_ADMIN
Allow a database user to reach specific hosts and ports for HTTP, connections, or name lookups, and fix ORA-24247.
How to Encode URLs with UTL_URL
Turn spaces, accented letters, and reserved characters into % codes for URLs and query values, and decode them again.
Creating an AI Agent in PL/SQL for Oracle APEX
Welcome to the next evolution of database programming. You can now build dynamic AI agents directly within your code, eliminating the need to rely entirely on static…
DUO Two Factor Authentication Using PL/SQL
This tutorial will teach you how to integrate DUO Two Factor Authentication using PL/SQL in the Oracle database. What is Two Factor Authentication? Two-factor…
Oracle UTL_HTTP Post Multipart/Form-Data (JSON & ZIP) Example
Here I am giving an example to send JSON and a Zip file to the REST web service using Oracle UTL_HTTP post multipart/form-data. Initially, I took the example code from…
Retrieve Messages from Mailbox Using PL/SQL Mail_Client API
Recently I got the task to build a kind of mail client system in Oracle Apex. The requirement was to show the messages of mail Inbox on a page for a particular account…
Oracle UTL_SMTP: Send Mail with Attachment Example Using Oracle Wallet Authentication
Send a mail with Attachment using Oracle UTL_SMTP and Oracle Wallet Authentication example. The following is the example of Oracle Wallet configuration information…
How to Call Oracle Function in Python?
In this tutorial, you will learn how to call Oracle function in Python. I am giving an example by using cx_Oracle library callfunc function in the Python program. The…
How to Call Oracle Stored Procedure in Python?
In this blog post, I am giving an example to call Oracle stored procedure in Python. Example include calling a stored procedure with IN-OUT parameters. I am using…
Java in Oracle Database Example
In this tutorial, I am giving an example to implement Java code in Oracle Database using the stored function. We can create Java programs in the Oracle database by using…
Jobs, Queues & Messaging 16
DBMS_SCHEDULER jobs and chains, Advanced Queuing, pipes, alerts, locks, and change notification.
Running an Oracle Procedure in Background
In this Oracle tutorial, you will learn how to run a stored procedure in the background using DBMS_SCHEDULER. Running a procedure in the background means that the stored…
How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB
Schedule PL/SQL to run nightly with one call, run it on demand to test it, check its runs, and stop or drop jobs.
How to Write Scheduler Calendar Expressions in Oracle
Describe when Scheduler jobs repeat with FREQ, INTERVAL, and BY clauses, and test the run dates before you schedule anything.
How to Create Scheduler Programs in Oracle (schedules, arguments)
Define what to run as a program with arguments and when as a named schedule, and combine them in jobs with argument values.
How to Handle Scheduler Job Failures in Oracle (job log, retries)
Find the error of a failed Scheduler job in the run log, and stop a job that keeps failing with the MAX_FAILURES attribute.
How to Build Job Chains with DBMS_SCHEDULER
Run load, check, and report steps as one Scheduler job, with rules that start the next step on success or failure.
How to Use DBMS_JOB in Oracle (legacy jobs)
Maintain old code that uses DBMS_JOB, see how its jobs become Scheduler jobs, and map each call to DBMS_SCHEDULER.
How to Create Queues with DBMS_AQADM
Create a queue table and a queue for your own message type, start it, and stop enqueuing or dequeuing separately.
How to Enqueue Messages with DBMS_AQ.ENQUEUE
Add messages to a queue with priorities, correlation IDs, expiration, and delays, committed together with your data.
How to Dequeue Messages with DBMS_AQ.DEQUEUE
Take messages off a queue in priority order, wait for new ones, filter by correlation ID, and browse without removing.
How to Add Queue Subscribers in Oracle
Deliver each queue message to several subscribers, filter what each receives with rules, and dequeue by consumer name.
How to Use Transactional Event Queues in Oracle
Create a sharded, high-throughput event queue in one call, publish JSON events, and read them as a subscriber.
How to Send Messages Between Sessions with DBMS_PIPE
Send packed messages from one session to another through named pipes in memory, and keep a cached value in a singleton pipe.
How to Signal Events with DBMS_ALERT
Let sessions wait for named events and wake them with a message that is delivered when the signaling transaction commits.
How to Get Change Notifications with DBMS_CQ_NOTIFICATION
Have Oracle call your PL/SQL procedure after committed changes to the tables a query reads, with the table, operation, and rows.
How to Use User Locks with DBMS_LOCK
Make sure only one session runs a process at a time with named user locks, and read the result codes REQUEST and RELEASE return.
JSON, XML & Text Search 15
JSON_OBJECT_T, JSON schemas and duality views, SODA, XML packages, and Oracle Text search.
How to Use Ubiquitous Search with DBMS_SEARCH
Build one search index over several tables and every column, and find matching rows in all of them with one query.
How to Highlight Search Results with CTX_DOC.SNIPPET
Show where search words matched, with a highlighted extract from SNIPPET or the full document marked up by MARKUP.
How to Write Oracle Text CONTAINS Queries (CTX_QUERY)
Search text columns with Boolean, proximity, fuzzy, and stem operators, escape special characters, and count hits.
How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX
Make new and changed rows searchable in an Oracle Text index with SYNC_INDEX or automatic sync, and keep it optimized.
How to Register XML Schemas with DBMS_XMLSCHEMA
Register an XML schema in the database under a URL, and check whether XML documents follow its structure and rules.
How to Parse XML with DBMS_XMLDOM
Parse XML into a DOM tree in PL/SQL, read elements and attributes, add new elements, and write the document back out.
How to Save XML to Tables with DBMS_XMLSTORE
Turn XML in ROWSET and ROW form into inserts, updates, and deletes on a table, with key and update columns.
How to Generate XML from Queries with DBMS_XMLGEN
Turn any query result into an XML document with one element per row, and set the tag names, NULL handling, and row limit.
How to Use XMLTYPE Methods in PL/SQL
Work with XML documents in PL/SQL variables: find the root, extract nodes with XPath, test for nodes, and apply XSLT.
How to Use SODA in PL/SQL (DBMS_SODA)
Work with JSON documents as a document store in PL/SQL: collections, keys, query-by-example filters, and replacements.
How to Generate Duality Views with DBMS_JSON_DUALITY
Infer tables and a JSON relational duality view from existing documents, generate the DDL, and import the documents.
How to Create Views on JSON with DBMS_JSON
Turn the fields of JSON documents into the columns of a relational view, generated from a search index data guide.
How to Validate JSON with DBMS_JSON_SCHEMA
Check JSON documents against a schema in PL/SQL, get a report of every error, and generate schemas from tables.
How to Use JSON_ARRAY_T in PL/SQL
Build JSON arrays element by element in PL/SQL, read them by position, and insert, remove, and check elements.
How to Use JSON_OBJECT_T in PL/SQL
Read and change JSON documents step by step in PL/SQL, and move them between JSON columns and JSON_OBJECT_T.
Security & Encryption 6
DBMS_CRYPTO, authenticated encryption, SQL injection prevention, VPD, and fine-grained auditing.
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 Encrypt Data with DBMS_CRYPTO.ENCRYPT
Protect sensitive values with AES-256 encryption in PL/SQL, with random keys and a new initialization vector for every value.
How to Use AES-GCM Authenticated Encryption in Oracle
Encrypt data and detect any change to it with AES-GCM, using an authentication tag and additional authenticated data.
How to Prevent SQL Injection with DBMS_ASSERT in PL/SQL
See how concatenated input rewrites a query, stop it with bind variables, and validate table and column names with DBMS_ASSERT.
How to Hide Columns with Column-Level VPD (DBMS_RLS)
Keep every row visible but show sensitive columns such as e-mail and phone only where a security policy allows it.
How to Audit Queries with Fine-Grained Auditing (DBMS_FGA)
Record only the queries that read sensitive columns of chosen rows, and run a handler procedure every time one happens.
Performance, Tuning & Code Quality 16
Execution plans, statistics, tracing, profiling, code coverage, and conditional and native compilation.
How to Check Table Space Usage with DBMS_SPACE
Find out how full a table's blocks are, and estimate how much space a planned table of millions of rows will need.
How to Measure PL/SQL Code Coverage
Record which blocks of PL/SQL code your tests actually run, see the percentage covered, and find the untested branches.
How to Profile PL/SQL with DBMS_HPROF
Record every PL/SQL call and SQL statement while code runs, and find the functions and statements where the time goes.
How to Manage the Result Cache with DBMS_RESULT_CACHE
See cached query results being reused, and check, flush, or invalidate the server result cache with DBMS_RESULT_CACHE.
How to Trace Sessions with DBMS_MONITOR
Measure the work of one end user, module, or session with targeted statistics and SQL tracing, instead of the whole database.
How to Lock Statistics with DBMS_STATS Preferences
Change how one table's statistics are gathered with preferences, and freeze them with a lock to keep plans stable.
How to Gather Statistics with DBMS_STATS
Keep the optimizer's picture of your data current by gathering table, column, and histogram statistics after large changes.
How to Show Actual Plans with DBMS_XPLAN.DISPLAY_CURSOR
See the plan a statement actually ran with, and compare the optimizer's estimated rows with the actual rows of each step.
How to Manage Compiler Warnings with DBMS_WARNING
Turn PL/SQL compiler warnings on, off, or into errors from code, read the current setting, and restore it afterward.
How to Use DBMS_PREPROCESSOR
Print the source the PL/SQL compiler actually sees after conditional compilation, for stored units or source text.
How to Report Progress with SET_SESSION_LONGOPS
Publish how far a long PL/SQL batch job has got, with a message and a time estimate, in V$SESSION_LONGOPS.
How to Track Sessions with DBMS_APPLICATION_INFO
Tell DBAs and monitoring tools what each session is doing by setting module, action, and client information from your code.
How to Time Code with DBMS_UTILITY.GET_TIME
Measure how long PL/SQL takes with GET_TIME and GET_CPU_TIME, and compare the two to tell computing from waiting.
How to Compile PL/SQL Natively and Wrap Source Code
Speed up compute-heavy PL/SQL with native compilation, and hide the source of procedures and package bodies by wrapping them.
How to Use PL/SQL Compiler Warnings
Find code that compiles but is probably wrong, such as unreachable lines, with PLSQL_WARNINGS, and make the worst ones errors.
How to Use Conditional Compilation in PL/SQL
Keep debug code and version-specific code in one PL/SQL source, and choose what is compiled with PLSQL_CCFLAGS and DBMS_DB_VERSION.
Schema, Metadata & Session Utilities 18
DBMS_METADATA, online redefinition, editions, DBMS_ROWID, DBMS_SESSION, and other schema tools.
How to Compile All Invalid Packages in Schema?
Below is an example of PL/SQL program to compile all invalid package specifications and all invalid package bodies for the current user (SCHEMA) in Oracle. Also, if any…
How to Compare Two Database Objects in Oracle?
In this tutorial, I am giving an example to compare two database table objects of different schemas using the DBMS_COMPARISON utility package in Oracle. Steps to Compare…
How to Get DDL with DBMS_METADATA.GET_DDL
Rebuild the CREATE statement of any object from the data dictionary, with its indexes and constraints, ready to run as a script.
How to Extract Schema DDL with the DBMS_METADATA Browse API
Script out the DDL of many objects at once, filtered by name or base table, with the DBMS_METADATA open-fetch-close API.
How to Compare Object DDL with DBMS_METADATA_DIFF
Compare two versions of a table and generate the ALTER statements between them, then review what the script would do.
How to Get Object Metadata with DBMS_DEVELOPER.GET_METADATA
Get the columns, keys, indexes, and statistics of a table, index, or view as one JSON document in Oracle AI Database 26ai.
How to Describe Procedures with DBMS_DESCRIBE
Find out the parameters, types, modes, defaults, and overloads of any procedure or function at run time.
How to Resolve Object Names with DBMS_UTILITY.NAME_RESOLVE
Find out what an object name really refers to, through schemas and synonyms, and split or canonicalize names in PL/SQL.
How to Expand Views with DBMS_UTILITY.EXPAND_SQL_TEXT
Replace every view and table SQL macro in a query with the SQL it stands for, to see what the database really runs.
How to Work with ROWIDs Using DBMS_ROWID
Split a ROWID into its object, file, block, and row number to see where rows are stored, including in bigfile tablespaces.
How to Check the Database Version with DBMS_DB_VERSION
Read the database version and release as PL/SQL constants, and use them to compile the right code for each release.
How to Use DBMS_SESSION in Oracle
Identify end users, change NLS settings, check roles, pause, and set application context values for the current session.
How to Reset Package State with DBMS_SESSION.RESET_PACKAGE
Throw away package variables and caches in the current session with RESET_PACKAGE or MODIFY_PACKAGE_STATE, and start clean.
How to Use Edition-Based Redefinition in Oracle
Install a new version of PL/SQL code next to the old one in a new edition, test it, and switch sessions over without downtime.
How to Redefine Tables Online with DBMS_REDEFINITION
Partition a table or change its columns while it stays in use, with an interim table and DBMS_REDEFINITION.
How to Sync Tables with DBMS_COMPARISON.CONVERGE
Find the rows that differ between two copies of a table, then make one copy match the other with CONVERGE.
How to Use DBMS_FLASHBACK in PL/SQL
Make every query of a session see the database as it was at an earlier SCN or time, then return to the present.
How to Verify Blockchain Tables with DBMS_BLOCKCHAIN_TABLE
Recompute the hash chain of a blockchain table to prove no row was changed, and delete rows once their retention ends.
Strings, Dates & Utilities 14
Split strings, random data, string matching, UTL_RAW, Base64, dates and time zones, and validation.
How to Compare Strings with UTL_MATCH (edit distance, Jaro-Winkler)
Find close matches for misspelled names and places with edit distance and Jaro-Winkler similarity scores.
How to Escape Text with UTL_I18N
Make text safe for HTML and XML, turn references back into characters, validate encodings, and look up locale data.
How to Encode Base64 with UTL_ENCODE
Turn text and binary data into Base64 for JSON, XML, and e-mail, including a BLOB encoded in pieces as a data URI.
How to Convert Character Sets with UTL_RAW.CONVERT
Turn text bytes from one character set into another, such as UTF-8 into Latin-1, and see what happens to characters that do not fit.
How to Cast Numbers to RAW with UTL_RAW
Turn PL/SQL numbers into the bytes binary formats use, in big-endian or little-endian order, and read them back.
How to Do Bit Operations with UTL_RAW
Handle RAW values byte by byte, and store and test yes-or-no flags in single bytes with AND, OR, XOR, and NOT.
How to Generate Random Data with DBMS_RANDOM
Create random numbers, strings, and test rows, shuffle query results, and get repeatable sequences with a seed.
How to Split CSV Lists with DBMS_UTILITY.COMMA_TO_TABLE
Turn a comma-separated list of names into a PL/SQL table and back, and see why numbers and other values are rejected.
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 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…
No tutorials match your search.
Try a shorter word, or search all of vinish.dev.