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

  1. Oracle PL / SQL




Select Query /

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

Insert Delete Update /

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

Subquery /

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

Hierarchical Query /

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

Table /

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

Table Joins /

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

View /

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

Aggregate Functions /

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

Char Functions /

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

Numeric Math Functions /

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

Analytical Functions /

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

PL SQL /

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

Data Type /

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

Cursor /

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

Result Set /

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

Regular Expressions /

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

Date Timezone /

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

Sequence /

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

Index /

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

Constraints /

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

Stored Procedure Function /

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

Trigger /

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

Object Oriented Database /

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

Large Objects /

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

Conversion Functions /

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

User Previliege /

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

System Tables Views /

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

System Packages /

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

Report Column Page /

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

SQL Plus /

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

XML /

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