Skip to Main Content
Oracle
Live SQL
Help
Sign In
Dark Mode
Home
Home
SQL Worksheet
SQL Worksheet
My Session
My Session
Previous Sessions
Previously Viewed
Utilization
NLS
Schema
Schema
Quick SQL
Quick SQL
My Models
My Scripts
My Scripts
My Tutorials
My Tutorials
Code Library
Code Library
Code Library
Breadcrumb
Search
Area
All
Oracle 19c - 7
SQL Analytics - 54
SQL General - 504
Oracle Text - 10
Data Models - 11
PL/SQL General - 484
Partitioning - 35
Compression - 1
SQL Types - 6
Oracle 12c - 12
Performance and Tuning - 21
Geospatial - 2
NLS - 8
Data Mining/Machine Learning - 1
Category
All
Uncategorized - 391
PL/SQL Triggers - 13
PL/SQL Procedures, Functions, Packages - 80
Types
All
Tutorials
Scripts
Sort By
Date Added
Executions
Name
Likes
Sort Order
Ascending
Descending
Results Per Page
60
120
180
240
300
360
420
480
540
600
Reset Search
Tutorial
PL/SQL Anonymous Blocks
Define blocks of procedural code using PL/SQL.
236
45,332
9.1 years ago
Mike Hichwa (Oracle)
Script
Hello World
Simple example showing how to write text to the console.
DBMS_OUTPUT, pl/sql
99
7,320
9.1 years ago
Mike Hichwa (Oracle)
Tutorial
Get Started with Table Functions 1: Overview
This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". This module of...
table function
85
6,783
6.4 years ago
Steven Feuerstein
Tutorial
Bulk Processing with PL/SQL
Learn how to make the most of the bulk processing features of PL/SQL: BULK COLLECT and FORALL. Be su...
bulk,forall,performance
77
3,272
5.5 years ago
Steven Feuerstein
Tutorial
Get Started with Table Functions 4: Pipelined Table Functions
This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". Pipelined tabl...
pipelined table function
28
1,684
6.4 years ago
Steven Feuerstein
Tutorial
Get Started with Table Functions 2: Returning Multiple Columns
This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". This module sh...
table function
31
1,588
6.4 years ago
Steven Feuerstein
Script
Different Types of Cursors in PL/SQL
An exploration into the different ways you can define and use cursors (pointers to SQL result sets) ...
cursor
96
1,342
8.5 years ago
Steven Feuerstein
Tutorial
Get Started with Table Functions 3: Streaming Table Functions
This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". This module sh...
20
623
6.4 years ago
Steven Feuerstein
Script
''How did I get here?'' DBMS_UTILITY.FORMAT_CALL_STACK
Use the DBMS_UTILITY.FORMAT_CALL_STACK function to answer the question "How did I get here?". Note t...
call stack
22
616
8.8 years ago
Steven Feuerstein
Script
add 2 numbers using PL SQL
this is a program to add two numbers using PL/SQL commands.
6
521
6.7 years ago
Harneet Singh
Script
Function and Procedure in Package
A Test PLSQL Package that demonstrates both a Function and a Procedure.
32
521
9.1 years ago
Hemant K Chitale
Script
PL/SQL case statement
PL/SQL case statement
PL/SQL case statement
8
463
7.7 years ago
PL/SQL case statement
Script
Change-Logging Trigger
Creates a table, a log table, and a trigger that inserts a row in the log table after any row of the...
16
418
9.1 years ago
Oracle
Script
DML Trigger Conditional Predicates: INSERTING, UPDATING, DELETING
The triggering event of a DML trigger can be composed of multiple triggering statements. When one of...
conditional predicate,inserting,updating,deleting
14
365
8.5 years ago
Steven Feuerstein
Script
Row-Level Trigger
Creates a table of words and a trigger that strips leading and trailing blanks from each word that i...
10
312
9.1 years ago
Oracle
Script
Exceptions Raised in Declaration Section Not Handled Locally
This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can only...
exception,declaration,unhandled
9
296
8.6 years ago
Steven Feuerstein
Script
Extending UTL_FILE with Java Methods
UTL_FILE is a handy package but it doesn't do everything everyone wants with files (such as: get lis...
utl_file
1
204
8.3 years ago
Steven Feuerstein
Script
Associative Array Indexed by String
This example defines a type of associative array indexed by string, declares a variable of that type...
LNPLS
4
199
7.8 years ago
Sarah Hirschfeld (Oracle)
Script
Exceptions Do Not Rollback Uncommitted Changes
Any non-query DML statements that complete successfully in your session are not rolled back when an ...
6
189
8.6 years ago
Steven Feuerstein
Script
All About PL/SQL Compiler Settings
Companion to my blog post on the topic.
0
184
6.1 years ago
Steven Feuerstein
Script
Function Result Cache Demo
Demonstrate the use and performance advantage of function result cache
Result cache PLSQL
17
181
9 years ago
Binuraj Somanathan Nair (http://oracleappsdiary.blogspot.co.uk)
Script
JSON Array Parsing
This script shows how strings holding JSON arrays can be parsed using JSON support in Oracle Databas...
json
9
170
8.5 years ago
Lucas Jellema
Script
FORALL Inserts Performance Comparison
Sure, we say FORALL is fast, but how fast, really? And how does it compare to "pure" SQL (which of c...
FORALL,performance,forall_timing
15
162
8.8 years ago
Steven Feuerstein
Script
Varray Examples
The varray (variable size array) is one of the three types of collections in PL/SQL (associative arr...
varray
14
148
8.9 years ago
Steven Feuerstein
Script
Alternative Quoting Mechanism (''Q'') for String Literals
Oracle Database offers the ability, in both SQL and PL/SQL, to specify our own user-defined delimite...
STRING,LITERAL,quote
19
144
8.9 years ago
Sarah Hirschfeld (Oracle)
Script
The $$PLSQL_UNIT and $$PLSQL_TYPE Conditional Compilation Flags
These pre-defined conditional compilation flags, or ccflags, return the name and type of the program...
CCFLAG,CONDITIONAL COMPILATION
5
143
8.6 years ago
Steven Feuerstein
Script
Back Trace Exception to Line That Raised It
The DBMS_UTILITY.format_error_backtrace function, added in Oracle Database 10g Release 2, is a criti...
BACKTRACE,Exception,dbms_utility
10
134
8.8 years ago
Steven Feuerstein
Script
SELECT * FROM [table] with PL/SQL procedure
"In Table" utility: use DBMS_SQL to implement a Method 4 dynamic SQL challenge. Overview: ...
method 4,dynamic SQL
6
130
7.5 years ago
Steven Feuerstein
Script
Error Message Functions: SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK
SQLERRM is a function that returns the current error message (if no argument is passed to it) or the...
ERROR,MESSAGE,stack,sqlerrm
3
128
8.8 years ago
Steven Feuerstein
Script
Function Returns Associative Array Indexed by PLS_INTEGER
This example defines a type of associative array indexed by PLS_INTEGER and a function that returns ...
LNPLS
0
126
7.8 years ago
Sarah Hirschfeld (Oracle)
Script
The Cursor FOR Loop
An exploration into the very useful and elegant cursor FOR loop, in which we declaratively tell the ...
cursor,cursor for loop
16
125
8.5 years ago
Steven Feuerstein
Script
Automatic Optimization of Cursor FOR Loop
This script demonstrates that when you have the PL/SQL optimization level set to at least 2, the PL/...
OPTIMIZATION,PERFORMANCE
9
112
9 years ago
Steven Feuerstein
Script
BASIC LOOP
print numbers 1 to 5 using a basic loop statement.
4
110
7.7 years ago
SALONI
Script
SQL Injection Demo
SQL Injection examples of a procedure vulnerable to statement modification and a procedure vulnera...
LNPLS
5
108
7.6 years ago
Sarah Hirschfeld (Oracle)
Script
CALCULADORA
CALCULADORA PL SQL
CALCULADORA
3
104
7.6 years ago
KAKAROTO
Script
Local Procedure
Local procedure to return multiple values i.e. addition, subtraction, multiplication and division of...
Local procedure
0
102
6.9 years ago
Jb
Script
SAVE EXCEPTIONS and FORALL - An Exploration
Add the SAVE EXCEPTIONS clause to your FORALL statement when you want the PL/SQL runtime engine to e...
FORALL,SAVE EXCEPTIONS,INDICES OF, forall_bulkexc
8
97
8.9 years ago
Steven Feuerstein
Script
Test Your PL/SQL Exception Handling Knowledge
Don't just run this script! First: look at each statement and ask yourself "What will I see on the s...
EXCEPTION HANDLING
4
95
8.9 years ago
Steven Feuerstein
Script
Generate XML with dbms_xmldom
Basic procedure on generating XML using the DBMS_XMLDOM API.
xml
5
92
8.2 years ago
trent
Script
UTL_CALL_STACK: Fine-grained execution call stack package (12.1)
UTL_CALL_STACK, introduced in Oracle Database 12c, offers fine grained information about your execut...
call stack
6
91
8.8 years ago
Steven Feuerstein
Script
Generate Named Exceptions
Simplified example to declare named exceptions and generate code to use them easily.
named_exceptions
5
89
9 years ago
Matzberger Marcus
Script
Binding Variables with EXECUTE IMMEDIATE of PL/SQL Block
When you execute a dynamic PL/SQL block dynamically, variables are bound to placeholders BY NAME, no...
DYNAMIC PLSQL
7
89
8.8 years ago
Steven Feuerstein
Script
Binding PLSQL Types in SQL (12.1 and Higher)
In 12.1, Oracle Database extended its support for PL/SQL-specific datatypes in SQL statements. Befor...
BINDING
9
82
9 years ago
Steven Feuerstein
Script
%ROWTYPE Variable Does Not Inherit Initial Values or Constraints
This creates a table with two columns, each with an initial value and a NOT NULL constraint. Then it...
LNPLS
1
82
7.6 years ago
Sarah Hirschfeld (Oracle)
Script
Use Compound Trigger to Simplify Mutating Table Error Resolution
The compound trigger, added in 11.1, allows you to define variables which persist through the execut...
COMPOUND,MUTATING TABLE
6
78
9 years ago
Steven Feuerstein
Script
Basic Error Logging Package
This very, VERY basic error logging package demonstrations the critical elements: use an autonomous ...
error,exception,log
14
77
8.6 years ago
Steven Feuerstein
Script
12.2: Find duplicate SQL statements with PL/Scope
PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statemen...
impact, change,duplicate
5
76
7.8 years ago
Steven Feuerstein
Script
Use EXCEPTION_INIT to Give Names to Un-named Oracle Errors
Oracle Database pre-defines a number of exceptions for common ORA errors, such as NO_DATA_FOUND and ...
exception_init,pragma
5
75
8.6 years ago
Steven Feuerstein
Script
Script to check naming standards for variables in PLSQL code
The PLSQL code checks the naming standards of one or more PLSQL packages. This can be used in PLSQL...
PLSQL naming standards
2
72
9 years ago
Binuraj Somanathan Nair (http://oracleappsdiary.blogspot.co.uk)
Script
PL/Scope Examples
PL/Scope is a powerful code analysis tool, built into PL/SQL. Before compiling your program units, t...
PL/SCOPE,CODE ANALYSIS
7
72
8.7 years ago
Steven Feuerstein
Script
PL/SQL variable scope
Demonstrating variables getting inherited in sub-units
2
72
8.6 years ago
trent
Script
Simple CONTINUE Example
CONTINUED was added in 11.1 to support a wider variety of behavior within loops, especially nested l...
LOOP, continue, pl/sql
1
70
9.1 years ago
Mike Hichwa (Oracle)
Script
Fetching Multiple Rows from Dynamic SELECT
This script shows you how to use both EXECUTE IMMEDIATE with BULK COLLECT and OPEN FOR with cursor v...
dynamic sql,bulk collect
6
69
8.6 years ago
Steven Feuerstein
Script
Associative Array Example
Demonstrates how to declare an associative array type, populate that array non-sequentially, and ite...
ASSOCIATIVE ARRAY SPARSE METHODS
3
68
9.1 years ago
Mike Hichwa (Oracle)
Script
If Exception Not Re-raised, No More Exception!
You have a subprogram that invokes another subprogram (or nested block). That "inner" subprogram fai...
sqlcode,error
2
65
8.6 years ago
Steven Feuerstein
Script
Object Type Hierarchies in PL/SQL
While PL/SQL is mostly used to provide secure, efficient access to the relational tables (SQL), you ...
object type,hierarchy,inheritance
11
63
8.7 years ago
Steven Feuerstein
Script
SQL on Collections
"Hi Steven, what is the best way to perform operations like SELECT a, SUM(b) from t GROUP BY a that ...
table operator
4
61
8.8 years ago
Steven Feuerstein
Script
Raising Exceptions in PL/SQL
An exploration into RAISE and RAISE_APPLICATION_ERROR, as a complement to Steven's blog post: http:/...
exception,raise,raise_application_error
8
61
8.2 years ago
Steven Feuerstein
Script
PLSQL Puzzle
Can you come up with just ONE STATEMENT to add to plsqlpuzzle_proc so that it can execute without te...
1
60
5 years ago
Steven Feuerstein
Script
Using Dynamic SQL for Multi-row Queries
This script contains the code for my Oracle Magazine article of the same name.
dynamic sql
10
59
7.2 years ago
Steven Feuerstein
row(s) 1 - 60 of 484
Next