Built-in SQL Functions in PL/SQL

Built-in SQL Functions in PL/SQL allow developers to perform powerful operations such as string manipulation, arithmetic, date handling, type conversion, and error reporting. These functions come from the Oracle STANDARD package, enabling easy access across PL/SQL programs. In this guide, we’ll explore all major categories of built-in SQL functions with syntax, examples, and usage tips.

Overview of Built-in SQL Functions in PL/SQL

Character Functions in PL/SQL

Character functions operate on VARCHAR2 or CHAR inputs and return a string or numeric value. Common functions include LOWER, UPPER, INITCAP, SUBSTR, INSTR, and regular expressions like REGEXP_REPLACE.

Example:

BEGIN
  DBMS_OUTPUT.PUT_LINE(LOWER('Case Conversion Example'));
END;

Numeric SQL Functions in PL/SQL

These functions perform operations on numeric data types. Examples include ROUND, CEIL, FLOOR, MOD, ABS, POWER, and SQRT.

Example:

DECLARE
  v_num NUMBER := 1234.567;
BEGIN
  DBMS_OUTPUT.PUT_LINE(ROUND(v_num, 2)); -- 1234.57
END;

Date Functions in PL/SQL

Date functions help you manipulate and retrieve information about dates and timestamps. Popular ones are SYSDATE, SYSTIMESTAMP, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, and NEXT_DAY.

Example:

DECLARE
  v_date DATE := SYSDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Today: ' || TO_CHAR(v_date, 'DD-MON-YYYY'));
END;

Conversion SQL Functions in PL/SQL

Conversion functions convert one data type to another. Common functions include TO_DATE, TO_CHAR, TO_NUMBER, TO_TIMESTAMP.

Example:

DECLARE
  v_str VARCHAR2(20);
  v_date DATE := SYSDATE;
BEGIN
  v_str := TO_CHAR(v_date, 'YYYY-MM-DD');
  DBMS_OUTPUT.PUT_LINE('Formatted Date: ' || v_str);
END;

Error Handling with Built-in SQL Functions

These functions are unique to PL/SQL and cannot be used directly in SQL. They include SQLCODE and SQLERRM, used in exception handling.

Example:

BEGIN
  SELECT dummy INTO :v FROM dual WHERE 1=2;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;

Other Useful Built-in SQL Functions in PL/SQL

Some useful built-in SQL functions don’t fall into standard categories:

Function	Purpose
`GREATEST`	Returns the largest of multiple values
`LEAST`	Returns the smallest
`COALESCE`	Returns first non-null expression
`NVL`	Substitutes null with a given value
`DECODE`	Conditional logic similar to CASE

Example:

DECLARE
  v1 NUMBER := GREATEST(100, 200, 150);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Greatest: ' || v1);
END;

Summary of Built-in SQL Functions in PL/SQL

Function Type	Examples
Character	LOWER, UPPER, SUBSTR, REGEXP_REPLACE
Numeric	ROUND, MOD, POWER, ABS
Date	SYSDATE, ADD_MONTHS, LAST_DAY, SYSTIMESTAMP
Conversion	TO_DATE, TO_CHAR, TO_NUMBER
Error	SQLERRM, SQLCODE
Miscellaneous	GREATEST, LEAST, COALESCE, NVL, DECODE

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.