In teaching, I had a problem because my students have different base operating systems, like Windows 7, Windows 8, Linux, and Mac OS X. I needed a teaching and lecture platform that would let me teach it all (not to mention support their environments). That meant it had to virtualize any of the following with a portable device:
- Windows 7 or 8 hosting natively an Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Windows 7 or 8 hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Mac OS X hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
- Ubuntu hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
I never considered a manufacturer other than Apple for a laptop since they adopted the Intel chip. Too many of the others sell non-hyperthreaded laptop machines that they market as i5 or i7 64-bit OS machines when they’re not. Some of those vendors disable the hyperthreading facility while others provide motherboards that can’t support hyperthreading. The ones I dislike the most provide a BIOS setting that gives the impression you can enable hyperthreading when you can’t. All Apple devices, MacBook, MacBook Pro, Mac Mini, and Mac Pro do fully support a 64-bit OS and their virtualization.
A MacBook Pro came to mind but the disk space requirements were 1 TB, and that’s too pricey. I went with the Mac Mini because with 16 GB of memory and a 1 TB drive it was only $1,200. Add a wireless keyboard and mighty mouse, and an HDMI splitter and cables for $35, and I had my solution. Naturally, my desktop is a one generation old Mac Pro with 64 GB of memory and 12 TB of disk space, which supports all the virtual machines used for testing. Note to Apple marketing staff: The prior version of the Mac Pro let you pay reasonable (3rd party) prices for the additional memory and disk drives.
The Mac Mini means I can travel anywhere and plug into the console and demo tools and techniques from a myriad set of platforms without the hassle of moving on and off to frequently VM images. It’s a great solution with only one downside, HDMI to DVI sometimes creates purple toned screens. It’s unfortunate because some venues have monitors that don’t support HDMI).
After writing nine books, it’s always great when the author copies arrive. That’s when I know the process is complete. Friday, my twelve copies of the Oracle Database 12c PL/SQL Programming book arrived in two boxes of six each. The book is also available online at Amazon.com.
The book qualifies all the Oracle 12c new SQL and PL/SQL features. I added review sections and mastery questions to each chapter, and expanded examples and techniques. To conserve space and avoid reprinting duplicate code blocks, I adopted line numbers for the code segments so I could provide the technique variations by line numbers for alternate solutions.
You have complete examples on how to white list functions, procedures, packages, and object types with the new
ACCESSIBLE BY clause. Likewise, you’ll learn how to use your PL/SQL variables inside embedded queries.
The improved collection coverage shows you how to write PL/SQL functions that let you use unnested UPDATE statements to add, change, and remove elements from Attribute Data Types (ADTs), and the expanded SQL Primer shows you how to update nested User-Defined Types (UDTs) without having to write PL/SQL. The book also shows you how to export object tables or columns into relational tables for ETL processes.
The Oracle Database Primer grew to include more database administration, multiversion concurrency control, SQL tuning, and SQL tracing. The SQL Primer now contains expanded coverage on data types, Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), SQL queries, joins, and unnesting queries. The SQL Built-in Functions appendix was expanded to enable me to remove side discussions about SQL elements from the PL/SQL chapters. John Harper wrote some wonderful examples of DBMS_COMPARISON, DBMS_CRYPTO, and DBMS_FGA to supplement the PL/SQL Built-in Packages and Types appendix. The Regular Expression Primer was rewritten to make it easier to read and use.
I’ve created an Errata for the Oracle Database 12c PL/SQL Programming. If you buy a copy and find an error, please post a comment in the errata.
Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the
/etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.
You can open a port by adding the following line to the
/etc/sysconfig/iptables file (Fedora’s instructions on editing
iptables). The file won’t exist initially, but you can create it by running the following command as the
root superuser or sudoer:
shell> service iptables save
You you can run the following commands as the
root superuser, which saves the line in the
shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT shell> iptables-save
After making the change to the
/etc/iptables file you can change the firewall by running the following command as the
shell> service iptables restart
Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:
shell> service iptables status shell> service ip6tables status
MySQL Workbench Configuration
- The first thing you need to do is click on the
+symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.
- The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.
- The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button.
- When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.
- Click the VMWare Fedora Instance connection to start a new connection.
- The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.
- Type the following two lines in the Query1 panel (at least if you have a
USE studentdb; SELECT DATABASE();
My students wanted a simple way to cleanup a development schema. So I wrote the following anonymous block PL/SQL program, which also manages the fact that Oracle Database 12c doesn’t completely drop system-generated sequences in active sessions.
The new identity columns in Oracle Database 12c create system-generated sequences, which you must purge from the recycle bin. If you don’t a generic script, like the following raises:
ORA-32794: cannot DROP a system-generated SEQUENCE
Here’s the script that cleans up an Oracle schema:
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
BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects ORDER BY object_type DESC) LOOP /* Drop types in descending order. */ IF i.object_type = 'TYPE' THEN /* Drop type and force operation because dependencies may exist. Oracle 12c also fails to remove object types with dependents in pluggable databases (at least in release 12.1). Type evolution works in container database schemas. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE'; /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints don't prevent the action. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS'; /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated sequence values because dropping the table doesn't automatically remove them from the active session. CRITICAL: Remark out the following when working in Oracle Database 11g. */ EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN /* A system generated LOB column or INDEX will cause a failure in a generic drop of a table because it is listed in the cursor but removed by the drop of its table. This NULL block ensures there is no attempt to drop an implicit LOB data type or index because the dropping the table takes care of it. */ NULL; ELSE /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; /
As noted by Marat, you can simplify the drop of the tables by simply appending a
PURGE clause to the
DROP TABLE statement.
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
/* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints but you need to purge system-generated constraints. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE'; ELSE /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; /
As always, I hope this helps a few people.
I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the
sudoers list, you should check this older and recently updated blog post.
- Download the MySQL Yum Repository and launch the downloaded RPM.
- Install MySQL on Fedora, Version 20, which you can find with the following command:
shell> rpm -qa | grep mysql mysql-community-release-fc20-5.noarch
fc20-5 changes with point releases, but assuming that you’re installing the
shell> sudo yum localinstall mysql-community-release-fc20-5.noarch
- Install MySQL on Fedora with the following command:
shell> sudo yum install mysql-server
- Start the MySQL service on Fedora with the following command:
shell> sudo service mysqld start
- Secure the MySQL installation with the following command:
- Set the MySQL Service to start with the Fedora operating system with the following command (not
shell> sudo systemctl enable mysqld.service
Restart the Fedora operating system to effect the changes.
- Reset the MySQL configuration file to enable external connections through Port 3306 with the following changes to the my:
Remark out the
socket line, like this:
port lines below after you know the actual IP address of the server to the
my.cnf file in the
You substitute the actual IP address for the
nnn.nnn.nnn.nnn on the
bind_address line with the actual IP address returned by the
ifconfig command, like this:
Then, add these two lines to the my.cnf file.
- Restart the mysqld service with the following syntax:
shell> sudo service mysqld restart
You can check whether MySQL is listening on Port 3306 with this syntax:
shell> sudo netstat –anp | grep 3306
Oracle Database 12c provides a limit syntax in SQL for a query with the following clause:
[OFFSET n ROWS] FETCH FIRST m ROWS ONLY
Unfortunately, it can’t be used dynamically like this in a stored function or procedure:
CURSOR dynamic_cursor ( cv_offset NUMBER , cv_rows NUMBER ) IS SELECT i.item_title FROM item i OFFSET cv_offset ROWS FETCH FIRST cv_rows ROWS ONLY;
If you attempt it, you would raise the following error:
CREATE OR REPLACE FUNCTION dynamic_range * ERROR at line 1: ORA-03113: end-of-file ON communication channel Process ID: 4516 SESSION ID: 78 Serial NUMBER: 4467
The easy solution is to simply write it as a function returning an ADT (Attribute Data Type) collection like:
1 2 3
CREATE OR REPLACE TYPE item_title_table AS TABLE OF VARCHAR2(60); /
dynamic_range function returns a collection with a dynamic range bound to the limiting clause:
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
CREATE OR REPLACE FUNCTION dynamic_range ( pv_offset NUMBER , pv_rows NUMBER ) RETURN item_title_table IS /* Declare a collection type. */ lv_item_title_table ITEM_TITLE_TABLE := item_title_table(); /* Local variable length string. */ lv_item_title VARCHAR2(60); /* Declare a local counter. */ lv_counter NUMBER := 1; /* Local NDS statement and cursor variables. */ lv_stmt VARCHAR2(2000); lv_cursor SYS_REFCURSOR; BEGIN /* Assigned a dynamic SQL statement to local variable. */ lv_stmt := 'SELECT i.item_title'||CHR(10) || 'FROM item i'||CHR(10) || 'OFFSET :bv_offset ROWS FETCH FIRST :bv_rows ROWS ONLY'; /* Open cursor for dynamic DNS statement. */ OPEN lv_cursor FOR lv_stmt USING pv_offset, pv_rows; LOOP /* Fetch element from cursor and assign to local variable. */ FETCH lv_cursor INTO lv_item_title; /* Exit when no more record found. */ EXIT WHEN lv_cursor%NOTFOUND; /* Extend space, assign a value, and increment counter. */ lv_item_title_table.EXTEND; lv_item_title_table(lv_counter) := lv_item_title; lv_counter := lv_counter + 1; END LOOP; /* Close cursor. */ CLOSE lv_cursor; /* Return collection. */ RETURN lv_item_title_table; END; /
By using, the following query:
SELECT COLUMN_VALUE AS item_title FROM TABLE(dynamic_range(2,5));
Hope this helps anybody who wants to make the limiting clause dynamic. You can find out how to embed it in PHP in Chapter 2 of the Oracle Database 12c PL/SQL Programming.
I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.
I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.
Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.
Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an
OUT-only mode parameter from a procedure.
That’s a great question, and it’s actually simple once you understand the difference between Oracle object types and other data types. Oracle object types must always be initiated before you use them, which means you must initialize any
OUT-only mode parameters at the top of your execution section, like this:
1 2 3 4 5 6 7 8 9 10 11 12
CREATE OR REPLACE PROCEDURE reset_troll ( pv_troll OUT TROLL_OBJECT ) IS /* Troll default name. */ lv_troll_name VARCHAR2(20) := 'Bert'; BEGIN /* Initialize the incoming parameter by allocating memory to it. */ pv_troll := troll_object(); /* Set the name to something other than the 'Tom' default value. */ pv_troll.set_troll(lv_troll_name); END reset_troll; /
Line 7 shows you the trick, initialize the incoming parameter because there isn’t an incoming parameter for an
OUT-only mode parameter. The calling parameter to an
OUT-only mode parameter is only a reference where PL/SQL will copy the internal object reference. While the calling parameter has been initialized, the reference to the call parameter’s object is where the internal object will be copied. The local program must first ensure a new memory location for a new instance of the object type before it can act on or return an object instance to the external reference. More or less, the internal object is copied to the calling object instance’s memory location when the procedure completes its execution.
Here’s the source code for the
troll_object object type and body:
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
CREATE OR REPLACE TYPE troll_object IS OBJECT ( troll VARCHAR2(20) , CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION troll_object ( troll VARCHAR2 ) RETURN SELF AS RESULT , MEMBER FUNCTION get_troll RETURN VARCHAR2 , MEMBER PROCEDURE set_troll (troll VARCHAR2) , MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / CREATE OR REPLACE TYPE BODY troll_object IS /* Default no-argument constructor. */ CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT IS troll TROLL_OBJECT := troll_object('Tom'); BEGIN SELF := troll; RETURN; END troll_object; /* Single argument constructor. */ CONSTRUCTOR FUNCTION troll_object (troll VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.troll := troll; RETURN; END troll_object; /* A getter function. */ MEMBER FUNCTION get_troll RETURN VARCHAR2 IS BEGIN RETURN SELF.troll; END get_troll; /* A setter procedure. */ MEMBER PROCEDURE set_troll (troll VARCHAR2) IS BEGIN SELF.troll := troll; END set_troll; /* A function that returns the formatted object type's contents. */ MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN 'Hello '||SELF.troll; END to_string; END; /
You can test the reset_troll procedure with the following anonymous block:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
/* Enable printing from a PL/SQL block. */ SET SERVEROUTPUT ON SIZE UNLIMITED /* Anonymous testing block. */ DECLARE lv_troll TROLL_OBJECT := troll_object('Bill'); BEGIN dbms_output.put_line('--------------------'); /* Prints 'Hello William' */ dbms_output.put_line(lv_troll.to_string()); dbms_output.put_line('--------------------'); reset_troll(lv_troll); /* Prints 'Hello Bert' */ dbms_output.put_line(lv_troll.to_string()); dbms_output.put_line('--------------------'); END; /
If you remark out line 7 from the reset_troll procedure, you’d raise the following exception by the call on line 10 because the local object hasn’t been instantiated (given life). It means there’s no memory location allocated for the instantiated (instance of an object type).
-------------------- Hello Bill -------------------- DECLARE * ERROR at line 1: ORA-30625: method dispatch ON NULL SELF argument IS disallowed ORA-06512: at "VIDEO.RESET_TROLL", line 10 ORA-06512: at line 8
Hope this helps those trying to solve the same problem.
A neat feature of Oracle Database 12c is the ability to put PL/SQL functions inside SQL
WITH statements. It’s covered in Chapter 2 on new SQL and PL/SQL features of the Oracle Database 12c PL/SQL Programming. There’s a trick though, you must disable the
SQLTERMINATOR before creating the statement or accessing it, like:
SET SQLTERMINATOR OFF
Then, you can write a
WITH statement like this:
WITH FUNCTION glue ( pv_first_name VARCHAR2 , pv_last_name VARCHAR2) RETURN VARCHAR2 IS lv_full_name VARCHAR2(100); BEGIN lv_full_name := pv_first_name || ' ' || pv_last_name; RETURN lv_full_name; END; SELECT glue(a.first_name,a.last_name) AS person FROM actor a /
Unfortunately, you need to include it in a view to make the
WITH statement useful, like:
CREATE OR REPLACE VIEW actor_v AS WITH FUNCTION glue ( pv_first_name VARCHAR2 , pv_last_name VARCHAR2) RETURN VARCHAR2 IS lv_full_name VARCHAR2(100); BEGIN lv_full_name := pv_first_name || ' ' || pv_last_name; RETURN lv_full_name; END; SELECT glue(a.first_name,a.last_name) AS person FROM actor a /
Hope this helps those trying to use the feature.
Somebody asked how to create a
SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.
The following SQL statements create two databases and grant appropriate privileges to the
student as the
/* Create two databases. */ CREATE DATABASE seussdb; CREATE DATABASE appdb; /* Grant privileges to a student user. */ GRANT ALL ON seussdb.* TO student; GRANT ALL ON appdb.* TO student;
Log out from the
root superuser and reconnect as the
student user. Then, the following code connects to the
seuss database and create a
hat table; and inserts two rows into the
/* Use the appdb database. */ USE seussdb; /* Create a hat table. */ CREATE TABLE hat ( hat_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , hat_text VARCHAR(20)); /* Insert two rows into the hat table. */ INSERT INTO hat (hat_text) VALUES ('Thing 1'); INSERT INTO hat (hat_text) VALUES ('Thing 2');
The following code connects to the
application database and creates a
hat view; and then the code inserts one additional row into the
/* Connect to the application database. */ USE appdb; /* Create a hat view. */ CREATE VIEW hat AS SELECT * FROM seussdb.hat; /* Query the contents of the view, or seuss.hat table. */ SELECT * FROM hat; /* Insert a new row into the hat table. */ INSERT INTO hat (hat_text) VALUES ('Thing 3'); /* Query the contents of the view, after insert to the view. */ SELECT * FROM hat;
The results will be the following:
+--------+----------+ | hat_id | hat_text | +--------+----------+ | 1 | Thing 1 | | 2 | Thing 2 | | 3 | Thing 3 | +--------+----------+
I hope this answers the question on how to mimic the Oracle database’s synonyms. The
appdb.hat view acts as a synonym to the