How to write an anonymous PL/SQL function and use it in your SQL statement

Have you ever encountered a situation when you were in the middle of writing a SQL query and thought, “if only I could write a quick PL/SQL function for this, it would make this quick and easy?” But, unfortunately, you don’t have any privileges to create any functions in the schema. Luckily, since Oracle Database 12c, there is an answer for you.

With the release of Oracle Database 12.1.0.1 in 2013, Oracle introduced the capability of writing anonymous PL/SQL declarations as part of a SQL query. This is done with the WITH clause also referred to as Common Table Expression clause or CTE, which can now contain said anonymous PL/SQL declarations.

Let’s imagine for a second that you have a table products containing information about various sellers’ products. This could look something like this:

CREATE TABLE products (
  id     NUMBER         NOT NULL PRIMARY KEY,
  name   VARCHAR(255)   NOT NULL,
  url    VARCHAR(255)
);

INSERT INTO products VALUES (1, 'AirPods Pro (2nd gen)', 'https://www.apple.com/shop/product/MQD83AM/A/airpods-pro');

INSERT INTO products VALUES (2, 'SanDisk - Ultra 512GB USB 3.0', 'https://www.bestbuy.com/site/sandisk-ultra-512gb-usb-3-0-flash-drive-black/6422265.p');

commit;

You would like to write a query that provides you, say, the product name, the domain where each product is sold and the URL. But you want to ensure that the first letter of the name and the domain name is always capitalized. Sure, you can do that in pure SQL, but you are comfortable with PL/SQL, and such functions are written quickly:

get_domain_name function

FUNCTION get_domain_name
  (
    p_url VARCHAR2,
    p_sub_domain VARCHAR2 DEFAULT 'www.'
  )
  RETURN VARCHAR2
IS
  v_begin_pos BINARY_INTEGER;
  v_length    BINARY_INTEGER;
BEGIN
  v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
  v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
  RETURN SUBSTR(p_url, v_begin_pos, v_length);
END;

capitalize function

FUNCTION capitalize (p_string VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
END;

With this new feature, you can simply wrap both these functions into the WITH clause and reuse them within your SQL block, even multiple times:

WITH
  -- Function to capitalize input string
  FUNCTION capitalize
    (
      p_string VARCHAR2
    )
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
  END;
  -- Function to retrieve the domain name from a URL
  FUNCTION get_domain_name
    (
      p_url VARCHAR2,
      p_sub_domain VARCHAR2 DEFAULT 'www.'
    )
    RETURN VARCHAR2
  IS
    v_begin_pos BINARY_INTEGER;
    v_length    BINARY_INTEGER;
  BEGIN
    v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
    v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
    RETURN SUBSTR(p_url, v_begin_pos, v_length);
  END;
-- SQL statement
SELECT capitalize(name) as name, capitalize(get_domain_name(url)) AS domain_name, url
  FROM products;

To the database, this is just another SELECT statement with a common table expression. You do not need any write privileges on the schema for the user you are connected with:

SQL> WITH
  2    -- Function to capitalize input string
  3    FUNCTION capitalize
  4      (
  5        p_string VARCHAR2
  6      )
  7      RETURN VARCHAR2
  8    IS
  9    BEGIN
 10      RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
 11    END;
 12    -- Function to retrieve the domain name from a URL
 13    FUNCTION get_domain_name
 14      (
 15        p_url VARCHAR2,
 16        p_sub_domain VARCHAR2 DEFAULT 'www.'
 17      )
 18      RETURN VARCHAR2
 19    IS
 20      v_begin_pos BINARY_INTEGER;
 21      v_length    BINARY_INTEGER;
 22    BEGIN
 23      v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
 24      v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
 25      RETURN SUBSTR(p_url, v_begin_pos, v_length);
 26    END;
 27  -- SQL statement
 28  SELECT capitalize(name) as name, capitalize(get_domain_name(url)) AS domain_name, url
 29    FROM products;
 30 /

NAME                             DOMAIN_NAME    URL
________________________________ ______________ _______________________________________________________________________________________
AirPods Pro (2nd gen)            Apple          https://www.apple.com/shop/product/MQD83AM/A/airpods-pro
SanDisk - Ultra 512GB USB 3.0    Bestbuy        https://www.bestbuy.com/site/sandisk-ultra-512gb-usb-3-0-flash-drive-black/6422265.p