8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Plsql » Here


Oracle PL/SQL Articles

Getting Started

Introduction to PL/SQL - A brief overview of some of the important points you should consider when first trying to learn PL/SQL.

PL/SQL : Stop Making the Same Performance Mistakes - This article describes the common PL/SQL performance mistakes I see people making time and time again.

Oracle 8i

Bulk Binds - Improve performance by reducing the overhead associated with context switches between the PL/SQL and SQL engines.

Collections in Oracle PL/SQL - Use collections in PL/SQL to perform array processing.

Complex Recordsets - Build complex recordsets using temporary or PL/SQL tables within stored procedures.

Data Encryption - DBMS_OBFUSCATION_TOOLKIT - Encrypt and decrypt data using the DBMS_OBFUSCATION_TOOLKIT package.

DBMS_APPLICATION_INFO - Track session and long operation activity more accurately using thes built-in procedures and views.

The DBMS_SYSTEM Package - The DBMS_SYSTEM package contains a number of routines that can be useful on occasion.

Export BLOB - A simple method for exporting the contents of a BLOB datatype to the filesystem.

Export CLOB - A simple method for exporting the contents of a CLOB datatype to the filesystem.

File Handling From PL/SQL - Perform basic file manipulation from PL/SQL using this simple API.

Import BLOB - A simple method for importing the contents of a file into a BLOB datatype.

Import CLOB - A simple method for importing the contents of a file into a CLOB datatype.

InterMedia - Import-Export Of Images - Prior to Oracle 8.1.7 the interMedia support for import and export of data was a little flunky. Even now the API forces you to use directory object to access the file system. The code supplied here will free you from the constraints of inter

Parse XML Documents - Explode unstructured XML documents into relational tables using the XDK for PL/SQL.

Shell Commands From PL/SQL - Use this simple method to perform shell commands from within PL/SQL.

Oracle 9i

ANYDATA Type - This article presents an overview of the ANYDATA type.

Associative Arrays - Oracle9i Release 2 allows you to index-by string values using this renamed collection.

Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle9i Release 2 - Take advantage of bulk binds (BULK COLLECT & FORALL) for performance improvements whilst using record structures.

CASE Expressions And Statements - Learn how to use CASE expressions in both SQL and PL/SQL. In addition, learn how to use the CASE statement in PL/SQL.

Consuming Web Services - Access web services directly from PL/SQL using this simple API.

DBMS_PROFILER - Profile the run-time behaviour of PL/SQL code to identify potential bottlenecks.

DBMS_TRACE - Trace the run-time behaviour of PL/SQL code to identify potential bottlenecks.

DBMS_XPLAN : Display Execution Plans - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.

Dynamic Binds Using Contexts - Simplify dynamic variable binds within dynamic SQL using contexts.

Dynamic Binds Using Query Transformation - Simplify dynamic variable binds within dynamic SQL using query transformation.

Export BLOB Contents Using UTL_FILE - Use the new UTL_FILE functionality to write binary data to files.

Images from Oracle Over HTTP - Retrieve images directly from the database over HTTP.

Metadata API (DBMS_METADATA) - Extract DDL or XML definitions of all database objects using this simple API.

Mutating Table Exceptions - A simple method to prevent triggers producing mutating table exceptions.

Parse XML Documents - Explode unstructured XML documents into relational tables using the new integrated XDB packages.

Pipelined Table Functions - Improve performance of ETL processes by pipelining all transformation functions.

PL/SQL Native Compilation - Improve the performance of PL/SQL procedural code by compiling it to native shared libraries.

PL/SQL Server Pages - Use PL/SQL as a scripting language to generate web pages directly from the database.

PL/SQL Web Toolkit - Generate web pages directly from the database using this simple toolkit.

Stateless Locking Methods - Learn how to avoid data loss in stateless environments.

Storing Passwords In The Database - Store passwords securely in the database using this simple hashing technique.

Useful Procedures And Functions - Procedures and functions you may have overlooked which can come in useful during development.

UTL_FILE Enhancements - Oracle9i Release 2 includes some long overdue enhancements including basic file handling and support for NCHAR and RAW data.

UTL_FILE - Random Access of Files - Use the UTL_FILE package for random access of files from PL/SQL.

Universal Unique Identifier (UUID) - Reduce data migration and replication issues by replacing sequence generated IDs with UUIDs.

XML Generation In Oracle9i Using DBMS_XMLQuery, DBMS_XMLGen, Sys_XMLGen And Sys_XMLAgg - Generate XML and perform XSL transformations with ease using the new XML features of Oracle9i.

XML-Over-HTTP - XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs.

XMLType Datatype - Store XML documents in tables and query them using SQL.

Oracle 10g

PL/SQL Enhancements in Oracle Database 10g - Get to grips with the Oracle 10g enhancements to PL/SQL using simple cut & paste examples.

Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations.

DBMS_ASSERT - Sanitize User Input to Help Prevent SQL Injection - The DBMS_ASSERT package contains a number of functions that can be used to sanitize user input and help to guard against SQL injection.

DBMS_CRYPTO - Learn how to use the replacement for the DBMS_OBFUSCATION_TOOLKIT package.

DBMS_EPG - The Embedded PL/SQL Gateway in Oracle 10g Database Release 2 - Run mod_plsql applications directly from the database using the XML DB HTTP server, rather than Apache.

SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g Onward.

UTL_COMPRESS : Compress and Uncompress Data from PL/SQL - The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE).

UTL_DBWS - Consuming Web Services in Oracle 10g - Use the UTL_DBWS package to consume web services from PL/SQL.

UTL_MAIL : Email from PL/SQL in the Oracle Database - The UTL_MAIL package was introduced in Oracle 10g to provide a simple API to allow email to be sent from PL/SQL.

The WRAP Utility and the DBMS_DDL Package - Learn how to use the enhanced DBMS_DDL package to dynamically wrap PL/SQL source in Oracle 10g Release 2.

Oracle 11g

PL/SQL New Features and Enhancements in Oracle Database 11g Release 1 - Get to grips with the new features and enhancements to PL/SQL in Oracle 11g Release 1.

APPEND_VALUES Hint in Oracle Database 11g Release 2 - Use the APPEND_VALUES hint to perform direct-path inserts when using the FORALL statement.

Automatic Subprogram Inlining in Oracle Database 11g Release 1 - Automatic subprogram inlining can reduce the overheads associated with calling subprograms, whilst leaving your original source code in its normal modular state.

CONTINUE Statement in Oracle Database 11g Release 1 - The CONTINUE statement has finally been added to PL/SQL in Oracle 11g, allowing you to jump out of the current iteration of a loop.

Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1 - Improve the performance of PL/SQL functions across the whole database instance by caching return values.

DBMS_PARALLEL_EXECUTE - Use the DBMS_PARALLEL_EXECUTE package to break down large workloads into manageable chunks that can be run in parallel.

DBMS_XA - Process a single transaction across multiple sessions using the DBMS_XA package.

Edition Based Redefinition in Oracle Database 11g Release 2 - Upgrade database components online using this new feature or Oracle Database 11g Release 2.

Native Oracle XML DB Web Services in Oracle 11g Release 1 - Present your exisitng PL/SQL procedures and functions as web services using XML DB in Oracle 11g.

PL/Scope in Oracle Database 111g Release 1 (11.1) - PL/Scope is a tool that gathers information about user defined identifiers in PL/SQL code at compile time.

PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1 - Use the DBMS_HPROF package and the plshprof utility to generate and analyze hierarchical profiler data for PL/SQL programs.

Trigger Enhancements in Oracle Database 11g Release 1 - Learn about the enhancements to triggers in Oracle Database 11g Release 1.

UTL_MATCH : String Matching by Testing Levels of Similarity/Difference - Use the UTL_MATCH package to determine the similarity between two strings.

Oracle 12c

PL/SQL New Features and Enhancements in Oracle Database 12c Release 1 (12.1) - Learn about the new features and enhancements to PL/SQL in Oracle 12c Release 1 (12.1).

Oracle SQL and PL/SQL Enhancements in Oracle Database 12c Release 2 (12.2) - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 12c Release 2 (12.2).

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1) - Discover how Oracle Database 12c allows you to grant roles directly to PL/SQL program units.

Code Coverage Using DBMS_PLSQL_CODE_COVERAGE in Oracle Database 12c Release 2 (12.2) - The DBMS_PLSQL_CODE_COVERAGE package was introduced in Oracle Database 12c Release 2 (12.2) to provide an API to capture code coverage information during testing.

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES) - Learn how to plug security holes related to invoker rights PL/SQL code in Oracle Database 12c Release 1.

Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER) - This article describes support for invoker rights function calls in views in Oracle Database 12c Release 1.

DEPRECATE Pragma for PL/SQL in Oracle Database 12c Release 2 (12.2) - Oracle database 12.2 introduced the DEPRECATE pragma to mark code as deprecated and display compiler warnings if deprecated code is referenced by any newly compiled code.

Edition-Based Redefinition Enhancements in Oracle Database 12c Release 1 (12.1) - Learn how to create non-editionable objects in edition-enabled schema.

Extended Data Types in Oracle Database 12c Release 1 (12.1) - Ease the migration of applications from other database engines to Oracle 12c by using extended data types.

Implicit Statement Results in Oracle Database 12c Release 1 (12.1) (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT) - Implicit statement results will simplify the migration of stored procedures from other database engines to Oracle 12c onward.

Invisible Columns in Oracle Database 12c Release 1 (12.1) - Oracle database 12c allows columns to be created or modified to make them invisible.

Multitenant : Database Triggers on Pluggable Databases (PDBs) in Oracle 12c Release 1 (12.1) - With the introduction of the multitenant option, database event triggers can be created in the scope of the CDB or PDB.

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) - Define PL/SQL functions and procedures in the WITH clause of SQL statements.

PL/Scope Enhancements in Oracle Database 12c Release 2 (12.2) - PL/Scope is a tool that gathers information about user defined identifiers in PL/SQL code at compile time. Oracle Database 12c Release 2 (12.2) enhances the information captured by PL/Scope.

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c Release 1 (12.1) - Oracle 12c now supports the binding of additional PL/SQL-only data types to SQL.

PL/SQL White Lists Using the ACCESSIBLE BY Clause in Oracle Database 12c Release 1 (12.1) - Use the ACCESSIBLE BY clause to create white lists, adding an extra layer of security to your PL/SQL objects.

JSON Support Enhancements in Oracle Database 12c Release 2 (12.2) - Oracle 12c Release 2 (12.2) includes several enhancements to the JSON functionality in the database. This article details some of them, as well as linking to separate articles on some of them.

PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2) - Oracle 12c Release 2 (12.2) includes several enhancements to the JSON functionality in the database including native support for JSON parsing and generation in PL/SQL.

Using the TABLE Operator with Locally Defined Types in PL/SQL - In Oracle 12c, the TABLE operator can now be used in PL/SQL with locally defined types.

UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1) - Display information about call stack and error stack for PL/SQL subroutines using the UTL_CALL_STACK package.

UTL_URL : Escape and unescape strings in URLs - The UTL_URL package was introduced in Oracle 12.2 to help escape and unescape strings in URLs.

Oracle 18c

SQL and PL/SQL Enhancements in Oracle Database 18c - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 18c.

DBMS_SESSION.SLEEP Replaces DBMS_LOCK.SLEEP in Oracle Database 18c - The SLEEP procedure is added to the DBMS_SESSION package and deprecated from the DBMS_LOCK package in Oracle Database 18c.

Private Temporary Tables in Oracle Database 18c - Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup.

Polymorphic Table Functions in Oracle Database 18c - The return type of a Polymorphic Table Function (PTF) can be determined by input parameters. This differs from conventional table functions, where the output table type is fixed at compile time.

Qualified Expressions (Populating Record Types and Associative Arrays) in PL/SQL in Oracle Database 18c - Qualified expressions provide and alternative way to define the value of complex objects, which in some cases can make the code look neater.

Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c - This article gives an overview of the Simple Oracle Document Access (SODA) for PL/SQL functionality in Oracle Database 18c.

Oracle 19c

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases - This article describes how to install the DBMS_CLOUD package in on-prem 19c and 21c databases.

DBMS_CLOUD Package - This article describes the basic usage of the DBMS_CLOUD package.

Oracle 21c

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases - This article describes how to install the DBMS_CLOUD package in on-prem 19c and 21c databases.

DBMS_CLOUD Package - This article describes the basic usage of the DBMS_CLOUD package.

DBMS_XPLAN : Compare Execution Plans in Oracle Database 19c and 21c (COMPARE_PLANS, COMPARE_CURSORS and COMPARE_EXPLAIN) - This article demonstrates how to compare execution plans using the DBMS_XPLAN package in Oracle Database 19c and 21c.

FOR LOOP Iteration Enhancements in Oracle Database 21c - The FOR LOOP iteration control has been enhanced in Oracle database 21c, making it much more flexible.

JSON Support Enhancements in Oracle Database 21c - Oracle 21c includes several enhancements to the JSON functionality in the database. This post acts as a links page to separate articles on each feature.

JSON Data Type in Oracle 21c - The JSON data type was introduced in the Oracle 20c preview release to provide native JSON support and improve the performance of JSON processing. It has become generally available in Oracle 21c.

Qualified Expressions Enhancements in Oracle Database 21c - Oracle database 21c makes it even easier to populate collections using qualified expressions.

Oracle 23c

Automatic PL/SQL to SQL Transpiler in Oracle Database 23c -
The automatic SQL transpiler in Oracle 23c allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.

Boolean Data Type in Oracle Database 23c - Oracle database 23c introduced the boolean data type in SQL.

CASE Statement and CASE Expression Enhancements in Oracle Database 23c - In Oracle database 23c the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

IF [NOT] EXISTS DDL Clause in Oracle Database 23c - Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23c.

Removal of Touch-Once Restriction after Parallel DML in Oracle Database 23c - Oracle database 23c makes parallel DML more flexible by removing the touch-once restriction after parallel DML.

SELECT Without FROM Clause in Oracle Database 23c - From Oracle 23c onward we can use a SELECT without a FROM clause in some circumstances.

Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23c - In previous releases making HTTPS callouts from the database required the use of a client wallet. From Oracle database 23c onward we can use the operating system certificate store instead.

JSON_VALUE Function Enhancements in Oracle Database 23c - In Oracle database 23c the RETURNING clause of the JSON_VALUE function has been enhanced allowing it to convert JSON data to user-defined types.

Miscellaneous

APEX_DATA_PARSER : Convert simple CSV, JSON, XML and XLSX data to rows and columns. - The APEX_DATA_PARSER package allows you to easily convert simple CSV, JSON, XML and XLSX formatted data into rows and columns from SQL.

APEX_JSON Package: Generate and Parse JSON Documents in Oracle - This article gives a brief overview of using the APEX_JSON package to generate and parse JSON documents in Oracle.

APEX_MAIL : Send Emails from PL/SQL - The APEX_MAIL package provides an API for sending emails from PL/SQL.

APEX_WEB_SERVICE : Consuming SOAP and REST Web Services - Use the APEX_WEB_SERVICE package to simplify consuming SOAP and REST web services from PL/SQL.

APEX_ZIP : Manage Zip Files From PL/SQL - The APEX_ZIP package provides an API to manage zip files from PL/SQL.

Autonomous Transactions - A brief overview of autonomous transactions.

AutoNumber And Identity Functionality - Implement AutoNumber or Identity column behaviour in Oracle.

Check the Contents of Ref Cursors - This article demonstrates a few simple ways to display the contents of ref cursors.

Database Triggers Overview - An introduction to database triggers in Oracle.

DBMS_SQLHASH Package - The DBMS_SQLHASH package allows us to generate a hash based on the result set returned by a query.

DBMS_PIPE - For Inter-Session Communication - This article presents a brief description of the DBMS_PIPE package, explaining how it can be used for non-secure inter-session mesaging.

DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases - A guide to using the DBMS_SESSION package to manage sessions that form part of a connection pool.

Decoupling to Improve Performance - Learn how to decouple processing to give the impression of improved performance.

Download Files Using PL/SQL and a Gateway (ORDS, mod_plsql, EPG) - This article gives some examples of the PL/SQL needed to download files using a PL/SQL gateway, such as ORDS, mod_plsql or the embedded PL/SQL gateway (EPG).

Efficient Function Calls From SQL - This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.

Email From Oracle PL/SQL (UTL_SMTP) - Email from PL/SQL using UTL_SMTP rather than using external procedures or Java.

FTP From PL/SQL - A description of two methods for triggering FTP jobs directly from PL/SQL.

HTML with Embedded Images from PL/SQL - Use PL/SQL to create HTML with embedded images.

Identifying Host Names and IP Addresses - This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

Implicit vs. Explicit Cursors in Oracle PL/SQL - A comparison of the relative performance of implicit and explicit cursors in Oracle PL/SQL.

Instrumenting Your PL/SQL Code - No matter who you are or how cool you think you are at programming, you can never know exactly what is going on in your code unless you instrument it.

List Files in a Directory From PL/SQL and SQL : Comparison of Methods - This article compares the methods available to list files in a directory on the database server.

List Files in a Directory From PL/SQL and SQL : External Table - This article shows how to list files in a directory on the database server using an external table.

List Files in a Directory From PL/SQL and SQL : Java - This article shows how to list files in a directory on the database server using a Java in the database.

List Files in a Directory From PL/SQL and SQL : DBMS_BACKUP_RESTORE - This article shows how to list files in a directory on the database server using the DBMS_BACKUP_RESTORE package.

List Files in a Directory From PL/SQL and SQL : DBMS_SCHEDULER - This article shows how to list files in a directory on the database server using the DBMS_SCHEDULER package.

Logic/Branch Ordering in PL/SQL - This article demonstrates the affect of branch ordering on the performance of CASE and ELSIF statements.

NOCOPY Hint to Improve Performance of OUT and IN OUT Parameters in PL/SQL Code - This article discusses the benefits of using the NOCOPY hint for passing large or complex OUT and IN OUT parameters in PL/SQL.

NULL-Related Funtions (NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL) - A summary of the functions available for handling NULL values.

Oracle Dates, Timestamps and Intervals - An overview of the usage of dates, timestamps and intervals in Oracle databases.

Overlapping Date Ranges - This article presents simple methods to test for overlapping date ranges.

Performance of Numeric Data Types in PL/SQL - This article demonstrates the relative performance of the numeric data types in PL/SQL.

PL/SQL Vs. Oracle JVM: Speed Comparison for Mathematical Operations - This article does a side-by-side speed comparison of PL/SQL and the Oracle JVM for some basic mathematical operations.

Populating Master-Detail Foreign Key Values Using Sequences - This article presents some safe methods for populating master-detail foreign key columns when using sequences.

Regular Expression Support in Oracle (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_LIKE) - Using regular expressions to solve some questions I've been asked over the years.

Retrieving HTML and Binaries into Tables Over HTTP - This article gives two brief examples of how HTML pages and Binaries, such as images or documents, can be downloaded over HTTP and placed directly into tables.

ROWIDs for PL/SQL Performance - This article demonstrates how using ROWIDs in transactions can improve performance.

Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.

Short-Circuit Evaluation in PL/SQL - This article describes how short-circuit evaluation can be used to improve the performance of some boolean expressions.

Using Ref Cursors To Return Recordsets - Return recordsets from Oracle stored procedures.

UTL_HTTP and SSL (HTTPS) using Oracle Wallets - This article describes how to use the UTL_HTTP package to interact with resources secured by SSL (HTTPS).

utPLSQL Installation and Upgrade - This article demonstrates the installation and upgrade of the utPLSQL unit test framework for PL/SQL.

Validate an XML Document Against an XML Schema (XSD) in an Oracle Database - The article gives some simple examples of validating XML documents against a XML Schema (XSD) in an Oracle database.

Web Services and the Oracle Database - An overview of the functionality available in the Oracle database for consuming and publishing web services.

XML-Over-HTTP (REST Web Services) From PL/SQL - XML-over-HTTP was the precursor to web services allowing easy access to XML via HTTP GETs and POSTs.

Liquibase : Deploying Oracle Application Express (APEX) Applications - This article demonstrates how to deploy Oracle Application Express (APEX) applications using Liquibase.

SQLcl : Deploying Oracle Application Express (APEX) Applications using the SQLcl implementation of Liquibase - This article demonstrates how to deploy Oracle Application Express (APEX) applications using the SQLcl implementation of Liquibase.