Oracle PL/SQL Articles

• Oracle 8i
• Oracle 9i
• Oracle 10g
• Oracle 11g
• Oracle 12c
• Miscellaneous

Copied from http://www.oracle-base.com/articles/plsql/articles-plsql.php

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 - 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

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.
PL/SQL Enhancements in Oracle Database 10g - Get to grips with the Oracle 10g enhancements to PL/SQL using simple cut & paste examples.
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_DBWS - Consuming Web Services in Oracle 10g - Use the UTL_DBWS package to consume web services 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

APPEND_VALUES Hint in Oracle Database 11g Release 2 - Use the APPEND_VALUES hint to perform direct-path inserts when using the FORALL statement.
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/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.
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.
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

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

Miscellaneous

Autonomous Transactions - A brief overview of autonomous transactions.
AutoNumber And Identity Functionality - Implement AutoNumber or Identity column behaviour in Oracle.
Database Triggers Overview - An introduction to database triggers in Oracle.
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.
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.
Introduction to PL/SQL - A brief overview of some of the important points you should consider when first trying to learn PL/SQL.
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 : Stop Making the Same Performance Mistakes - This article describes the common PL/SQL performance mistakes I see people making time and time again.
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).
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.

Oracle - Execution Plans , Explain Plans, AWR Reports, Tuning - A handy Guide

There is an inherent different between explain plans and execution plans , and I almost got confused that they were the same.

In order to capture an execution plan , we need to follow the steps:

The user who will be running the sql for which you want the execution plan , should have permissions to alter session in order to enable trace.(This is not mandatory , if you can run the sqls via the user who has permissions then that is also fine)

Grant permission:

grant alter session to ;
revoke alter session from ;

Enable SQL TRACING

To enable Trace run the following DCL:

ALTER SESSION SET sql_trace = true;

To specify trace file identifier which helps to locate specific trace files easily

ALTER SESSION SETtracefile_identifier =tracing_example;

To specify the trace destination other than the oracle default , we could perform below


ALTER SESSION SET user_dump_dest=

Default vaues for the various parameters above can also be found using TOAD->Database->Administer->Parameters

Explain Plan

Using toad , this can be achieved by following the steps below:

1.  Connect to the Oracle SID
2. Open a SQL editor, and write the SQL query for which the explain plan is required.
3. CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
4. Analyze the cost of the query , and identify the areas which are causing the cost to grow high.
5. Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
6. This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

Enable SNAPSHOTS

By enabling snapshots we will be able to capture the Oracle activity between regular periods identified by snapshot ids. To view the current snapshot interval we can run the following:

select * from DBA_HIST_WR_CONTROL

To enable snapshots for every 15 minutes , we can run the following procedure. We can change the snapshot interval based on approximately how much time would a process(which we are interested in profiling / tuning ) takes to run .

BEGIN dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => null); END;

Generate AWR Report

An AWR report captures all Oracle activity between given snapshots. This can be helpful to monitor the queries which are taking longer than expected and also identify tuning paramters.

To generate an AWR report we can perform the following steps:

1. Invoke @awrrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
2. Choose report type as html
3. Provide number of days you want the report to span for snapshot ids
4. Provide the snapshots which we want to capture in our report. 
5. Provide a valid report name.
6. AWR report is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

Many of the above options can be chosen as default as well.

See here http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php
for detailed explanation.  

Capturing SQL ID

If we do not have a AWR report , but still want to identify the the SQL id based on the query content the following can be used:

SELECT sql_id, child_number, substr(sql_text, 1, 300) sqltxt from v$sql WHERE sql_text LIKE %%' ;

Also the various execution plans used by oracle for this SQL can be generated using:

set linesize 150

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID'));

Execution Plan

Execution plan on the other hand is the actual path /plan used by oracle to execute a query.

To extract an execution plan, you will have to perform a few more steps as below:

1. Enable snapshots for the SID which you will be executing the queries from.Set the snapshot interval based on your specific enviroment , sql process running
2. Execute the SQL procedure, process or DBMS transaction which you want to tune.
3. Generate AWR report for the snapsots we are interested in.
4. Browse the sql statistics to identify which queries are taking up the most time , in terms of execution , io waits , cpu etc
5. Each SQL will have a unique SQL_ID , capture that.
6. Invoke @awrsqrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
7. Provide the sql id for which we want to generate the execution plan
8. Provide snapshot ids
9. Provide a name for the plan report
10. Execution plan is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

This plan will provide the exact path , and can be used to identify whats causing the costs for SQL runtime to go high.

Tuning Options:

• Disable tracing
• Disable all SQL profilers
• Perform Database connections monitoring - if connections are being returned once transactions complete
• reduce redo logs
• add hints wherever costs of queries are more due to joins and full table scans
• add appropriate indexes 
• Check Init.ORA for Oracle settings ,and modify based on the needs
• The list if huge .......

Finding missing dates using SQL

Query to find missing dates between two date range in a table. We need to LEFT JOIN the table.

 WITH all_dates AS (
  SELECT TO_DATE('01-jan-09') -- Start-date
    + ROWNUM - 1 AS d
  FROM dual
CONNECT BY ROWNUM <= to_number(to_char(to_date('31-dec-16'),'J')-to_char(to_date('01-jan-09'),'J')) + 1   -- Number of days calculated using Julian numbers
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN  t ON ( t.day = all_dates.d )
WHERE t.day IS NULL
ORDER BY all_dates.d;