PL/SQL Table Function
Eleven years ago I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they were available in Microsoft T-SQL User-Defined Functions (UDFs) with Microsoft’s Common Language Infrastructure (CLI). Naturally, I wrote an example for them in T-SQL. Now a reader wants an equivalent PL/SQL example.
I figured that borrowing that sample data was appropriate. This creates the conquistador table:
CREATE TABLE conquistador ( conquistador_id NUMBER GENERATED ALWAYS AS IDENTITY , conquistador VARCHAR2(30) , actual_name VARCHAR2(30) , nationality VARCHAR2(30) , lang VARCHAR2(2)); |
Then, you can insert the following data:
INSERT INTO conquistador ( conquistador, actual_name, nationality, lang ) VALUES ('Juan de Fuca','Ioánnis Fokás','Greek','el') ,('Nicolás de Federmán','Nikolaus Federmann','German','de') ,('Sebastián Caboto','Sebastiano Caboto','Venetian','it') ,('Jorge de la Espira','Georg von Speyer','German','de') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs') ,('Fernando Consag','Ferdinand Konšcak','Croatian','sr') ,('Américo Vespucio','Amerigo Vespucci','Italian','it') ,('Alejo García','Aleixo Garcia','Portuguese','pt'); |
Next, create a UDT struct(ure) and table of that structure:
/* Create type of object structure. */ CREATE OR REPLACE TYPE conquistador_struct IS OBJECT ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)); / /* Create table of object structure. */ CREATE OR REPLACE TYPE conquistador_table IS TABLE OF conquistador_struct; / |
Now, we can create a table function that filters on nationality:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | CREATE OR REPLACE FUNCTION getConquistador (pv_lang IN VARCHAR) RETURN conquistador_table IS /* Declare a return variable. */ lv_retval CONQUISTADOR_TABLE := conquistador_table(); /* Declare a dynamic cursor. */ CURSOR get_conquistador ( cv_lang VARCHAR2 ) IS SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = cv_lang; /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input CONQUISTADOR_STRUCT ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read through the cursor and assign to the UDT table. */ FOR i IN get_conquistador(pv_lang) LOOP ADD(conquistador_struct( conquistador => i.conquistador , actual_name => i.actual_name , nationality => i.nationality )); END LOOP; /* Return collection. */ RETURN lv_retval; END; / |
You can select only the rows with German nationality, qualified by the ISO standard DE with the following query:
COL conquistador FORMAT A21 COL actual_name FORMAT A21 COL nationality FORMAT A12 SELECT * FROM TABLE(getConquistador('de')); |
It should return:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicolás de Federmán Nikolaus Federmann German Jorge de la Espira Georg von Speyer German |
I retested this on Oracle Database 23ai Free, and found the NLS character set fails to support the characters for Nicolás de Federmán. It returns:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicol??s de Federm??n Nikolaus Federmann German Jorge de la Espira Georg von Speyer German |
This is interesting because it brought to my attention that Oracle does try to account for unreadable ASCII values based on character sets. In Google’s GO language these would be considered “Runes”.
As always, I hope these coding samples help those solving new problems.
Subscribe via RSS