DEV Community

Cover image for How to write an anonymous PL/SQL function and use it in your SQL statement
Gerald Venzl 🚀
Gerald Venzl 🚀

Posted on • Originally published at geraldonit.com

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

#sql #oracle #database #plsql

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

capitalize function 

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

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Top comments (0)