Archive for the ‘Linux’ Category
Waking up the Network
Interesting problems seem to plague me from time to time. The current problem isn’t yet solved but I’m working on it. After a sleep cycle, IPV6 networking isn’t starting.
Try:
- Checking the network cables, modem, and router
- Reconnecting to Wi-Fi
ERR_INTERNET_DISCONNECTED |
In the broken Fedora 30 VM, I checked the status with the nmcli tool:
sudo nmcli general status |
It returned:
STATE CONNECTIVITY WIFI-HW WIFI WWAN-HW WWAN asleep none enabled enabled enabled enabled |
The STATE should return connected and connectivity return full. Unfortunately, that’s not the case.
There was little surprise that the next check:
sudo nmcli device |
Returned the following:
DEVICE TYPE STATE CONNECTION virbr0 bridge unmanaged -- ens33 ethernet unmanaged -- lo loopback unmanaged -- virbr0-nic tun unmanaged -- |
In a working instance, it should return:
DEVICE TYPE STATE CONNECTION ens33 ethernet connected ens33 virbr0 bridge connected virbr0 lo loopback unmanaged -- virbr0-nic tun unmanaged -- |
I’m currently troubleshooting what failed by leveraging an article on How to Configure Network Connection Using ‘nmcli’ Tool and the Gnome nmcli documentation. Naturally, when I get it fixed, I’ll finish this article.
MySQL with CTEs
As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.
These bullets describe the examples:
- Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
- Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
- Uses a table to hold the variables necessary to evaluate and add the element to the query result set.
This is the first Python program:
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 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Declare a list of tuples. dict = [{'level':'Hollywood Star','min_roles':30,'max_roles':99999} ,{'level':'Prolific Actor','min_roles':20,'max_roles':29} ,{'level':'Newcomer','min_roles':1,'max_roles':19}] # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("SELECT a.actor_id " ", a.first_name " ", a.last_name " ", COUNT(fa.actor_id) AS films " "FROM actor a INNER JOIN film_actor fa " "ON a.actor_id = fa.actor_id " "GROUP BY a.actor_id " ", a.first_name " ", a.last_name " "ORDER BY a.last_name " ", a.first_name") # Execute cursor. cursor.execute(query) # Display the rows returned by the query. for (actor_id, first_name, last_name, films) in cursor: for i in range(len(dict)): if films >= dict[i]["min_roles"] and films <= dict[i]["max_roles"]: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , dict[i]["level"] , films)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
The Python dictionary on lines 7 thru 9 and range for loop and if statement on lines 41 and 42 can be eliminated by putting the literal values in a Common Table Expression (CTE). That’s because a CROSS JOIN matches all rows in the CTE against the base table before filtering them.
The match of all rows in the CTE against the base table effectively replaces the range for loop in the original code. The WHERE clause replaces the if statement in the original code.
Another optimization for readability of the final query puts the grouped query into a CTE as well. That way the final query simply demonstrates the filtering process.
This is the second Python program, and it converts the Python dictionary to a list of lists and assigns the lists to param tuple:
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 81 82 83 84 85 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Declare a list of lists. list = [['Hollywood Star',30,99999] ,['Prolific Actor',20,29] ,['Newcomer',1,19]] # Declare a tuple of the set of lists. param = (list[0] + list[1] + list[2]) # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.actor_id " " , a.first_name " " , a.last_name " " , COUNT(fa.actor_id) AS num_roles " " FROM actor a INNER JOIN film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.actor_id " " , a.first_name " " , a.last_name ) " " , levels AS " "(SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles " " UNION ALL " " SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles " " UNION ALL " " SELECT %s AS level " " , %s AS min_roles " " , %s AS max_roles) " " SELECT a.first_name " " , a.last_name " " , l.level " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query, param) # Display the rows returned by the query. for (first_name, last_name, level, num_roles) in cursor: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , level.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
This is the third Python program requires some SQL setup. You should run this script inside the sakila database first. It basically takes the variables out of the code and stores them in a table. This is more likely what you would do to ensure maintainability of ever changing range values like these if you built a solution like this in a real application. It leaves the aggregation process inside a CTE and simplifies the final query.
-- Conditionally drop the levels table. DROP TABLE IF EXISTS levels; -- Create the levels list. CREATE TABLE levels ( level VARCHAR(16) , min_roles INT , max_roles INT ); -- Insert values into the list table. INSERT INTO levels ( level, min_roles, max_roles ) VALUES ('Hollywood Star', 30, 99999) ,('Prolific Actor', 20, 29) ,('Newcommer',1,19); |
After seeding the data in the levels table, you can test the query natively in MySQL, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- Query the data. WITH actors AS (SELECT a.actor_id , a.first_name , a.last_name , COUNT(*) AS num_roles FROM actor a INNER JOIN film_actor fa ON a.actor_id = fa.actor_id GROUP BY actor_id) SELECT a.first_name , a.last_name , l.level , a.num_roles FROM actors a CROSS JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles ORDER BY a.last_name , a.first_name; |
There’s also a syntax that makes this type of query appear to be an INNER JOIN when it’s actually a filtered CROSS JOIN. If you adopt that syntax, you would rewrite lines 14 and 15:
14 15 | FROM actors a INNER JOIN levels l WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles; |
Then, you can run this version without the second CTE element:
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 | # Import the library. import sys import mysql.connector from mysql.connector import errorcode # Attempt the query. # ============================================================ # Use a try-catch block to manage the connection. # ============================================================ try: # Open connection. cnx = mysql.connector.connect(user='student', password='student', host='127.0.0.1', database='sakila') # Create cursor. cursor = cnx.cursor() # Set the query statement. query = ("WITH actors AS " "(SELECT a.actor_id " " , a.first_name " " , a.last_name " " , COUNT(fa.actor_id) AS num_roles " " FROM actor a INNER JOIN film_actor fa " " ON a.actor_id = fa.actor_id " " GROUP BY a.actor_id " " , a.first_name " " , a.last_name ) " " SELECT a.first_name " " , a.last_name " " , l.level " " , a.num_roles " " FROM actors a CROSS JOIN levels l " " WHERE a.num_roles BETWEEN l.min_roles AND l.max_roles " " ORDER BY a.last_name " " , a.first_name") # Execute cursor. cursor.execute(query) # Display the rows returned by the query. for (first_name, last_name, level, num_roles) in cursor: print('{0} {1} is a {2} with {3} films.'.format( first_name.title() , last_name.title() , level.title() , num_roles)) # Close cursor. cursor.close() # ------------------------------------------------------------ # Handle exception and close connection. except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error code:", e.errno) # error number print("SQLSTATE value:", e.sqlstate) # SQLSTATE value print("Error message:", e.msg) # error message # Close the connection when the try block completes. else: cnx.close() |
As always, I hope this helps those trying to understand how CTEs can solve problems that would otherwise be coded in external imperative languages like Python.
MySQL and JavaScript
Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?
// Declare a string and substring. var myStr = 'Get me from the string.' var mySubstr = 'me' // Assign the substring to variable by rescuing it from the larger string. var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length) // Print the result. print(rescued) |
tested with MongoDB, like
mongo --nodb --norc < parsing.js |
returning:
MongoDB shell version v4.0.20 me bye |
They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.
They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).
The MySQL steps are:
-- Stable testing scenario with table values requires a test table. DROP TABLE IF EXISTS sample; CREATE TABLE sample ( text VARCHAR(100) ); -- Insert the literal string into the testing table. INSERT INTO sample ( text ) VALUES ('Get me from the string.'); -- Test using a WITH clause to place a variable in context for use -- in the query, relying on the fact that a Cartesian set of one -- column and row becomes a new column in all rows of the other -- table's set. WITH struct AS (SELECT 'me' AS result) SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued FROM sample CROSS JOIN struct; |
It returns the following:
+---------+ | rescued | +---------+ | me | +---------+ 1 row in set (0.00 sec) |
Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.
Oracle’s Sparse Lists
Oracle’s PL/SQL Programming Language is really quite nice. I’ve written 8 books on it and still have fun coding in it. One nasty little detail about Oracle’s lists, introduced in Oracle 8 as PL/SQL Tables according their documentation, is they rely on sequential numeric indexes. Unfortunately, Oracle lists support a DELETE method, which can create gaps in the sequential indexes.
Oracle calls a sequence without gaps densely populated and a sequence with gaps sparsely populated. This can cause problems when PL/SQL code inadvertently removes elements at the beginning, end, or somewhere in the middle of the list. That’s because a program can then pass the sparsely populated list as a parameter to another stored function or procedure where the developer may traverse the list in a for-loop. That traversal may raise an exception in a for-loop, like this when it has gaps in the index sequence:
DECLARE * ERROR AT line 1: ORA-01403: no data found ORA-06512: AT line 20 |
Oracle’s myriad built-in libraries don’t offer a function to compact a sparsely populated list into a densely populated list. This post provides a compact stored procedure that converts a sparsely populated list to a densely populated list.
The first step to using the compact stored procedure requires that you create an object type in SQL, like this list of 20-character strings:
DROP TYPE list; CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(20); / |
Now, you can implement the compact stored procedure by passing the User-Defined Type as it’s sole parameter.
CREATE OR REPLACE PROCEDURE compact ( sparse IN OUT LIST ) IS /* Declare local variables. */ iterator NUMBER; -- Leave iterator as null. /* Declare new list. */ dense LIST := list(); BEGIN /* Initialize the iterator with the starting value, which is necessary because the first element of the original list could have been deleted in earlier operations. Setting the initial iterator value to the first numeric index value ensures you start at the lowest available index value. */ iterator := sparse.FIRST; /* Convert sparsely populated list to densely populated. */ WHILE (iterator <= sparse.LAST) LOOP dense.EXTEND; dense(dense.COUNT) := sparse(iterator); iterator := sparse.NEXT(iterator); END LOOP; /* Replace the input parameter with the compacted list. */ sparse := dense; END; / |
Before we test the compact stored procedure, let’s create deleteElement stored procedure for our testing:
CREATE OR REPLACE PROCEDURE deleteElement ( sparse IN OUT LIST , element IN NUMBER ) IS BEGIN /* Delete a value. */ sparse.DELETE(element); END; / |
Now, let’s use an anonymous block to test compacting a sparsely populated list into a densely populated list. The test program will remove the first, last, and one element in the middle before printing the sparsely populated list’s index and string values. This test will show you gaps in the remaining non-sequential index values.
After you see the gaps, the test program compacts the remaining list values into a new densely populated list. It then prints the new index values with the data values.
DECLARE /* Declare a four item list. */ lv_strings LIST := list('one','two','three','four','five','six','seven'); BEGIN /* Check size of list. */ dbms_output.put_line('Print initial list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); /* Delete a value. */ deleteElement(lv_strings,lv_strings.FIRST); deleteElement(lv_strings,3); deleteElement(lv_strings,lv_strings.LAST); /* Check size of list. */ dbms_output.put_line('Print modified list size: ['||lv_strings.COUNT||']'); dbms_output.put_line('Print max index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.LAST LOOP IF lv_strings.EXISTS(i) THEN dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END IF; END LOOP; /* Call a procedure by passing current sparse collection and the procedure returns dense collection. */ dbms_output.put_line('==================================='); dbms_output.put_line('Compacting list.'); compact(lv_strings); dbms_output.put_line('==================================='); /* Print the new maximum index value and list size. */ dbms_output.put_line('Print new index and size: ['||lv_strings.LAST||']['||lv_strings.COUNT||']'); dbms_output.put_line('==================================='); FOR i IN 1..lv_strings.COUNT LOOP dbms_output.put_line('List list index and item: ['||i||']['||lv_strings(i)||']'); END LOOP; dbms_output.put_line('==================================='); END; / |
It produces output, like:
Print initial list size: [7] =================================== Print modified list size: [4] Print max index and size: [6][4] =================================== List list index and item: [2][two] List list index and item: [4][four] List list index and item: [5][five] List list index and item: [6][six] =================================== Compacting list. =================================== Print new index and size: [4][4] =================================== List list index and item: [1][two] List list index and item: [2][four] List list index and item: [3][five] List list index and item: [4][six] =================================== |
You can extend this concept by creating User-Defined Types with multiple attributes, which are essentially lists of tuples (to draw on Pythonic lingo).
Title Case Anyone?
Sometimes life is too surreal. Like when somebody says, “How do you get title case in an Oracle database?” That’s when you know three things about the individual, while suppressing laughter. They’re not very experienced with SQL, likely lazy, and don’t read the documentation.
I had a little fun with somebody today by taking them down a small rat-hole. “Oh, gosh … ” I said, “… let’s write a function for that.” Here’s the joke function, like:
CREATE OR REPLACE FUNCTION title_case ( string VARCHAR2 ) RETURN VARCHAR2 IS BEGIN /* Change upper case to title case. */ RETURN UPPER(SUBSTR(string,1,1)) || LOWER(SUBSTR(string,2,LENGTH(string))); END title_case; / |
Then, we tested it with a query from the pseudo dual table:
SELECT title_case('incredible') AS "Proper Name" FROM dual; |
It returned:
Proper Name ---------- Incredible |
Then, I said “Oh, that’s not his proper name in the Pixar World.” It should be: Mr. Incredible. Let’s try that:
SELECT title_case('mr. incredible') AS "Proper Name" FROM dual; |
It returned:
Proper Name -------------- Mr. incredible |
Then, I said: “That’s not what we want at all. Should we rewrite our function or simply use the INITCAP built-in function?” Then, I wrote:
SELECT INITCAP('mr. incredible') AS "Proper Name" FROM dual; |
It returned:
Proper Name -------------- Mr. Incredible |
Well, needless to say my acquaintance got it immediately and said “I should have Googled it or read the documentation.” I concurred with his observation.
Just sharing a cute day’s event that made me laugh and cry at the same time because there are too many who say SQL isn’t worth learning.
MySQL PHP Transaction
My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.
The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.
The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:
- Submits credentials from a file and raises an error when they don’t work.
- Initializes a SQL statement.
- Disables auto commit.
- Starts a transaction.
- Defines a first SQL statement with placeholders.
- Binds local variables to the first SQL statement’s placeholders.
- Rolls back the transaction when the first statement fails and continues to the next SQL statement when first statement succeeds.
- Defines a second SQL statement with placeholders.
- Binds local variables to the second SQL statement’s placeholders.
- Rolls back the transaction when the second statement fails and commits the work when the second statement succeeds.
- Closes a SQL statement.
The code for the PHP function 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 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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | /* || Function Name: update_membership || ---------------------------------------------------------------------- || No return, treated as a procedure, or method returning a void. || ---------------------------------------------------------------------- */ function update_membership( $h_account_number , $h_member_type , $h_credit_card_number , $h_credit_card_type , $account_number , $member_type , $credit_card_number , $credit_card_type , $h_first_name , $h_middle_name , $h_last_name , $h_contact_type , $first_name , $middle_name , $last_name , $contact_type) { // Include the credentials file if omitted. include_once("MySQLCredentials.inc"); // Assign credentials to connection. $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE); // Check for connection error and print message. if ($mysqli->connect_errno) { print $mysqli->connect_error."<br />"; print "Connection not established ...<br />"; } else { // Initial statement. $stmt = $mysqli->stmt_init(); // Disable auto commit when you want two plus statements run. $mysqli->autocommit(FALSE); // Set the transaction guarantee. $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); // Declare a static query. $sql = "UPDATE member\n" . "SET member_type = ?\n" . ", credit_card_number = ?\n" . ", credit_card_type = ?\n" . "WHERE account_number = ?\n" . "AND member_type = ?\n" . "AND credit_card_number = ?\n" . "AND credit_card_type = ?\n"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param( "sssssss" , $member_type , $credit_card_number , $credit_card_type , $account_number , $h_member_type , $h_credit_card_number , $h_credit_card_type); } // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Rollback or undo the transaction. $mysqli->rollback(); // Print failure to resolve query message. print $mysqli->error."<br />\n"; print "Failed to resolve query ...<br />\n"; } // Declare a static query. $sql = "UPDATE contact\n" . "SET first_name = ?\n" . ", middle_name = ?\n" . ", last_name = ?\n" . ", contact_type = ?\n" . "WHERE first_name = ?\n" . "AND middle_name = ?\n" . "AND last_name = ?\n" . "AND contact_type = ?\n"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param( "ssssssss" , $first_name , $middle_name , $last_name , $contact_type , $h_first_name , $h_middle_name , $h_last_name , $h_contact_type); } // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Rollback or undo the transaction. $mysqli->rollback(); // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { /* Manually commiting writes when you have disabled the || default auto commit setting, explained above. || ------------------------------------------------------------ || You would add the following command to commit the || transaction. || ------------------------------ || $mysqli->commit(); || ------------------------------------------------------------ */ $mysqli->commit(); // Close the transaction. $mysqli->close(); } } } |
Line 41 disables auto commit and line 44 starts the transaction. Each statement is managed with the subsequent statement nested inside a block of code that is only reachable when the prior statement succeeds. While this only uses the member and contact table, it could use any number of tables. The natural alternative is building an updatable view.
As always, I hope this helps anybody looking for a code complete example.
Mongo List Databases
After you install MongoDB on your Linux system, you can connect without a user name or password and discover version and many other details about the installation, as shown in this prior post.
You can use the db.adminCommand() to understand the existing databases, as qualified in the online documentation. The basic syntax to list all databases is:
db.adminCommand( {listDatabases:1} ) |
In a generic Linux installation you should see something like the following:
{
"databases" : [
{
"name" : "admin",
"sizeOnDisk" : 32768,
"empty" : false
},
{
"name" : "config",
"sizeOnDisk" : 73728,
"empty" : false
},
{
"name" : "local",
"sizeOnDisk" : 90112,
"empty" : false
},
{
"name" : "test",
"sizeOnDisk" : 49152,
"empty" : false
}
],
"totalSize" : 245760,
"ok" : 1
} |
You can filter for a test database and find only your authorization to use the test database with the following command:
db.adminCommand( {listDatabases:1, filter: {"name": /test/}, authorizedDatabases: true} ) |
It returns:
{
"databases" : [
{
"name" : "test",
"sizeOnDisk" : 49152,
"empty" : false
}
],
"totalSize" : 49152,
"ok" : 1
} |
If you just want the names of your authorized databases, use this syntax:
db.adminCommand( {listDatabases:1, nameOnly: true, authorizedDatabases: true} ) |
It should return:
{
"databases" : [
{
"name" : "admin"
},
{
"name" : "config"
},
{
"name" : "local"
},
{
"name" : "test"
}
],
"ok" : 1
} |
As always, I hope this helps those trying to sort through the official documentation and learn how to do things.
Defrag Collections
One of the problems with Oracle’s Collection is there implementation of lists, which they call object tables. For example, you declare a collection like this:
CREATE OR REPLACE TYPE list IS TABLE OF VARCHAR2(10); / |
A table collection like the LIST table above is always initialized as a densely populated list. However, over time the list’s index may become sparse when an item is deleted from the collection. As a result, you have no guarantee of a dense index when you pass a table collection to a function. That leaves you with one of two options, and they are:
- Manage all collections as if they’re compromised in your PL/SQL blocks that receive a table collection as a parameter.
- Defrag indexes before passing them to other blocks.
The first option works but it means a bit more care must be taken with how your organization develops PL/SQL programs. The second option defrays a collection. It requires that you write a DEFRAG() function for each of your table collections. You should probably put them all in a package to keep track of them.
While one may think the function is as easy as assigning the old table collection to a new table collection, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE OR REPLACE FUNCTION defrag ( sparse LIST ) RETURN LIST IS /* Declare return collection. */ dense LIST := list(); BEGIN /* Mimic an iterator in the loop. */ dense := sparse; /* Return the densely populated collection. */ RETURN dense; END defrag; / |
Line 8 assign the sparse table collection to the dense table collection without any changes in the memory allocation or values of the table collection. Effectively, it does not defrag the contents of the table collection. The following DEFRAG() function does eliminate unused memory and reindexes the table collection:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE OR REPLACE FUNCTION defrag ( sparse LIST ) RETURN LIST IS /* Declare return collection. */ dense LIST := list(); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Mimic an iterator in the loop. */ CURRENT := sparse.FIRST; WHILE NOT (CURRENT > sparse.LAST) LOOP dense.EXTEND; dense(dense.COUNT) := sparse(CURRENT); CURRENT := sparse.NEXT(CURRENT); END LOOP; /* Return the densely populated collection. */ RETURN dense; END defrag; / |
You can test the DEFRAG() function with this anonymous PL/SQL block:
DECLARE /* Declare the collection. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Create a gap in the densely populated index. */ lv_list.DELETE(2); /* Mimic an iterator in the loop. */ CURRENT := lv_list.FIRST; WHILE NOT (CURRENT > lv_list.LAST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; /* Print a line break. */ dbms_output.put_line('----------------------------------------'); /* Call defrag function. */ lv_list := defrag(lv_list); FOR i IN 1..lv_list.COUNT LOOP dbms_output.put_line('['||i||']['||lv_list(i)||']'); END LOOP; END; / |
which prints the before and after state of the defrayed table collection:
[1][Moe] [3][Larry] [4][Curly] ---------------------------------------- [1][Moe] [2][Larry] [3][Curly] |
As always, I hope this helps those trying to sort out a feature of PL/SQL. In this case, it’s a poorly documented feature of the language.
PL/SQL Mimic Iterator
There’s no formal iterator in PL/SQL but you do have the ability of navigating a list or array with Oracle’s Collection API. For example, the following navigates a sparsely indexed collection from the lowest to the highest index value while skipping a missing index value:
DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Declare the collection. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Create a gap in the densely populated index. */ lv_list.DELETE(2); /* Mimic an iterator in the loop. */ CURRENT := lv_list.FIRST; WHILE NOT (CURRENT > lv_list.LAST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
The next one, navigates a sparsely indexed collection from the highest to the lowest index value while skipping a missing index value:
DECLARE /* Create a local table collection. */ TYPE list IS TABLE OF VARCHAR2(10); /* Declare the collection. */ lv_list LIST := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT NUMBER; BEGIN /* Create a gap in the densely populated index. */ lv_list.DELETE(2); /* Mimic an iterator in the loop. */ CURRENT := lv_list.LAST; WHILE NOT (CURRENT < lv_list.FIRST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.PRIOR(CURRENT); END LOOP; END; / |
However, the next example is the most valuable because it applies to a PL/SQL associative array indexed by string values. You should note that the string indexes are organized in ascending order and assigned in the execution section of the program. This differs from the earlier examples where the values are assigned by constructors in the declaration section.
There’s no need to delete an element from the associative array because the string-based indexes are already sparsely constructed. A densely populated character index sequence is possible but not very useful, which is probably why there aren’t any examples of it.
Moreover, the following example is how you navigate a dictionary, which is known as an associative array in Oracle parlance (special words to describe PL/SQL structures). Unfortunately, associative arrays lack any utilities like Python’s key() method for dictionaries.
DECLARE /* Create a local associative array type. */ TYPE list IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10); /* Define a variable of the associative array type. */ lv_list LIST; -- := list('Moe','Shemp','Larry','Curly'); /* Declare a current index variable. */ CURRENT VARCHAR2(5); BEGIN /* Assign values to an associative array (PL/SQL structure). */ lv_list('One') := 'Moe'; lv_list('Two') := 'Shemp'; lv_list('Three') := 'Larry'; lv_list('Four') := 'Curly'; /* Mimic iterator. */ CURRENT := lv_list.FIRST; dbms_output.put_line('Debug '||CURRENT); WHILE NOT (CURRENT < lv_list.LAST) LOOP dbms_output.put_line('['||CURRENT||']['||lv_list(CURRENT)||']'); CURRENT := lv_list.NEXT(CURRENT); END LOOP; END; / |
As always, I hope this example helps somebody solve a real world problem.
What Identifier?
It’s always interesting to see students find the little nuances that SQL*Plus can generate. One of the first things we cover is the concept of calling PL/SQL interactively versus through an embedded call. The easiest and first exercise simply uses an insecure call like:
sqlplus -s student/student @call.sql |
to the call.sql program:
SQL> DECLARE 2 lv_input VARCHAR2(20); 3 BEGIN 4 lv_input := '&1'; 5 dbms_output.put_line('['||lv_input||']'); 6 END; 7 / |
It prints the following to console:
Enter value for 1: machine old 4: lv_input := '&1'; new 4: lv_input := 'machine'; [machine] PL/SQL procedure successfully completed. |
Then, we change the '&1' parameter variable to '&mystery' and retest the program, which prints the following to the console:
Enter value for mystery: machine old 4: lv_input := '&mystery'; new 4: lv_input := 'machine'; [machine] PL/SQL procedure successfully completed. |
After showing a numeric and string input parameter, we remove the quotation from the lv_input input parameter and raise the following error:
Enter value for mystery: machine
old 4: lv_input := &mystery;
new 4: lv_input := machine;
lv_input := machine;
*
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'MACHINE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored |
The point of the exercise is to spell out that the default input value is numeric and that if you pass a string it becomes an identifier in the scope of the program. So, we rewrite the call.sql program file by adding a machine variable, like:
SQL> DECLARE 2 lv_input VARCHAR2(20); 3 machine VARCHAR2(20) := 'Mystery Machine'; 4 BEGIN 5 lv_input := &mystery; 6 dbms_output.put_line('['||lv_input||']'); 7 END; 8 / |
It prints the following:
Enter value for mystery: machine old 5: lv_input := &mystery; new 5: lv_input := machine; [Mystery Machine] PL/SQL procedure successfully completed. |
The parameter name becomes an identifier and maps to the variable machine. That mapping means it prints the value of the machine variable.
While this is what we’d call a terminal use case, it is a fun way to illustrate an odd PL/SQL behavior. As always, I hope its interesting for those who read it.
Subscribe via RSS