Oracle PL / SQL examples (example source code) Organized by topic






  1. Oracle PL / SQL

Select Query /

alias column 8ALL 1
AND 5ANY 1
Arithmetic Operators Query 4AS 2
BETWEEN AND 10Calculation in Query 6
Case Query 19Char Case 1
Char Compare 2Column alias 4
Column Concatenation 5Column Names 9
Comparison Operators 6Date Add 1
Date Calculation Query 9Derive values 1
DISTINCT 9EXISTS 4
GROUP BY 17HAVING 14
Like 19Logical Operators 4
Nesting Functions 2NOT 10
Null Value Calculation 2Null Value Compare 10
Null Value Concatanation 2Number Format 1
OR 9Order By 26
Query Date Compare 3Query IN 13
Query Select Columns 7Quote 5
rowid 2ROWNUM 10
Select 18UNIQUE 1
Where 12Wild Card 4
With 2

Insert Delete Update /

Delete Subquery 1DELETE WHERE 6
INSERT Column Name 8INSERT with Functions 4
Insert with subquery 8Update All 2
UPDATE Set Clause 9Update subquery 3
UPDATE WHERE 5

Subquery /

Correlated Subquery 9Exists Subquery 5
Subquery ALL 4Subquery ANY 4
Subquery Basics 36Subquery IN 10
update 3Virtual Table 3

Hierarchical Query /

Indent 2Level 7
Nodes Branches 2Root 2
SIBLINGS 4Start With Connect 3
sys_connect_by_path 6

Table /

Add Column 13Alter Table 16
Change Column Size 2Change Column Type 1
chartorowid 1Cluster 8
Column Default Value 8Comment Table 5
Copy Table 13Create Table 25
CSV Load 14CSV Table 5
Database 3Describe 4
Drop Columns 3Drop Table 4
DUAL Table 3External Table 1
Merge 4Rename Column 1
Rename Table 1Row Count 1
ROWID 2ROWNUM 5
SAVEPOINT 2SYNONYM 1
Table Reference 2Table space 12
Table Statistics 1Temporary Table 17
Transaction Rollback Commit 11TRUNCATE TABLE 2

Table Joins /

ANSI SQL 92 Joins 5Cartesian product 3
Cross joins 1Equijoins 5
Full Outer Join 2Inner Joins 8
Left Outer Join 6Natural join 3
Outer Joins 7Right Outer Joins 6
Self Joins 2Table Join Basics 15
Table Join Column 10

View /

Alter View 2Create View 12
Inline View 1MATERIALIZED_VIEW 3
Query View 2Updatable View 1
Update View 1

Aggregate Functions /

Aggregate Functions Basics 8AVG 15
COUNT 22GROUP Function 3
MAX 10MIN 8
SUM 14

Char Functions /

ASCII 3ASCIISTR 2
Char Length 1CHR 2
COMPOSE 2CONCAT 2
CONTAINS 4Convert 1
decompose 1Dump 7
I18N 2INITCAP 7
INSTR 12LENGTH 4
LOWER 4LPAD 7
LTRIM 7NVL 14
NVL2 4REPLACE 10
RPAD 6RTRIM 4
SOUNDEX 4String Concatenate 1
SUBSTR 10Translate 10
TRIM 11UNISTR 3
UPPER 3VSIZE 3

Numeric Math Functions /

ABS 8ACOS 3
ASIN 3ATAN 2
ATAN2 2BIN_TO_NUM 4
BITAND 5CEIL 6
COS 4COSH 3
EXP 4FLOOR 4
greatest 6hextoraw 1
least 4LN 2
LOG 4MOD 8
POWER 7ROUND 19
SIGN 8SIN 3
SINH 2SQRT 5
TAN 3TANH 3
TRUNC 13

Analytical Functions /

Aggregrate Analytical 21Analytical Functions Basics 12
Centered Average 1Compute 3
connect by 3Corr 1
COVAR_POP 1COVAR_SAMP 1
Cube 10CUME_DIST 3
Cumulative Sum 8CURRENTV 2
DENSE_RANK 13First Last Value 11
First Last 1GROUPING SETS 2
GROUPING 7GROUPING_ID 4
IGNORE NAV KEEP NAV 1IS PRESENT 1
LAG LEAD 10Measure 3
MEDIAN 2Model 2
Moving Average 1NTILE 8
NULLs First 3NULLs Last 5
Over 11PARTITION 14
percentile_cont 1PERCENTILE_DISC 1
PERCENT_RANK 4PRESENTV 1
Range 3Rank Row 1
Rank 12Ratio Portion 1
RATIO_TO_REPORT 6REGR_AVGX 2
REGR_AVGY 1REGR_COUNT 2
REGR_INTERCEPT 2REGR_R2 2
REGR_SLOPE 2REGR_SXX 2
REGR_SXY 2REGR_SYY 3
Rollup 12ROW_NUMBER 11
RULES UPDATE 1Start With 3
STATS_MODE 1STATS_WSR_TEST 3
STDDEV 2STDDEV_POP 1
STDDEV_SAMP 1Sum 5
Top Values 1UNBOUNDED FOLLOWING 1
VARIANCE 2VAR_POP 1
VAR_SAMP 1

PL SQL /

APPEND 1Arithmetic Operators 1
Begin End Block 15Between and 4
Boolean Expression 4Break 2
CARDINALITY 3Case 10
Char to Number 1Char Varchar Compare 5
Column Type Variable 2Comments 3
Compile Error 10Concatenation Operator 1
Conditional_compilation 1Console Prompt Read 4
Constant Number 5Data Insert 14
Data Type Convert 10Data Type Define 2
Date Calculation 2DATE Compare 2
Date Convert 2DBMS_OUTPUT 4
Decode 6Default Value 3
Define 3Delete Data 8
Dump 1Errors Table 1
Exception Handle 24Exception Range 5
Execute Immediate 24EXIT 9
For Loop 27FORALL 5
GOTO 6Handle Exception 5
IF 20IN 5
Index by Table 5Insert Data 4
IS 6Label 6
LIKE 2Loop 7
MEMBER OF 3Multi line Command 1
MULTISET 7Not Null Variable 2
NULL Statement 3Null Value 3
Number to Char 4Object Type 5
Operator 1Output Print 2
Package 30Pause 1
Predefined Exceptions 6Print 1
Program Block 4Prompt 1
Raise 10Record Data Type 14
Repeat Until 2RETURNING Into 2
rowtype 13Select Into 26
Server Output 1Set 2
SQL FOUND 1SQL ISOPEN 1
SQL ROWCOUNT 1sqlcode 3
SQLERRM 8SUBMULTISET 3
subtype 2Table of Char 5
Table of Column 1Table of Date 1
Table of number 26Table of Object 1
Table of rowtype 5Table of Type 18
Table of Varchar2 28Timing PL SQL 2
Transaction 9Type 8
Update Data 17Utility Package 7
Varchar Table 4Variable Declare 6
Variable Default Value 2Variable init 4
Variable Scope 9Variable 4
varray 41When 3
While Loop 17Your own Exception 7

Data Type /

bfile 2Binary Data Type 5
blob 5Boolean 7
Cast 1CHAR 13
Clob 23Date 30
EXCEPTION 2httpuritype 3
Integer 4INTERVAL 6
IS INFINITE 1IS NAN 1
IS NULL Value 2LOB 1
Long 7National Char 2
nclob 2NULL 3
Number Type 24Numeric Formatting 19
PLS_INTEGER 8TIMESTAMP 22
TO_BINARY_DOUBLE 1TO_BINARY_FLOAT 1
TO_Char Date 16TO_CHAR Number 6
TO_CLOB 2TO_Date 6
TO_MULTI_BYTE 1TO_NCHAR 1
TO_NCLOB 1TO_NUMBER 4
TO_SINGLE_BYTE 1VARCHAR2 20

Cursor /

Close Cursor 7Cursor Fetch 20
CURSOR FOR Loop 11Cursor FOUND 6
Cursor ISOPEN 2Cursor Not Found 11
Cursor Open 6Cursor parameters 8
Cursor ROWCOUNT 7Cursor Update 3
Cursor Value 13Explicit Cursor 12
Implicit Cursor 14Nested Cursor 2
ref cursor 5Reference Cursor 7
sys_refcursor 4

Result Set /

Intersect 6Loop Result set 1
Minus 8Union all 5
UNION 13

Regular Expressions /

alnum 1Alpha 3
Brackets 4Case 3
Control Letter 1Digit 4
Escape 1Group 5
NOT 4OR 2
Punct Print 2Range 1
REGEXP_INSTR 16REGEXP_LIKE 5
REGEXP_REPLACE 3REGEXP_SUBSTR 3
Repeat Operator 4Space 1
Wild Card 15Word Start End 4

Date Timezone /

ADD_MONTHS 7CURRENT_DATE 1
CURRENT_TIMESTAMP 4Date Default Format 7
Date Format 19Date Time Interval 2
Day Calculation 1DBTIMEZONE 2
EXTRACT 10FROM_TZ 1
INTERVAL 6LAST_DAY 8
LOCAL TIME ZONE 2LOCALTIMESTAMP 1
MONTHS_BETWEEN 11NEW_TIME 3
NEXT_DAY 7NLS_DATE_FORMAT 1
numtodsinterval 8numtoyminterval 5
ROUND Date 7SESSIONTIMEZONE 3
SYSDATE 11SYSTIMESTAMP 4
Time Zone 7Timestamp 12
TO_DATE 5TO_DSINTERVAL 3
TO_TIMESTAMP 5TO_TIMESTAMP_TZ 2
TO_YMINTERVAL 5TRUNC Date 10
TZ_OFFSET 2

Sequence /

Alter Sequence 1Create Sequence 5
Drop Sequence 1Sequence Increment 2
Sequence Properties 3Sequence Start 1
Sequence Value 9

Index /

Alter Index 1Create Index 19
Drop Index 1Index STATISTICS 4
Rename Index 1

Constraints /

Add Check Constraint 1Add Constraint 4
Add Foreign Key 6Add Primary Key 11
Alter Constraints 1Constraint Check 21
Constraint Types 2Contraint Name 3
Create Foreign Key 6Create Primary Key 7
Defer Constraint 2DELETE CASCADE 1
DELETE SET NULL 2Drop Cascade 2
Drop Constraint 3Drop Foreign Key 3
Drop Primary Key 1Enable Disable Primary Key 2
Foreign Key 2Name for Primary Key 1
NOT NULL 8Primary Key 4
Unique 9

Stored Procedure Function /

Autonomous_transaction 4Call 6
Default Parameter Value 4exec 3
Function Call 8Function Definition 16
Function Parameters 12Function Return 15
Insert 7NOCOPY 2
Overload 2Parameter IN 5
Parameter OUT 11PIPELINED 3
Procedure Call 13Procedure Definition 21
Procedure Function Body 2Procedure Parameters 21
Reference Your Function 1Show Error 2
Utility Function 8Utility Procedure 9

Trigger /

After Delete Trigger 2After Insert Trigger 4
After Update Trigger 2Audit Log Table 8
Before Delete Trigger 1Before Insert Trigger 4
Before Trigger 3Before Update Trigger 3
Business Logic Trigger 6Create Trigger 16
Database Trigger 11Disable Trigger 5
Drop Trigger 1Enable Trigger 3
Log Trigger 2Old New Value 8
Schema Trigger 4Trigger Error 2
Trigger Firing 2Trigger on View 2
Triggerable Actions 2

Object Oriented Database /

Alter Type 3Attribute 2
Cast 2Comparable 5
Constructor 7Cursor 2
Delete 1DEREF 1
final 1Insert 11
NOT INSTANTIABLE 1Object Column 11
Object Method 11Object Table Insert 3
Object Table 19Object View 2
Object 18Ref 6
Reference Object 3Select 12
Self 2table function 2
Table 4treat 2
Type Body 8Type Combination 3
Type Hierarchy 5Update 7
Value Function 1value 1
Varray 2Where 1

Large Objects /

bfile 7blob 1
Directory 2EMPTY_BLOB 1
empty_clob 1HEXTORAW 2
Insert 2Update 1

Conversion Functions /

CAST 5chartorowid 1
COALESCE 3CONVERT 1
DECODE 18NULLIF 3
ROWIDTOCHAR 2TO_CHAR 10
TO_Number 5

User Previliege /

Alter User 3AUDIT 2
Column Privileges 1Create User 4
Current_User 4Drop User 3
Grant Permission 11Lock Unlock Account 2
outline 4PROFILE 1
Revok Roles 1Revoke Permission 3
REVOKE REFERENCES 1Role 10
schema 1Sequence privileges 1
SESSION privileges 3Synonym 8
System Privileges 3Table privileges 5
UID 1User Password 8
View privileges 1

System Tables Views /

all_arguments 1ALL_CATALOG 1
all_col_privs 1all_indexes 1
all_ind_columns 1all_objects 2
ALL_SEQUENCES 1ALL_SYNONYMS 1
ALL_TABLES 2all_tab_columns 2
all_tab_privs 1ALL_TRIGGERS 1
all_users 2CAT 2
DBA_AUDIT_OBJECT 6DBA_AUDIT_SESSION 5
dba_coll_types 1dba_col_privs 1
DBA_DATA_FILES 2dba_ddl_locks 1
dba_dependencies 2DBA_EXTENTS 4
dba_external_tables 1DBA_INDEXES 2
dba_job 1DBA_JOBS_RUNNING 1
dba_lobs 1dba_locks 2
dba_method_result 1dba_objects 6
DBA_OBJECT_SIZE 1dba_object_tables 1
dba_outlines 1dba_procedure 1
dba_profiles 1dba_resumable 1
dba_roles 1dba_role_privs 4
dba_rsrc_consumer_groups 1dba_rsrc_plan_directives 1
dba_source 1DBA_SYNONYMS 1
dba_sys_privs 3DBA_Tables 5
dba_tablespaces 1DBA_TAB_COLUMNS 2
dba_tab_comments 1dba_tab_privs 3
DBA_TAB_SUBPARTITIONS 1dba_triggers 1
dba_types 1dba_type_attributes 1
dba_type_method 1dba_updatable_columns 1
dba_users 7dba_views 1
db_object_cache 1dict 1
dictionary 1global_name 2
obj 1open_cursor 1
option 1parameter 5
pgastat 1process 3
ROLE_SYS_PRIVS 1ROLE_TAB_PRIVS 2
RSRC_CONSUMER_GROUP 1session 3
session_event 1session_role 1
sgastat 1sql 2
sqltext 1sys.aux_stats 1
sys.col 4SYS_EXTRACT_UTC 1
sys_nc_rowinfo 1tab 1
timezone_names 1USER_CATALOG 2
user_col_comments 1USER_COL_PRIVS_MADE 1
USER_COL_PRIVS_RECD 1USER_CONSTRAINTS 4
user_cons_columns 2user_dependencies 5
USER_ERRORS 3user_indexes 2
user_ind_columns 4USER_JOBS 3
USER_OBJECTS 23user_object_size 1
user_outlines 1user_outline_hints 2
USER_ROLE_PRIVS 2user_segments 3
USER_SEQUENCES 2user_source 3
USER_SYNONYMS 1USER_SYS_PRIVS 1
USER_TABLES 9USER_TAB_COLUMNS 1
USER_TAB_COMMENTS 1user_tab_privs 1
USER_TAB_PRIVS_MADE 2USER_TAB_PRIVS_RECD 1
user_triggers 5user_types 1
user_unused_col_tabs 1USER_UPDATABLE_COLUMNS 1
user_users 1USER_VIEWS 5
version 1v_buffer_pool 1
V_Controlfile 1V_DATAFILE 1
V_dbfile 1V_db_cache_advice 1
V_FIXED_TABLE 1V_instance 4
V_lock 1V_logfile 1
V_log_history 1V_nls_parameters 1
V_Object_Usage 1V_Parameter 5
V_pga_target_Advice 1V_Rollname 2
V_SESSION 3V_SGA 1
v_sgastat 2V_sql 2
V_sqlarea 1V_statistics_level 1
V_statname 3v_Sysstat 1
V_Waitstat 1

System Packages /

DBMS_ALERT 6dbms_application_info 4
DBMS_AQADM 3dbms_crypto 8
DBMS_DB 1dbms_ddl 1
DBMS_DEBUG 1DBMS_FGA 1
DBMS_FILE_TRANSFER 1dbms_flashback 3
dbms_job 8dbms_lob 17
dbms_lock 1DBMS_METADATA 2
DBMS_OBFUSCATION_TOOLKIT 6dbms_output 16
DBMS_PIPE 13DBMS_RANDOM 12
DBMS_REDEFINITION 1DBMS_REPAIR 4
dbms_rowid 7dbms_scheduler 1
DBMS_SESSION 1dbms_space 1
dbms_sql 27dbms_stats 12
DBMS_TRACE 2dbms_utility 15
DBMS_WARNING 2dbms_xmlquery 1
dbms_xmlschema 2htp 1
ORA Error 13ORA 18
outln_pkg 1TEXT_IO 1
UTL_COMPRESS 1UTL_FILE 9
UTL_HTTP 1utl_i18n 1
UTL_INADDR 1UTL_MAIL 1
utl_raw 27UTL_SMTP 1
UTL_TCP 1

Report Column Page /

COLSEP 1Column Format Clear 1
Column Heading Name 1Column Heading Size 1
Column Length 1Column Number Format 7
Column Word Wrap 1Heading 1
Line size 2Line Width 1
Page Size 2

SQL Plus /

Accept 7all_policies 1
Analyze 24autotrace 23
break 2Column 25
compute 1define 3
Edit 1editor 2
Environment Variables 2explain plan 4
Feedback 2flashback 1
Get 1linesize 1
Login 2long 2
markup html 4numformat 1
pagesize 4pause 5
Prompt 4Query Rewrite 1
Quit 1recyclebin 2
Run 1Save 3
SERVEROUTPUT 6Session variable 43
SGA 1show 3
Skip 1space 1
SPOOL 5Start 2
sys_context 3Timing 11
Title 3USERENV 2
variable 9

XML /

ExistsNode 4UPDATEXML 5
xdburitype 1XML Save 1
xmlattributes 2XMLElement 2
xmlforest 1XMLType 18
xpath 8