Oracle PLSQL Samples

Les' Oracle Reference Sheet

Oracle : DDL : Alter a column
Oracle : DDL : Analyze and Gather Statistics
Oracle : DDL : Changing a column's type or name
Oracle : DDL : Creating a table - Part II
Oracle : DDL : Creating a table
Oracle : DDL : Creating a table with NOLOGGING clause
Oracle : DDL : Creating an index with the nosort option
Oracle : DDL : Creating and using a sequence
Oracle : DDL : Creating constraints
Oracle : DDL : Creating indexes
Oracle : DDL : Moving a table
Oracle : DDL : Oracle data types
Oracle : DDL : Partitioned Tables
Oracle : DDL : Renaming a table
Oracle : DDL : Synonyms and Database Links
Oracle : PL-SQL : Global Temporary Tables

Oracle With Clause Sample

Oracle : Administration : Using SQL Loader to load data with embedded CRs / Line Feeds
Oracle : Monitoring : Displaying Running SQL Commands and Wait Times
Oracle : PL-SQL : Arrays and structures
Oracle : PL-SQL : Complex IF/THEN Processing
Oracle : PL-SQL : Conditionals
Oracle : PL-SQL : Creating a stored procedure
Oracle : PL-SQL : Creating triggers

PLSQL Functions

Triggers

Oracle : PL-SQL : More on triggers The Trigger-Happy DBA - System Triggers

PRAGMA AUTONOMOUS_TRANSACTION - Note that with this example the trigger will insert and commit log entries - even if the main transaction is rolled-back!

Oracle : PL-SQL : Cursors
Oracle : PL-SQL : Date Manipulation
Oracle : PL-SQL : Displaying output
Oracle : PL-SQL : Editing SQL within SQL-Plus
Oracle : PL-SQL : Exception Handling
Oracle : PL-SQL : Exit if error, in a SQL script
Oracle : PL-SQL : Forcing an index with a hint
Oracle : PL-SQL : Formatting Records for Output Files in SQL-Plus
Oracle : PL-SQL : Global Temporary Tables
Oracle : PL-SQL : How to do an outer join in Oracle
Oracle : PL-SQL : Lead and Lag Functions
Oracle : PL-SQL : Looping in PL-SQL

It’s important to know when not to use cursor FOR loops

Explicit Cursors -- The use of explicit cursors allows PL/SQL programs to handle sets of rows, rather than being forced to query a single row for each operation. Each row that meets the criteria defined by the cursor’s WHERE clause can be processed individually.

Implicit Cursors -- An implicit cursor is created by Oracle for all SQL statements that manipulate data (even if the query returns only a single row). If a query has a chance of returning multiple rows, it’s a good idea to use an explicit cursor to retrieve the rows instead of using a single SELECT statement.

Oracle : PL-SQL : Loops in PL-SQL : Hierarchy
Oracle : PL-SQL : Oracle Procedures : Fixing Invalid Objects
Oracle : PL-SQL : Output variables
Oracle : PL-SQL : Packages
Oracle : PL-SQL : Parallel Query Processing
Oracle : PL-SQL : Passing parms to a SQL script via SQL-Plus
Oracle : PL-SQL : Ranking Data
Oracle : PL-SQL : SQL Loader: Using SQL Loader with Inline carriage returns
Oracle : PL-SQL : Showing the Query Plan
Oracle : PL-SQL : Sleep and Wait
Oracle : PL-SQL : Update from one table to another
Oracle : PL-SQL : Using Blobs
Oracle : PL-SQL : Using the Context cartridge
Oracle : UNIX : SQL Loader
Oracle : UNIX : SQL*Net
Oracle : UNIX : Using SQL-Plus

Collections:

Oracle Bulk Collect

The FORALL command can be used in conjunction with a DML statement with a RETURNING clause

The RETURNING clause is used to return specific columns from rows manipulated by DML statements. When DML statements manipulate multiple rows, the data returned can be loaded into a collection using a bulk operation. The returning_bulk_collect.sql script provides an example of this functionality.

Ref Cursor

1) Working with REF CURSOR in PL/SQL

2) Working with REF CURSOR in PL/SQL - Working with RECORD and REF CURSOR

3) Working with REF CURSOR in PL/SQL - Working with more than one query with the same REF CURSOR

4) Working with REF CURSOR in PL/SQL - Working with REF CURSOR inside loops

5) Working with REF CURSOR in PL/SQL - Dealing with REF CURSOR in the sub-programs of a PL/SQL block

limit 500;

Can one use dynamic SQL statements from PL/SQL?

Text Files

Reading Text Files using Oracle PL/SQL and UTL_FILE

Reading Text Files using Oracle PL/SQL and UTL_FILE - Improving it a bit with exception handling

Exception Handling

Database Interaction with PL/SQL: Pre-defined Exceptions

NO_DATA_FOUND

TOO_MANY_ROWS

DUP_VAL_ON_INDEX

ZERO_DIVIDE, INVALID_NUMBER, VALUE_ERROR

OTHERS

Tuning PL/SQL Code

Analyzing Performance of PL/SQL Code

DBMS_PROFILER

DBMS_UTILITY.GET_TIME

Tuning PL/SQL Code - Optimizing PL/SQL Performance

Use BULK COLLECT when querying multiple rows

Use FORALL when modifying multiple rows

Use the NOCOPY hint when passing large structures

Use PLS_INTEGER for intensive integer computations

Use BINARY_FLOAT or BINARY_DOUBLE for ﬂoating-point arithmetic

Group together related programs in a package

Pin into shared memory large and frequently executed programs

Oracle : Monitoring : A Simple Monitoring Tool
Oracle : Monitoring : Active sorts in instance
Oracle : Monitoring : Archive Log Mode Status
Oracle : Monitoring : Check the current number of extents and blocks allocated to a seg
Oracle : Monitoring : Check the extents for a given segment
Oracle : Monitoring : Connection Errors
Oracle : Monitoring : Constraint columns
Oracle : Monitoring : Constraint listing
Oracle : Monitoring : Determining Cache Hit Percentages
Oracle : Monitoring : Displaying Running SQL Commands and Wait Times
Oracle : Monitoring : Explain Plan: syntax
Oracle : Monitoring : Extent information
Oracle : Monitoring : Extent information for a table
Oracle : Monitoring : Index & constraint information
Oracle : Monitoring : Indexed column listing
Oracle : Monitoring : Information about all rollback segments in the database
Oracle : Monitoring : List Space Allocated by Table
Oracle : Monitoring : List data file information
Oracle : Monitoring : List free and used space in database
Oracle : Monitoring : List information about tablespace to which datafiles belong
Oracle : Monitoring : List log file information
Oracle : Monitoring : List names and default storage parameters for all tablespaces
Oracle : Monitoring : List segments reaching extent limits
Oracle : Monitoring : List segments with fewer than 5 extents remaining
Oracle : Monitoring : List session information
Oracle : Monitoring : List sessions with active transactions
Oracle : Monitoring : List table blocks, empty blocks, extent count, and chain block count
Oracle : Monitoring : List tables and synonyms
Oracle : Monitoring : List tablespace fragmentation information
Oracle : Monitoring : Statistics of the rollback segments currently used by instance
Oracle : Monitoring : Tablespace types, and availability of data files
Oracle : Monitoring : Trigger listing
Oracle : Monitoring : Tuning: buffer cache
Oracle : Monitoring : Tuning: data dictionary cache
Oracle : Monitoring : Tuning: dynamic extension
Oracle : Monitoring : Tuning: library cache
Oracle : Monitoring : Tuning: physical file placement
Oracle : Monitoring : Tuning: rollback segments
Oracle : Monitoring : Tuning: sorts
Oracle : Monitoring : Version information
Oracle : Monitoring : What is going on in the database?