Archive for March, 2025
Comparative Techniques
This post is designed to illustrate PL/SQL code with techniques employed before Oracle Database 12c with manual sequence references rather than auto generated IDs. The trick is small but should be made with older code. It impacts creation statement, conditional drop statements, and modification to stored procedures that manage transactions across two or more tables.
The key to the difference is in the table creation. Prior to Oracle Database 12c, there were no automatic sequences that you could assign to table definitions. Tables were created without sequences, and sequences were created independently. Likewise, there was no “IF EXISTS” clause for a DROP statement, which meant conditional drops were required as anonymous blocks.
Old tables and sequences from the Oracle Database 11g would use the following definitions:
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 36 37 38 39 | /* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ','GRANDMA','GRANDMA_SEQ') ORDER BY 1 DESC, 2) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq; |
The key to the differences in Oracle Database 12c are in the table creation, and then in the INSERT statement because you can exclude the surrogate key column. A surrogate key column is also known as an ID column or sequence identified column.
New tables and sequences from the Oracle Database 12c forward use the following definitions to manage sequences:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | /* Conditionally drop grandma table and grandma_s sequence. */ DROP TABLE IF EXISTS tweetie_bird; DROP TABLE IF EXISTS grandma; /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER DEFAULT grandma_seq.NEXTVAL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER DEFAULT tweetie_bird_seq.NEXTVAL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn1 NOT NULL , grandma_id NUMBER DEFAULT grandma_seq.CURRVAL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); |
This is the pattern that I setup to explain the new default surrogate primary and foreign keys when I wrote the Oracle Database 12c PL/SQL Programming book (pp. 20-22). Unfortunately, it was just a simple example and fits this use case:
- You write one create_something procedure or function, also known interchangeably as a method, to insert initial records; and
- You write one add_something method add a subsequent dependent record; and
This is a common practice by many but it creates unnecessary code that you need to maintain, which translates to accumulated technical debt throughout a design. The technical debt grows by a factor of two when you want to overload behaviors for security features or batch processing.
A better solution is to write one method that accommodates both an creates a new record and adds dependent records. The warner_brother procedure will do that later in this post. To prepare for the warner_brother procedure the dependent tweetie_bird table needs to change.
The default of the .currval of a “parent” table’s sequence value is no longer guaranteed in an independent action. The create and add methods now share the same method, and inspection of an existing independent record is necessary for both the create and add methods.
The new tweetie_bird table looks like:
/* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER DEFAULT tweetie_bird_seq.NEXTVAL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn1 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn2 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); |
The next part of the post discusses how to write the create and add procedure in pre-12c and 12c forward implementations. Clearly, the automatic generated sequence values are the best approach when you use user-defined names for them as shown earlier.
The warner_brother procedure models the idea that any grandma may have one or more Tweetie birds, which means you must check if there’s a row in the grandma table. If there’s a row in the grandma table, you shouldn’t insert a new row in the grandma table. The internal get_grandma_id function returns:
- A zero if there is no existing row in the grandma table. After which, the nested if-block will insert a new row into the grandma table; and then assign the grandma_seq.CURRVAL value to a local variable.
- An existing grandma_id value, which can be used as a foreign key value in the subsequent insert statement to the tweety_bird table.
The warner_brother procedure for Oracle Database 11g is shown below:
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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | /* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; / |
The warner_brother procedure for Oracle Database 12c forward simplifies somethings and complicates others. The DEFAULT for the grandma_id foreign key column in the tweetie_bird table must be overwritten with a copy of the correct primary key value. More or less, it requires the correct grandma_id column value by finding the existing row in the grandma table. The internal get_grandma_id function grabs that value while returning a value that precludes writing to the grandma table.
The warner_brother procedure for Oracle Database 12c forward shown below shows that the grandma_id column default value is overridden by a local lv_grandma_id value returned by the internal get_grandma_id function on lines 60 thru 65 below.
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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | /* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_house ) VALUES ( pv_grandma_house ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_house , grandma_id ) VALUES ( pv_tweetie_bird_house , lv_grandma_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; / |
You can test either version with the following anonymous PL/SQL block:
/* Test the warner_brother procedure. */ BEGIN warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Cage'); warner_brother( pv_grandma_house => 'Yellow House' , pv_tweetie_bird_house => 'Tree House'); END; / |
Ultimately, we’ll use a natural join to test the integrity of primary and foreign key relationship:
/* Query results from warner_brother procedure. */ COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A20 HEADING "Grandma House" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A20 HEADING "Tweetie Bird House" SELECT * FROM grandma NATURAL JOIN tweetie_bird; |
As always, I hope this helps those trying to leverage existing PL/SQL code into more modern code.
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.
PL/SQL Coupled Loops
A standard approach using fall through in languages that support it, like C, C++, and Java leverages a case structure. Unfortunately, in language that follow the Pascal pattern case statements act like if-else-if-else statements. In the Pascal model, you need to use coupled loops.
PL/SQL follows that Pascal, via Ada, structure. So, you must couple the performance of the inner descending loop to the maximum value of the outer ascending loop. Here’s an example of how you would code that in PL/SQL with the 12 Days of Christmas song, which has a chorus that grows with each verse.
It requires the definition of two ADTs (Attribute Data Types), a UDT (User-Defined Data Type) like a struct(ure), and a UDT table of the UDT struct. They’re all done in this code snippet.
/* Create a list of strings. */ CREATE OR REPLACE TYPE days IS TABLE OF VARCHAR2(8); / /* Create a list of strings. */ CREATE OR REPLACE TYPE song IS TABLE OF VARCHAR2(36); / /* Create a record structure. */ CREATE OR REPLACE TYPE lyric IS OBJECT ( DAY VARCHAR2(8) , gift VARCHAR2(24)); / /* Create a list of the record structure. */ CREATE OR REPLACE TYPE lyrics IS TABLE OF LYRIC; / |
The twelve_days function that will display the lyrics of the 12-Days of Christmas. It reads forward through the 12 days of Christmas and backwards through the chorus in the inner loop beginning with the current day of the outer loop.
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 36 37 38 39 40 | /* Create a function. */ CREATE OR REPLACE FUNCTION twelve_days ( pv_days DAYS , pv_gifts LYRICS ) RETURN song IS /* Initialize the collection of lyrics. */ lv_retval SONG := song(); /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input VARCHAR2 ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read forward through the days. */ FOR i IN 1..pv_days.COUNT LOOP ADD('On the ' || pv_days(i) || ' day of Christmas'); ADD('my true love sent to me:'); /* Read backward through the lyrics based on ascending value of the day. */ FOR j IN REVERSE 1..i LOOP IF i = 1 THEN ADD('- '||'A'||' '||pv_gifts(j).gift); ELSE ADD('- '||pv_gifts(j).DAY||' '||pv_gifts(j).gift); END IF; END LOOP; /* A line break by verse. */ ADD(CHR(13)); END LOOP; /* Return the song's lyrics. */ RETURN lv_retval; END; / |
The typical test case for the function in PL/SQL is:
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 36 37 38 39 40 41 42 43 44 45 46 47 48 | /* Test the function in PL/SQL. */ SET SERVEROUTPUT ON SIZE UNLIMITED DECLARE /* * Declare an lv_days array of an 8 character variable length string * and initialize it with values. */ lv_days days := days('first','second','third','fourth' ,'fifth','sixth','seventh','eighth' ,'nineth','tenth','eleventh','twelfth'); /* * Declare an lv_gifts array of the user-defined LYRIC data type and * initialize it with values. */ lv_gifts lyrics := lyrics(lyric(DAY => 'and a', gift => 'Partridge in a pear tree') ,lyric(DAY => 'Two', gift => 'Turtle doves') ,lyric(DAY => 'Three', gift => 'French hens') ,lyric(DAY => 'Four', gift => 'Calling birds') ,lyric(DAY => 'Five', gift => 'Golden rings' ) ,lyric(DAY => 'Six', gift => 'Geese a laying') ,lyric(DAY => 'Seven', gift => 'Swans a swimming') ,lyric(DAY => 'Eight', gift => 'Maids a milking') ,lyric(DAY => 'Nine', gift => 'Ladies dancing') ,lyric(DAY => 'Ten', gift => 'Lords a leaping') ,lyric(DAY => 'Eleven',gift => 'Pipers piping') ,lyric(DAY => 'Twelve',gift => 'Drummers drumming')); /* * Declare an lv_days array of an 36 character variable length string * and initialize it with values. */ lv_song song := song(); BEGIN /* Call the twelve_days function and assign the results to the local * lv_song variable. */ lv_song := twelve_days(lv_days,lv_gifts); /* * Read the lines from the local lv_song variable and print them. */ FOR i IN 1..lv_song.LAST LOOP dbms_output.put_line(lv_song(i)); END LOOP; END; / |
It displays:
On the first day of Christmas my true love sent to me: - A Partridge in a pear tree On the second day of Christmas my true love sent to me: - Two Turtle doves - and a Partridge in a pear tree On the third day of Christmas my true love sent to me: - Three French hens - Two Turtle doves - and a Partridge in a pear tree ... On the twelfth day of Christmas my true love sent to me: - Twelve Drummers drumming - Eleven Pipers piping - Ten Lords a leaping - Nine Ladies dancing - Eight Maids a milking - Seven Swans a swimming - Six Geese a laying - Five Golden rings - Four Calling birds - Three French hens - Two Turtle doves - and a Partridge in a pear tree |
As always, I hope this helps those learning how to write PL/SQL and/or code.
PL/SQL List to Struct
This post shows you how to take a list of strings and convert them into a struct(ure) of a date, number, and string. Oracle implements IDL (Interface Description Language), which means the solution requires creating:
- An attribute data type (ADT), or collection of a standard date type, which in this case is a varchar2 (variable length string).
- A user defined type (UDT), which is an object type with or without methods. In this case, it implements a UDT without methods.
- A collection of the UDT object type (known in Oracle’s parlance as a table).
Here’s the data definition language (DDL) for the three required structures:
- The ADT of strings:
CREATE OR REPLACE TYPE tre IS TABLE OF VARCHAR2(100); /
- The UDT struct(ure) object type:
CREATE OR REPLACE TYPE struct IS OBJECT ( xdate DATE , xnumber NUMBER , xstring VARCHAR2(20)); /
- The UDT structs collection or Oracle table:
CREATE OR REPLACE TYPE structs IS TABLE OF struct; /
Next, we create a function that accepts a collection of strings and returns a record UDT, which is the struct(ure) object type. As PL/SQL code gets larger, using conditional compilation can help discover problems.
Enable conditional compilation with the following statement:
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:1'; |
The cast_strings function is defined as:
CREATE OR REPLACE FUNCTION cast_strings ( pv_list TRE ) RETURN struct IS /* Declare a UDT and initialize an empty struct variable. */ lv_retval STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); /* A debugger function. */ FUNCTION debugger ( pv_string VARCHAR2 ) RETURN VARCHAR2 IS /* Declare return value. */ lv_retval VARCHAR2(60); BEGIN /* Conditional compilation evaluation. */ $IF $$DEBUG = 1 $THEN lv_retval := 'Evaluating ['||pv_string||']'; $END /* Return debug value. */ RETURN lv_retval; END debugger; BEGIN /* Loop through list of values to find only the numbers. */ FOR i IN 1..pv_list.LAST LOOP /* Print debugger remark. */ dbms_output.put_line(debugger(pv_list(i))); /* Ensure that a sparsely populated list can't fail. */ IF pv_list.EXISTS(i) THEN /* Order if number evaluation before string evaluation. */ CASE /* Implement WHEN clause that checks that the xnumber member is null and that the pv_list element contains only digits; and assign the pv_list element to the lv_retval's xnumber member. */ WHEN REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN lv_retval.xnumber := pv_list(i); /* Implement WHEN clause that checks that the xdate member is null and that the pv_list element is a valid date; and assign the pv_list element to the lv_retval's xdate member. */ WHEN verify_date(pv_list(i)) THEN lv_retval.xdate := pv_list(i); /* Implement WHEN clause that checks that the xstring member is null and that the pv_list element contains only alphanumeric values; and assign the pv_list element to the lv_retval's xstring member. */ WHEN REGEXP_LIKE(pv_list(i),'^([[:alnum:]]|[[:punct:]]|[[:space:]])*$') THEN lv_retval.xstring := pv_list(i); ELSE NULL; END CASE; END IF; END LOOP; /* Print the results. */ RETURN lv_retval; END; / |
The following tests the cast_strings function:
DECLARE /* Define a list. */ lv_list TRE := tre('16-APR-2018','Day after ...','1040'); /* Declare a structure. */ lv_struct STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); BEGIN /* Assign a parsed value set to get a value structure. */ lv_struct := cast_strings(lv_list); /* Print the values of the compound struct variable. */ dbms_output.put_line('xstring ['||lv_struct.xstring||']'); dbms_output.put_line('xdate ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']'); dbms_output.put_line('xnumber ['||lv_struct.xnumber||']'); END; / |
If the PLSQL_CCFLAGS is enabled for the session, the function will display these three rows:
Evaluating [16-APR-2018] Evaluating [DAY after ...] Evaluating [1040] |
Whether the PLSQL_CCFLAGS is set or not, the test case returns the following:
xstring [DAY after ...] xdate [16-APR-2018] xnumber [1040] |
As always, I hope this helps those looking for new approaches.
No Set f(x) in GO
Curious little tidbit as I continue my exploration of the GO programming language. I discovered that it doesn’t have a native set function that would let you create a set from a list. For example, converting a non-unique list of integers into a sorted set of integers is easy in Python:
a = [1, 2, 3, 1, 7, 6, 2, 5, 1, 3, 2] for x in sorted(list(set(a))): print(x) |
That returns:
1 2 3 5 6 7 |
Unfortunately, there’s no equivalent to Python’s IDLE (Integrated Development and Learning Environment) for GO. The best you have is to use the GO Playground; which gives you a shell where you can write small functions and your main() method to test concepts.
Here’s the equivalent of the two (or if you like three) lines of Python code in the GO Programming language to print the unique integers in an ascending sort:
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 36 37 38 39 40 41 42 | // You can edit this code! // Click here and start typing. package main import ( "fmt" "sort" ) // Convert a slice of non-unique integers into a set. func set(slice []int) []int { // Define a map and slice. keys := make(map[int]bool) set := make([]int, 0, len(slice)) // Traverse slice, assign new values as map keys, and assign map keys to new slice. for _, val := range slice { if !keys[val] { keys[val] = true set = append(set, val) } } // Return the set of integers. return set } func main() { // Create a slice of non-unique integers. a := []int{1, 2, 3, 1, 7, 6, 2, 5, 1, 3, 2} // Create a map of unique integers, as keys of the Boolean map. b := set(a) // Sort the unordered keys into ordered keys. sort.Ints(b) // Print the sorted unique results from the original non-unique slice. for _, i := range b { fmt.Println(i) } } |
You can past this into the GO Playground and click the run button to get those same sorted and unique values:
1 2 3 5 6 7 |
As always, I hope this helps a few users of the blog.
Hidden thought SQL question
I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.
It’s a simple question that tests two levels of understanding. The question is:
- If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?
Level 1 Understanding
Here’s the test script:
CREATE TABLE a1 ( text VARCHAR2(12) ); INSERT INTO a1 ( text ) VALUES ('Hello World!'); CREATE TABLE b1 ( text VARCHAR2(12) ); INSERT INTO b1 ( text ) VALUES ('Hello World!'); ROLLBACK; |
The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.
Level 2 Understanding
Here’s the test script:
CREATE TABLE a2 AS (SELECT 'Hello World!' AS text); CREATE TABLE b2 AS (SELECT 'Hello World!' AS text); ROLLBACK; |
The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.
Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.
Go Config & PostgreSQL
Finally, I got around to building a GO environment with PostgreSQL on Ubuntu. Next, I’ll need to sort out how to extend it to Oracle and MySQL. The only tedious part was figuring out where the current PostgreSQL ODBC driver was on GitHub.
The installation of GO has two options. You can install the version from the Linux Distro’s repo, or you can install the most current version from the GO website. The generic install on Ubuntu uses the following two commands:
sudo apt update sudo apt install -y golang-go |
This will install GO in the /usr/bin directory, which means you don’t need to add it manually to your $PATH environment variable in your .bashrc file.
Download from the GO website into your /home/username/Downloads directory and then run this command:
sudo tar -C /usr/local -xzf /home/student/Downloads/go1.24.0.linux-amd64.tar.gz |
If you haven’t installed the distro’s repo of the GO programming environment, you should add this to your .bashrc file:
# Added to PATH for Go export set PATH=$PATH:/usr/local/go/bin |
However, add the following if you have installed the Linux distorts GO repo and prefer to use the most current release available (a which -a go command displays the go program files in your current $PATH variable and you can call the first one in the list without an absolute or fully qualified file name but the second requires a fully qualified file name):
# Added to PATH for Go export set PATH=/usr/local/go/bin:$PATH |
Now, you should decide where you want to put your GO programs. I chose to create a base .go directory and then project directories beneath that. Within each project directory you should create bin, pkg, and src subdirectories, as shown below:
mkdir .go .go/hello .go/hello/bin .go/hello/pkg .go/hello/src |
You can write a “Hello World” program like the following in you ~.go/hello/src directory, which I’ve named hello-world.go for illustrative purposes:
// Declare package. package main // Import library. import "fmt" // Define main function using fmt library to print. func main() { fmt.Println("Hello World!") } |
Next, you should create a makefile, like this in the same ~.go/hello/src directory. If you’re new to the rules for creating a makefile, there can be no leading or trailing white spaces, and you should use before commands:
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/hello hello-world.go |
Before you run the make utility, you need to initialize the module with this syntax:
go mod init |
It will create the go.mod file. You will see something like the following when you display the content of the go.mod file.
module hello-world.go go 1.24.0 |
You have completed your project preparation. You run the make command to create the hello executable in the ~/.go/hello/bin directory.
Run the make utility:
make |
You can run it from your current directory with this command:
~/.go/hello/bin/hello |
It will return to console:
Hello World! |
Having built the GO development environment and a rudimentary starting program, it’s time to learn how to write a GO program that queries the PostgreSQL database. The first step is finding the correct PostgreSQL library in GitHub.com. A number of web sites post examples using the pq library but that’s no longer correct.
You need to download the pgx ODBC driver with the following GO command:
go get github.com/jackc/pgx@latest |
It will display the following to the console:
go: downloading github.com/jackc/pgx v3.6.2+incompatible go: downloading github.com/pkg/errors v0.9.1 go: downloading golang.org/x/crypto v0.35.0 go: downloading golang.org/x/text v0.22.0 go: added github.com/jackc/pgx v3.6.2+incompatible go: added github.com/pkg/errors v0.9.1 go: added golang.org/x/crypto v0.35.0 go: added golang.org/x/text v0.22.0 |
To use the standard database/sql approach you’ll also need to run this command:
go get github.com/jackc/pgx/v5/stdlib |
It will display the following to the console:
go: downloading github.com/jackc/pgx/v5 v5.7.2 go: downloading github.com/jackc/pgpassfile v1.0.0 go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: downloading github.com/jackc/puddle/v2 v2.2.2 go: downloading golang.org/x/sync v0.11.0 go: added github.com/jackc/pgpassfile v1.0.0 go: added github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 go: added github.com/jackc/pgx/v5 v5.7.2 go: added github.com/jackc/puddle/v2 v2.2.2 |
Change back to your home directory and run this command to build a new connect project:
mkdir .go/postgres .go/postgres/connect .go/postgres/connect/bin .go/postgres/connect/pkg .go/postgres/connect/src |
Create postgres-connect.go program in the .go/postgres/connect/src directory:
package main import ( "database/sql" "fmt" "os" _ "github.com/jackc/pgx/v5/stdlib" ) func main() { // Open connection and defer connection close. db, err := sql.Open("pgx", "host=localhost user=student password=student dbname=videodb sslmode=disable") if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer db.Close() // Declare an output variable for the query. var version string err = db.QueryRow("SELECT version()").Scan(&version) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } // Print database version. fmt.Println(version) } |
Next, you should create another makefile in the same ~.go/postgres/connect/src directory.
.DEFAULT_GOAL := build .PHONY: fmt vet build fmt: go fmt ./... vet: fmt go vet ./... build: vet go build -o ../bin/connect postgres-connect.go |
Before you run the make utility, you need to initialize the go.mod module file and check for dependencies in it. That requires the following two commands:
go mod init |
Which returns the following list of dependencies:
go: finding module for package github.com/jackc/pgx/v5/stdlib go: found github.com/jackc/pgx/v5/stdlib in github.com/jackc/pgx/v5 v5.7.2 |
There are two ways to fix this but the easiest is to run this command:
go mod tidy |
It adds the dependencies to the go.mod file. You will see something like the following when you display the content of the go.mod file.
module postgres-connect.go go 1.24.0 require github.com/jackc/pgx/v5 v5.7.2 require ( github.com/jackc/pgpassfile v1.0.0 // indirect github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect github.com/jackc/puddle/v2 v2.2.2 // indirect golang.org/x/crypto v0.31.0 // indirect golang.org/x/sync v0.10.0 // indirect golang.org/x/text v0.21.0 // indirect ) |
You should also note that the go mod tidy command created new go.sum file for the dependencies, which should look like the following:
github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo= github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= github.com/jackc/pgx/v5 v5.7.2 h1:mLoDLV6sonKlvjIEsV56SkWNCnuNv531l94GaIzO+XI= github.com/jackc/pgx/v5 v5.7.2/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ= github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo= github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk= github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4= golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U= golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk= golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ= golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk= golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo= golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ= gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= |
Run the make utility:
make |
You can now run make to create the connect executable, which you can call with the following command:
~/.go/postgres/connect/bin/connect |
It will return to console:
PostgreSQL 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit |
Subscribe via RSS