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

Oracle PL / SQL

Select Query /

alias column 8 ALL 1

AND 5 ANY 1

Arithmetic Operators Query 4 AS 2

BETWEEN AND 10 Calculation in Query 6

Case Query 19 Char Case 1

Char Compare 2 Column alias 4

Column Concatenation 5 Column Names 9

Comparison Operators 6 Date Add 1

Date Calculation Query 9 Derive values 1

DISTINCT 9 EXISTS 4

GROUP BY 17 HAVING 14

Like 19 Logical Operators 4

Nesting Functions 2 NOT 10

Null Value Calculation 2 Null Value Compare 10

Null Value Concatanation 2 Number Format 1

OR 9 Order By 26

Query Date Compare 3 Query IN 13

Query Select Columns 7 Quote 5

rowid 2 ROWNUM 10

Select 18 UNIQUE 1

Where 12 Wild Card 4

With 2

Insert Delete Update /

Delete Subquery 1 DELETE WHERE 6

INSERT Column Name 8 INSERT with Functions 4

Insert with subquery 8 Update All 2

UPDATE Set Clause 9 Update subquery 3

UPDATE WHERE 5

Subquery /

Correlated Subquery 9 Exists Subquery 5

Subquery ALL 4 Subquery ANY 4

Subquery Basics 36 Subquery IN 10

update 3 Virtual Table 3

Hierarchical Query /

Indent 2 Level 7

Nodes Branches 2 Root 2

SIBLINGS 4 Start With Connect 3

sys_connect_by_path 6

Table /

Add Column 13 Alter Table 16

Change Column Size 2 Change Column Type 1

chartorowid 1 Cluster 8

Column Default Value 8 Comment Table 5

Copy Table 13 Create Table 25

CSV Load 14 CSV Table 5

Database 3 Describe 4

Drop Columns 3 Drop Table 4

DUAL Table 3 External Table 1

Merge 4 Rename Column 1

Rename Table 1 Row Count 1

ROWID 2 ROWNUM 5

SAVEPOINT 2 SYNONYM 1

Table Reference 2 Table space 12

Table Statistics 1 Temporary Table 17

Transaction Rollback Commit 11 TRUNCATE TABLE 2

Table Joins /

ANSI SQL 92 Joins 5 Cartesian product 3

Cross joins 1 Equijoins 5

Full Outer Join 2 Inner Joins 8

Left Outer Join 6 Natural join 3

Outer Joins 7 Right Outer Joins 6

Self Joins 2 Table Join Basics 15

Table Join Column 10

View /

Alter View 2 Create View 12

Inline View 1 MATERIALIZED_VIEW 3

Query View 2 Updatable View 1

Update View 1

Aggregate Functions /

Aggregate Functions Basics 8 AVG 15

COUNT 22 GROUP Function 3

MAX 10 MIN 8

SUM 14

Char Functions /

ASCII 3 ASCIISTR 2

Char Length 1 CHR 2

COMPOSE 2 CONCAT 2

CONTAINS 4 Convert 1

decompose 1 Dump 7

I18N 2 INITCAP 7

INSTR 12 LENGTH 4

LOWER 4 LPAD 7

LTRIM 7 NVL 14

NVL2 4 REPLACE 10

RPAD 6 RTRIM 4

SOUNDEX 4 String Concatenate 1

SUBSTR 10 Translate 10

TRIM 11 UNISTR 3

UPPER 3 VSIZE 3

Numeric Math Functions /

ABS 8 ACOS 3

ASIN 3 ATAN 2

ATAN2 2 BIN_TO_NUM 4

BITAND 5 CEIL 6

COS 4 COSH 3

EXP 4 FLOOR 4

greatest 6 hextoraw 1

least 4 LN 2

LOG 4 MOD 8

POWER 7 ROUND 19

SIGN 8 SIN 3

SINH 2 SQRT 5

TAN 3 TANH 3

TRUNC 13

Analytical Functions /

Aggregrate Analytical 21 Analytical Functions Basics 12

Centered Average 1 Compute 3

connect by 3 Corr 1

COVAR_POP 1 COVAR_SAMP 1

Cube 10 CUME_DIST 3

Cumulative Sum 8 CURRENTV 2

DENSE_RANK 13 First Last Value 11

First Last 1 GROUPING SETS 2

GROUPING 7 GROUPING_ID 4

IGNORE NAV KEEP NAV 1 IS PRESENT 1

LAG LEAD 10 Measure 3

MEDIAN 2 Model 2

Moving Average 1 NTILE 8

NULLs First 3 NULLs Last 5

Over 11 PARTITION 14

percentile_cont 1 PERCENTILE_DISC 1

PERCENT_RANK 4 PRESENTV 1

Range 3 Rank Row 1

Rank 12 Ratio Portion 1

RATIO_TO_REPORT 6 REGR_AVGX 2

REGR_AVGY 1 REGR_COUNT 2

REGR_INTERCEPT 2 REGR_R2 2

REGR_SLOPE 2 REGR_SXX 2

REGR_SXY 2 REGR_SYY 3

Rollup 12 ROW_NUMBER 11

RULES UPDATE 1 Start With 3

STATS_MODE 1 STATS_WSR_TEST 3

STDDEV 2 STDDEV_POP 1

STDDEV_SAMP 1 Sum 5

Top Values 1 UNBOUNDED FOLLOWING 1

VARIANCE 2 VAR_POP 1

PL SQL /

APPEND 1 Arithmetic Operators 1

Begin End Block 15 Between and 4

Boolean Expression 4 Break 2

CARDINALITY 3 Case 10

Char to Number 1 Char Varchar Compare 5

Column Type Variable 2 Comments 3

Compile Error 10 Concatenation Operator 1

Conditional_compilation 1 Console Prompt Read 4

Constant Number 5 Data Insert 14

Data Type Convert 10 Data Type Define 2

Date Calculation 2 DATE Compare 2

Date Convert 2 DBMS_OUTPUT 4

Decode 6 Default Value 3

Define 3 Delete Data 8

Dump 1 Errors Table 1

Exception Handle 24 Exception Range 5

Execute Immediate 24 EXIT 9

For Loop 27 FORALL 5

GOTO 6 Handle Exception 5

IF 20 IN 5

Index by Table 5 Insert Data 4

IS 6 Label 6

LIKE 2 Loop 7

MEMBER OF 3 Multi line Command 1

MULTISET 7 Not Null Variable 2

NULL Statement 3 Null Value 3

Number to Char 4 Object Type 5

Operator 1 Output Print 2

Package 30 Pause 1

Predefined Exceptions 6 Print 1

Program Block 4 Prompt 1

Raise 10 Record Data Type 14

Repeat Until 2 RETURNING Into 2

rowtype 13 Select Into 26

Server Output 1 Set 2

SQL FOUND 1 SQL ISOPEN 1

SQL ROWCOUNT 1 sqlcode 3

SQLERRM 8 SUBMULTISET 3

subtype 2 Table of Char 5

Table of Column 1 Table of Date 1

Table of number 26 Table of Object 1

Table of rowtype 5 Table of Type 18

Table of Varchar2 28 Timing PL SQL 2

Transaction 9 Type 8

Update Data 17 Utility Package 7

Varchar Table 4 Variable Declare 6

Variable Default Value 2 Variable init 4

Variable Scope 9 Variable 4

varray 41 When 3

While Loop 17 Your own Exception 7

Data Type /

bfile 2 Binary Data Type 5

blob 5 Boolean 7

Cast 1 CHAR 13

Clob 23 Date 30

EXCEPTION 2 httpuritype 3

Integer 4 INTERVAL 6

IS INFINITE 1 IS NAN 1

IS NULL Value 2 LOB 1

Long 7 National Char 2

nclob 2 NULL 3

Number Type 24 Numeric Formatting 19

PLS_INTEGER 8 TIMESTAMP 22

TO_BINARY_DOUBLE 1 TO_BINARY_FLOAT 1

TO_Char Date 16 TO_CHAR Number 6

TO_CLOB 2 TO_Date 6

TO_MULTI_BYTE 1 TO_NCHAR 1

TO_NCLOB 1 TO_NUMBER 4

TO_SINGLE_BYTE 1 VARCHAR2 20

Cursor /

Close Cursor 7 Cursor Fetch 20

CURSOR FOR Loop 11 Cursor FOUND 6

Cursor ISOPEN 2 Cursor Not Found 11

Cursor Open 6 Cursor parameters 8

Cursor ROWCOUNT 7 Cursor Update 3

Cursor Value 13 Explicit Cursor 12

Implicit Cursor 14 Nested Cursor 2

ref cursor 5 Reference Cursor 7

sys_refcursor 4

Result Set /

Intersect 6 Loop Result set 1

Minus 8 Union all 5

UNION 13

Regular Expressions /

alnum 1 Alpha 3

Brackets 4 Case 3

Control Letter 1 Digit 4

Escape 1 Group 5

NOT 4 OR 2

Punct Print 2 Range 1

REGEXP_INSTR 16 REGEXP_LIKE 5

REGEXP_REPLACE 3 REGEXP_SUBSTR 3

Repeat Operator 4 Space 1

Wild Card 15 Word Start End 4

Date Timezone /

ADD_MONTHS 7 CURRENT_DATE 1

CURRENT_TIMESTAMP 4 Date Default Format 7

Date Format 19 Date Time Interval 2

Day Calculation 1 DBTIMEZONE 2

EXTRACT 10 FROM_TZ 1

INTERVAL 6 LAST_DAY 8

LOCAL TIME ZONE 2 LOCALTIMESTAMP 1

MONTHS_BETWEEN 11 NEW_TIME 3

NEXT_DAY 7 NLS_DATE_FORMAT 1

numtodsinterval 8 numtoyminterval 5

ROUND Date 7 SESSIONTIMEZONE 3

SYSDATE 11 SYSTIMESTAMP 4

Time Zone 7 Timestamp 12

TO_DATE 5 TO_DSINTERVAL 3

TO_TIMESTAMP 5 TO_TIMESTAMP_TZ 2

TO_YMINTERVAL 5 TRUNC Date 10

Sequence /

Alter Sequence 1 Create Sequence 5

Drop Sequence 1 Sequence Increment 2

Sequence Properties 3 Sequence Start 1

Sequence Value 9

Index /

Alter Index 1 Create Index 19

Drop Index 1 Index STATISTICS 4

Rename Index 1

Constraints /

Add Check Constraint 1 Add Constraint 4

Add Foreign Key 6 Add Primary Key 11

Alter Constraints 1 Constraint Check 21

Constraint Types 2 Contraint Name 3

Create Foreign Key 6 Create Primary Key 7

Defer Constraint 2 DELETE CASCADE 1

DELETE SET NULL 2 Drop Cascade 2

Drop Constraint 3 Drop Foreign Key 3

Drop Primary Key 1 Enable Disable Primary Key 2

Foreign Key 2 Name for Primary Key 1

NOT NULL 8 Primary Key 4

Unique 9

Stored Procedure Function /

Autonomous_transaction 4 Call 6

Default Parameter Value 4 exec 3

Function Call 8 Function Definition 16

Function Parameters 12 Function Return 15

Insert 7 NOCOPY 2

Overload 2 Parameter IN 5

Parameter OUT 11 PIPELINED 3

Procedure Call 13 Procedure Definition 21

Procedure Function Body 2 Procedure Parameters 21

Reference Your Function 1 Show Error 2

Utility Function 8 Utility Procedure 9

Trigger /

After Delete Trigger 2 After Insert Trigger 4

After Update Trigger 2 Audit Log Table 8

Before Delete Trigger 1 Before Insert Trigger 4

Before Trigger 3 Before Update Trigger 3

Business Logic Trigger 6 Create Trigger 16

Database Trigger 11 Disable Trigger 5

Drop Trigger 1 Enable Trigger 3

Log Trigger 2 Old New Value 8

Schema Trigger 4 Trigger Error 2

Trigger Firing 2 Trigger on View 2

Triggerable Actions 2

Object Oriented Database /

Alter Type 3 Attribute 2

Cast 2 Comparable 5

Constructor 7 Cursor 2

Delete 1 DEREF 1

final 1 Insert 11

NOT INSTANTIABLE 1 Object Column 11

Object Method 11 Object Table Insert 3

Object Table 19 Object View 2

Object 18 Ref 6

Reference Object 3 Select 12

Self 2 table function 2

Table 4 treat 2

Type Body 8 Type Combination 3

Type Hierarchy 5 Update 7

Value Function 1 value 1

Varray 2 Where 1

Large Objects /

bfile 7 blob 1

Directory 2 EMPTY_BLOB 1

empty_clob 1 HEXTORAW 2

Insert 2 Update 1

Conversion Functions /

CAST 5 chartorowid 1

COALESCE 3 CONVERT 1

DECODE 18 NULLIF 3

ROWIDTOCHAR 2 TO_CHAR 10

User Previliege /

Alter User 3 AUDIT 2

Column Privileges 1 Create User 4

Current_User 4 Drop User 3

Grant Permission 11 Lock Unlock Account 2

outline 4 PROFILE 1

Revok Roles 1 Revoke Permission 3

REVOKE REFERENCES 1 Role 10

schema 1 Sequence privileges 1

SESSION privileges 3 Synonym 8

System Privileges 3 Table privileges 5

UID 1 User Password 8

View privileges 1

System Tables Views /

all_arguments 1 ALL_CATALOG 1

all_col_privs 1 all_indexes 1

all_ind_columns 1 all_objects 2

ALL_SEQUENCES 1 ALL_SYNONYMS 1

ALL_TABLES 2 all_tab_columns 2

all_tab_privs 1 ALL_TRIGGERS 1

all_users 2 CAT 2

DBA_AUDIT_OBJECT 6 DBA_AUDIT_SESSION 5

dba_coll_types 1 dba_col_privs 1

DBA_DATA_FILES 2 dba_ddl_locks 1

dba_dependencies 2 DBA_EXTENTS 4

dba_external_tables 1 DBA_INDEXES 2

dba_job 1 DBA_JOBS_RUNNING 1

dba_lobs 1 dba_locks 2

dba_method_result 1 dba_objects 6

DBA_OBJECT_SIZE 1 dba_object_tables 1

dba_outlines 1 dba_procedure 1

dba_profiles 1 dba_resumable 1

dba_roles 1 dba_role_privs 4

dba_rsrc_consumer_groups 1 dba_rsrc_plan_directives 1

dba_source 1 DBA_SYNONYMS 1

dba_sys_privs 3 DBA_Tables 5

dba_tablespaces 1 DBA_TAB_COLUMNS 2

dba_tab_comments 1 dba_tab_privs 3

DBA_TAB_SUBPARTITIONS 1 dba_triggers 1

dba_types 1 dba_type_attributes 1

dba_type_method 1 dba_updatable_columns 1

dba_users 7 dba_views 1

db_object_cache 1 dict 1

dictionary 1 global_name 2

obj 1 open_cursor 1

option 1 parameter 5

pgastat 1 process 3

ROLE_SYS_PRIVS 1 ROLE_TAB_PRIVS 2

RSRC_CONSUMER_GROUP 1 session 3

session_event 1 session_role 1

sgastat 1 sql 2

sqltext 1 sys.aux_stats 1

sys.col 4 SYS_EXTRACT_UTC 1

sys_nc_rowinfo 1 tab 1

timezone_names 1 USER_CATALOG 2

user_col_comments 1 USER_COL_PRIVS_MADE 1

USER_COL_PRIVS_RECD 1 USER_CONSTRAINTS 4

user_cons_columns 2 user_dependencies 5

USER_ERRORS 3 user_indexes 2

user_ind_columns 4 USER_JOBS 3

USER_OBJECTS 23 user_object_size 1

user_outlines 1 user_outline_hints 2

USER_ROLE_PRIVS 2 user_segments 3

USER_SEQUENCES 2 user_source 3

USER_SYNONYMS 1 USER_SYS_PRIVS 1

USER_TABLES 9 USER_TAB_COLUMNS 1

USER_TAB_COMMENTS 1 user_tab_privs 1

USER_TAB_PRIVS_MADE 2 USER_TAB_PRIVS_RECD 1

user_triggers 5 user_types 1

user_unused_col_tabs 1 USER_UPDATABLE_COLUMNS 1

user_users 1 USER_VIEWS 5

version 1 v_buffer_pool 1

V_Controlfile 1 V_DATAFILE 1

V_dbfile 1 V_db_cache_advice 1

V_FIXED_TABLE 1 V_instance 4

V_lock 1 V_logfile 1

V_log_history 1 V_nls_parameters 1

V_Object_Usage 1 V_Parameter 5

V_pga_target_Advice 1 V_Rollname 2

V_SESSION 3 V_SGA 1

v_sgastat 2 V_sql 2

V_sqlarea 1 V_statistics_level 1

V_statname 3 v_Sysstat 1

System Packages /

DBMS_ALERT 6 dbms_application_info 4

DBMS_AQADM 3 dbms_crypto 8

DBMS_DB 1 dbms_ddl 1

DBMS_DEBUG 1 DBMS_FGA 1

DBMS_FILE_TRANSFER 1 dbms_flashback 3

dbms_job 8 dbms_lob 17

dbms_lock 1 DBMS_METADATA 2

DBMS_OBFUSCATION_TOOLKIT 6 dbms_output 16

DBMS_PIPE 13 DBMS_RANDOM 12

DBMS_REDEFINITION 1 DBMS_REPAIR 4

dbms_rowid 7 dbms_scheduler 1

DBMS_SESSION 1 dbms_space 1

dbms_sql 27 dbms_stats 12

DBMS_TRACE 2 dbms_utility 15

DBMS_WARNING 2 dbms_xmlquery 1

dbms_xmlschema 2 htp 1

ORA Error 13 ORA 18

outln_pkg 1 TEXT_IO 1

UTL_COMPRESS 1 UTL_FILE 9

UTL_HTTP 1 utl_i18n 1

UTL_INADDR 1 UTL_MAIL 1

utl_raw 27 UTL_SMTP 1

Report Column Page /

COLSEP 1 Column Format Clear 1

Column Heading Name 1 Column Heading Size 1

Column Length 1 Column Number Format 7

Column Word Wrap 1 Heading 1

Line size 2 Line Width 1

SQL Plus /

Accept 7 all_policies 1

Analyze 24 autotrace 23

break 2 Column 25

compute 1 define 3

Edit 1 editor 2

Environment Variables 2 explain plan 4

Feedback 2 flashback 1

Get 1 linesize 1

Login 2 long 2

markup html 4 numformat 1

pagesize 4 pause 5

Prompt 4 Query Rewrite 1

Quit 1 recyclebin 2

Run 1 Save 3

SERVEROUTPUT 6 Session variable 43

SGA 1 show 3

Skip 1 space 1

SPOOL 5 Start 2

sys_context 3 Timing 11

Title 3 USERENV 2

XML /

ExistsNode 4 UPDATEXML 5

xdburitype 1 XML Save 1

xmlattributes 2 XMLElement 2

xmlforest 1 XMLType 18

xpath 8