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
Gerald on IT 