MacLochlainns Weblog

Here are step-by-step instructions for installing Oracle Database 12c Release 1 on Windows 7. This is more or less to install a Desktop version of Oracle 12c. I posted other versions for Oracle Database 11gR1 and 11gR2. While some report that they installation doesn’t work for them, its typically because they didn’t configured their Windows 7 environment.

Here are some Windows 7 configuration caveats before you perform this installation, and they are:

Windows 7 Configuration Steps

1. Make sure you have at least 3 GB of memory on your Windows PC, or that you can allocate 4 GB of memory to your virtual machine for Windows.
2. Install Oracle’s JDK 7 for 64-bit OS with NetBeans and 32-bit OS when running on a 64-bit machine. The installer is smart enough to only install NetBeans once. The JDK 7 32-bit installation supports SQL Developer.
3. Disable Microsoft’s User Access Controls (UAC). I blogged about a set of Windows 7 gripes, and the second point shows you the screen shots that let you disable UAC on Windows 7.
4. Configure your C:\Windows\System32\drivers\etc\hosts file. Use lines 1 through 3 when you’re using a DHCP IP address, and lines 1 through 4 when you’re using a static IP address. If you want to set a static IP address, check this old post on how to set a static IP address. Please note that the hostname needs to be lowercase.

1
2
3

127.0.0.1      localhost
::1            localhost
127.0.0.1      oracle12c oracle12c.techtinker.com

Line 4, only use for a static IP address, would look like this on the 192.168.* subdomain:

4

192.168.0.5      oracle12c oracle12c.techtinker.com

5. Create a user account name that doesn’t have a white space, like McLaughlinM in the screen shots, and assign it Administrator privileges. During the install, you’ll be prompted to create a non-administrator account to start Oracle.

Oracle Database 12c Release 1 Installation Steps

1. The first thing you need to do is unzip the two zip files into a common directory. I’ve chosen to create an OracleInstall folder. Oracle creates a database folder inside it. Within the database folder, click the Setup file to launch the Oracle Installer.

2. After launching the Oracle Installer, the Configure Security Updates screen is your first stop. You may provide your email (attached to your Oracle Support Contract) and Oracle Support password, or uncheck the box and you can simply install a Desktop test environment. Click the Next button to proceed.

3. The Software Updates is the next screen. If you want to check for updates, click and enter your Oracle Support credentials. If you want to install the base release, click the Skip software updates radio button. After making a choice about what you want to do with updates, click the Next button to proceed with the install.

4. The Installation Option screen lets you chose whether you want to Create and configure a database (sample database), Intall database software only, or Upgrade an existing database. Check the appropriate radio button and then click the Next button to proceed with the install.

5. The System Class screen lets you chose whether you want to install a Desktop class (ideal for develoers to play around in) or a Server class. Check the appropriate radio button and then click the Next button to proceed with the install.

6. The Oracle Home User Selection screen lets you chose whether you want to Use Existing Windows User (that’s fine if you created one previously), Create New Windows User (what I’ll do next), or Use Windows Built-in Account. Check the appropriate radio button and then click the Next button to proceed with the install.

7. The Oracle Home User Selection screen lets you Create New Windows User, and that’s what I’ve done with the oracle user (it could be whatever you like). Click the Next button to proceed with the install.

8. The Typical Install Configuration screen lets you customize your installation. I’ve only opted to provide an Oracle compliant password while accepting the defaults. Click the Next button to proceed with the install.

9. The Perform Prerequisite Checks screen initially displays a task bar. You need some patience, it’ll show you the next screen if everything is fine.

10. The Summary screen tells you what you’re going to install. Read it over, save a copy for later, and when everything is right then click the Next button to install.

11. The Install Product screen tells you what you’re installing and it can take some time. Don’t walk away too quickly because you’re most likely going to have to allow access for the installation to complete successfully.

12. You should see two Windows Security Alerts with the installation. Click the Allow access button to continue successfully.

13. The Database Configuration Assistant screen tells you that you’ve been successful to this point. Although, this is where several errors can occur when you failed to correctly configure Windows 7 before installation. This takes some time to run, here’s where you can take a break.

14. The second Database Configuration Assistant screen lets you configure passwords for the database accounts. I’m skipping that by accepting the defaults and clicking the OK button to proceed.

15. The Install Product screen reappears while most of the database cloning operation has finished. At this point, it starts configuring the Oracle Enterprise Manager (OEM). It can take a couple minutes to complete. Simply monitor it.

15. The Finish screen appears after everything has worked. Click the Close button to finish the install.

16. Now, you can open a Windows command shell and call the sqlplus executable with a /nolog option (denies login credentials form OS history files). Then, you can connect using the TNS orcl alias, which verifies your networking listener.ora and tnsnames.ora files are setup correctly. You should see the following in the command shell when the installation was successful.

C:\Users\mclaughlinm>sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 02:00:19 2013
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> connect system@orcl
Enter password:
Connected.

Oracle 12c changes the dynamics on users. You now have two types of databases. One is a Container Database (CDB) and the other is a Pluggable Database (PDB). While Oracle’s default container users, like SYS, SYSTEM, and so forth, remain unchanged, you create new CDB users with a C##<user_name> preface. PDB users are a different matter, and they can retain the same format used previously for user-defined users. You set PDB users as the ADMIN user for a pluggable database when you provision it.

Subsequent to this post, I’ve posted how you can provision an Oracle 12c Pluggable Database (PDB), and you can find it in this post. AS always, I hope that this helps those trying to find a quick shortcut on reading the instructions. 😉

I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.

Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.

A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.

Missing the MySQL Connect 2013 Bus

Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). 🙁 It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47
Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48
Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69

Turns out Zend 6 doesn’t automatically set the [Date] elements in the php.ini file, which is required for the oci_set_client_info(), oci_set_module_name(), and oci_set_action() functions of the OCI. You can find the php.ini file in the C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
 
; http://php.net/date.default-latitude
;date.default_latitude = 31.7667
 
; http://php.net/date.default-longitude
;date.default_longitude = 35.2333
 
; http://php.net/date.sunrise-zenith
;date.sunrise_zenith = 90.583333
 
; http://php.net/date.sunset-zenith
;date.sunset_zenith = 90.583333

You can find the values for date.timezone here. Update the date.timezone as follows:

date.timezone = America/Denver

Then, reboot the Zend Server, and it fixes the warning messages.

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

1
2
3
4

CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1
2

CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

Lastly, you create an object table function, like:

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

CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3

CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).

Anyway, let me quote the rules:

Oracle recommends that the password you specify:

• Contains at least one lowercase letter.
• Contains at least one uppercase letter.
• Contains at least one digit.
• Is at least 8 characters in length.
• Uses the database character set which can include the underscore (_), dollar ($), and pound sign (#) character.
• If (the password) contains special characters, including beginning the password with a number or symbol, then enclose the password with double-quotation marks.
• Should not be an actual word.

Likewise, you can’t use the old educational passwords:

• The SYS account password cannot be change_on_install (case-insensitive).
• The SYSTEM account password cannot be manager (case-insensitive).
• The SYSMAN account password cannot be sysman (case-insensitive).
• The DBSNMP account password cannot be dbsnmp (case-insensitive).
• If you choose to use the same password for all the accounts, then that password cannot be change_on_install, manager, sysman, or dbsnmp (case-insensitive).

Hope this helps, and by the way reading the documentation never hurts too much! 🙂

My take on the good, the bad, and the ugly of our Winter season, beside all the snow I’ve shoveled and blown to get in and out of my driveway, is:

The Good?

February brought us MySQL 5.6 GA (General Availability) and Ruby 2.0.0-p0. Mega thanks to the MySQL and Ruby teams! By the way, if you’ve always wanted to know a little about Ruby without a great deal of effort, you should check this awesome little 20 minute Ruby tutorial. Naturally, the best Ruby Programming book is a bit dated, and maybe there will be a new one later in the year. If anybody knows whether there’s a new edition forthcoming, I’d appreciate hearing about it! Another more current, Ruby 1.9, book is Programming Ruby 1.9: The Pragmatic Programmers’ Guide.

The Bad?

It seems like a lot of folks who implemented Microsoft SharePoint are rapidly preparing to migrate from SharePoint 2010 to 2013, but what’s unclear is whether they’re upgrading for the features or bug fixes. It does seem many are opting for the Microsoft cloud services, and rumor has it that’s because a little collaboration requires a lot of hardware in the data center. It’s probably a good idea to do some research before implementing Microsoft SharePoint.

The Ugly?

If NBC got the quote right today, President Obama disappointed me by saying, “… I should somehow do a Jedi mind meld with these (republican) folks …” How can the President not know that Star Trek Vulcan’s aren’t in the Star Wars universe? Worse yet, my disappointment with Peter Jackson returns because he is releasing the theatrical version The Hobbit: An Unexpected Journey Bluray and DVD on March 19^th and then an extended version later in the year. Ouch!!! He’s getting into my wallet again.

Spring starts later this month and the rumor is that Oracle will release Oracle Database 12c soon. Then, we get Iron Man 3, Star Trek into Darkness, and the Lone Ranger, along with warm weather.

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT   m.account_number
,        m.member_type         -- A fk to common_lookup table.
,        m.credit_card_number
,        m.credit_card_type    -- A fk to common_lookup table.
,        c.first_name
,        c.middle_name
,        c.last_name
,        c.contact_type        -- A fk to common_lookup table.
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

It returns the results on the left, while a set of joins against the common_lookup table returns the results on the right (both use the \G in SQL Monitor to display the data vertically).

The member_type, credit_card_type, and contact_type columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup table is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT   m.account_number
,        cl1.common_lookup_meaning
,        m.credit_card_number
,        cl2.common_lookup_meaning
,        c.first_name
,        c.middle_name
,        c.last_name
,        cl3.common_lookup_meaning
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN common_lookup cl1
ON       cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2
ON       cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3
ON       cl3.common_lookup_id = c.contact_type
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):

The f(x) represents a generic function call where the x substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id) and the other holds the customer facing description (from the common_lookup_meaning column).

These steps describe the process:

1. Use the natural key (the user name) to find the data in the contact table.
2. Use the member_id foreign key column in the contact table to link to the same name column primary key in the member table by joining the two rows.
3. Use the foreign keys in the new row (combining columns from the contact and member tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.

The vertical choices displayed above map to OPTION tag elements of an HTML SELECT tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION tag, and it holds a null as the value of the VALUE attribute for the OPTION tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup function below.

Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.

You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.

Each of the drop downs contains the full selection of possibilities from the common_lookup table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:

The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:

As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup function. The function lets you find an existing value or returns a set of unique values for you to choose from.

You should rename the following credentials.php file as MySQLCredentials.inc for it to work in the file below it.

Assuming you’ve implemented the credentials.php file as the MySQLCredentials.inc file, you can now implement the following file. The get_common_lookup function returns a <SELECT> tag with a list embedded of <OPTION> tags with values; one <OPTION> tag is selected when the foreign key matches a valid primary key value in the common_lookup table; and no <OPTION> tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.

The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup function (on lines 104, 111, and 126). Each call to the get_common_lookup function selects the list of possible values and highlights the value associated with the foreign key value.

Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.

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
130
131
132
133
134
135
136
137
138
139
140

<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
  /* Class tag element styles. */
  .box {border:1px solid;padding:0px;width:392px;background-color:silver;}
  .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;}
  .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;}
  .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;}
  .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;}
  .clear {clear:left;}
  .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;}
  .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;}
  .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;}
  .formInput {min-width:150px;margin:5px;text-align:left;}
  .formShortInput {width:80px;margin:5px;text-align:left;}
  .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;}
  .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;}
</style>
</header>
<body>
<?php
 
  // Include libraries.
  include_once("MySQLCredentials.inc");
  include_once("library.inc");
 
  // Define a HTML page string.
  $out = '';
 
  // Declare input variables.
  $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry";
  $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James";
  $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter";
 
  // Declare output variables.
  $member_account_number = null;
  $credit_card_number = null;
 
  // Declare lookup input and output (initialized as nulls to suppress warnings) variables.  
  $member_table = 'member';
  $member_type = 'member_type';
  $member_type_id = null;
  $credit_card_type = 'credit_card_type';
  $credit_card_type_id = null;
  $contact_table = 'contact';
  $contact_type = 'contact_type';
  $contact_type_id = null;
 
  // 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();
 
    // Declare a static query.
    $sql = "SELECT   m.account_number\n"
         . ",        m.member_type\n"
         . ",        m.credit_card_number\n"
         . ",        m.credit_card_type\n"
         . ",        c.first_name\n"
         . ",        c.middle_name\n"
         . ",        c.last_name\n"
         . ",        c.contact_type\n"
         . "FROM     member m INNER JOIN contact c\n"
         . "ON       m.member_id = c.member_id\n"
         . "WHERE    c.first_name = ?\n"
         . "AND      c.middle_name = ?\n"
         . "AND      c.last_name = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      // This query only returns one row, and an empty block follows the while logic.
      $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id);
      while ($stmt->fetch()) {}
    }
  }
 
  // Print the query form.
  $out .= '<form method="post" name="myForm" action="submitItemType.php">';
  $out .= '<label class="title1">Membership Account Information</label><br />';
  $out .= '<div class="box">';
  $out .= '<div class="topBox">';
  $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />';
  $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($member_table, $member_type, $member_type_id);
 
  $out .= '</select><br />';
  $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />';
  $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 
  // Get dynamic credit card type lookup string fragment.
  $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id);
 
  // Print the closing HTML table tag.
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="middleBox">';
  $out .= '<label class="formDownLabel">&nbsp;</label>';
  $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />';
  $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />';
  $out .= '<label class="formDownLabel">Type</label>';
  $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id);
 
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="bottomBox">';
  $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">';
  $out .= '</div>';
  $out .= '</form>';
  $out .= '</body>';
  $out .= '</html>';
 
  print $out;
?>
</body>
</html>

The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:

1
2
3
4
5
6
7
8
9
10
11
12

<html>
<head>
</head>
<body>
<?php
  // Print the surrogate key values for the common_lookup_id column.
  print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />";
  print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />";
  print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />";
?>
</body>
</html>

I hope this helps those who are interested in sorting how to implement a common_lookup table.

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7^th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …

A few years ago, I gave a session on Oracle’s Data Vault. An attendee from that session and I happened to be seated at the same table for lunch last Thursday when I presented at Utah Oracle User Group Fall Symposium. He asked if I’d ever found a way to encrypt a column from the prying eyes of the SYS user. I said yes, and he asked how. It was a bit more than could be explained at lunch and promised to put it on the blog. (Disclaimer: It is possible to unwrap wrapped code but the mechanics change with each release and they require access to the SYS schema and substantial Application DBA knowledge; however, there are scripts published on the Internet to unwrap the code. There are also other vulnerabilities in the example, but I’ll pass on exposing them. After all this was written to illustrate an approach.)

It’s important to note you can now hide columns in Oracle Database 12c, but they’re still visible to the DBA-level staff. That’s why I’ll publish a new article on re-writing this encrypted object as a Java library, and disclose how to hide password exchanges from the SGA area.

This demonstrates how you can encapsulate a column from even the SYS user. I also put it into Appendix D, PL/SQL Built-in Packages and Types to illustrate the DBMS_CRYPTO package. It uses a User Defined Type (UDT) and assumes you have a working knowledge of object types in Oracle 10g forward. If not, you can find them in:

1. Chapter 11 of my Oracle Database 12c PL/SQL Programming book.
2. Chapter 14 of my Oracle Database 11g PL/SQL Programming book.

It also assumes basic knowledge of Oracle’s encryption technology, some of which is explained in the new Oracle Database 12c PL/SQL Programming book.

For reference, a good DBA would simply find this clear text password in the SGA. A more secure approach might be hiding the encryption keyword in a one column and table or embedded in some string within a common lookup table column value as a position specific substring. Alas, I don’t have time to write something so elaborate.

The steps are:

1. You must create a user defined type (UDT), which sets the up a single salary column.

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE TYPE masked IS OBJECT
( salary  RAW(1000)
, CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION masked ( salary  NUMBER ) RETURN SELF AS RESULT
, MEMBER FUNCTION get_raw_salary RETURN RAW
, MEMBER FUNCTION get_salary ( KEY VARCHAR2 ) RETURN NUMBER
, MEMBER PROCEDURE to_string
, ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER )
INSTANTIABLE FINAL;
/

2. As the SYS user, you must grant EXECUTE privilege on the DBMS_CRYPTO package to the target SCHEMA user.

1

GRANT EXECUTE ON dbms_crypto TO schema_name;

3. You implement the MASKED UDT, as follows below (source unencrypted, don’t worry I show you how to encrypt [wrap] it in a moment).

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

CREATE OR REPLACE TYPE BODY masked IS
 
  CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS
 
    /* Create a placeholder for a zero salary, like an empty_clob() call. */
    zero MASKED := masked(0);
 
  BEGIN
 
    /* Assign an encrypted zero salary to the instance. */
    self := zero;  
    RETURN;
 
  END masked;
  CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_key             RAW(1000);
    lv_raw             RAW(1000);
    lv_encrypted_data  RAW(1000);
 
  BEGIN
    /* Dynamic assignment. */
    lv_raw := UTL_RAW.cast_to_raw(NVL(salary,0));
 
    /* Convert to a RAW 64-character key. */
    lv_key := UTL_RAW.cast_to_raw(lv_key_string);
    lv_key := RPAD(lv_key,64,'0');   
 
    /* Encrypt the salary before assigning it to the object type attribute */
    lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key);
    self.salary := lv_encrypted_data;
 
    RETURN;
  END masked;
 
  MEMBER FUNCTION get_raw_salary RETURN RAW IS 
  BEGIN
    RETURN self.salary;
  END get_raw_salary;    
 
  MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS 
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_decrypted_data  RAW(4000);
    lv_key             RAW(1000);
    lv_return_value    NUMBER;
 
  BEGIN
 
    /* Verify key value matches local value before decrypting, substitute
       a zero value when the key doesn't match. */
    IF key = lv_key_string THEN
      lv_key := UTL_RAW.cast_to_raw(lv_key_string);
      lv_key := RPAD(lv_key,64,'0');   
      lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key);
      lv_return_value := TO_NUMBER(TO_CHAR(UTL_RAW.cast_to_number(lv_decrypted_data),'9999990.00'));
    ELSE
      lv_return_value := 0;    
    END IF;
 
    RETURN lv_return_value;
  END get_salary;
 
  ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS
 
    /* Declare local variables for encryption, object types hold instance
       objects and object body variables are method specific. Placing them
       inside the methods while tedious prevents their disclosure. */
    lv_key_string      VARCHAR2(4000)  := 'Encrypt Me!'; 
    lv_decrypted_self  RAW(4000);
    lv_decrypted_peer  RAW(4000);
    lv_key             RAW(1000);
 
  BEGIN
 
    /* Decrypt the current and peer object attribute values before
       comparing their values. */  
    lv_key := UTL_RAW.cast_to_raw(lv_key_string);
    lv_key := RPAD(lv_key,64,'0');   
    lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key);
    lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key);
 
    /* Sort order key. */
    IF lv_decrypted_self < lv_decrypted_peer THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
 
  END equals;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    /* Provide a to_string method for good practice. */
    DBMS_OUTPUT.put_line('Encrypted value');
  END to_string;
END;
/

4. You implement the MASKED UDT encrypted by using the DBMS_DDL package, as follows below. This ensures that others can’t read the source code by querying the ALL_, DBA_, or USER_SOURCE views. You should note that I’ve removed comments and unnecessary spaces.

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

BEGIN
  DBMS_DDL.create_wrapped(
     'CREATE OR REPLACE TYPE BODY masked IS '
  || 'CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS '
  || 'zero MASKED := masked(0); '
  || 'BEGIN '
  || 'self := zero; '
  || 'RETURN; '
  || 'END masked; '
  || 'CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_key             RAW(1000); '
  || 'lv_raw             RAW(1000) := RPAD(utl_raw.cast_from_number(salary),32,''0''); '
  || 'lv_encrypted_data  RAW (1000); '
  || 'BEGIN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string); '
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); '
  || 'self.salary := lv_encrypted_data; '
  || 'RETURN; '
  || 'END masked; '
  || 'MEMBER FUNCTION get_raw_salary RETURN RAW IS '
  || 'BEGIN '
  || 'RETURN self.salary; '
  || 'END get_raw_salary; '
  || 'MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_decrypted_data  RAW(4000); '
  || 'lv_key             RAW(1000); '
  || 'lv_return_value    NUMBER; '
  || 'BEGIN '
  || 'IF key = lv_key_string THEN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string); '
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); '
  || 'lv_return_value := TO_NUMBER(TO_CHAR(utl_raw.cast_to_number(lv_decrypted_data),''9999990.00'')); '
  || 'ELSE '
  || 'lv_return_value := 0; '
  || 'END IF; '
  || 'RETURN lv_return_value; '
  || 'END get_salary; '
  || 'ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS '
  || 'lv_key_string      VARCHAR2(4000)  := ''Encrypt Me!''; '
  || 'lv_decrypted_self  RAW(4000); '
  || 'lv_decrypted_peer  RAW(4000); '
  || 'lv_key             RAW(1000); '
  || 'BEGIN '
  || 'lv_key := utl_raw.cast_to_raw(lv_key_string);'
  || 'lv_key := RPAD(lv_key,64,''0''); '
  || 'lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); '
  || 'lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); '
  || 'IF lv_decrypted_self < lv_decrypted_peer THEN '
  || 'RETURN 1; '
  || 'ELSE '
  || 'RETURN 0; '
  || 'END IF; '
  || 'END equals; '
  || 'MEMBER PROCEDURE to_string IS '
  || 'BEGIN '
  || 'dbms_output.put_line(''Encrypted value''); '
  || 'END to_string; '
  || 'END; ');
END;
/

5. You can test a single instance with this anonymous PL/SQL block.

1
2
3
4
5
6
7
8
9

DECLARE
  o MASKED := masked(82000.12);
BEGIN
  DBMS_OUTPUT.put('Override:  ');
  o.to_string();
  DBMS_OUTPUT.put_line('Decrypted: '||o.get_salary('Encrypt Me!'));
  DBMS_OUTPUT.put_line('Bad Key:   '||o.get_salary('Incorrect'));
END;
/

Override:  Encrypted value
Decrypted: 82000.12
Bad Key:   0

6. You can test a series of instances by making them persistent objects, or columns in a table, and then query values from them. It also lets you you test the sorting feature provided in the UDT.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE sort_demo (salary MASKED);
 
INSERT INTO sort_demo VALUES (masked(82000.24));
INSERT INTO sort_demo VALUES (masked(61000.12));
INSERT INTO sort_demo VALUES (masked(93000.36));
 
SELECT salary AS "Encrypted" FROM sort_demo;
 
COLUMN unordered FORMAT 9,999,990.00 HEADING "Unordered|List"
 
SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Unordered
FROM   sort_demo;
 
COLUMN ordered FORMAT 9,999,990.00 HEADING "Ordered|List"
 
SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Ordered
FROM   sort_demo
ORDER BY 1;

Encrypted(SALARY)
--------------------------------------------------------------------------
MASKED('1798C04F8342C53A826144568075CBDB145D0C9BD226B410C8C7167B76382D86')
MASKED('82F783F2E117AA60955B0A2E73545506936D6F8FFBEC5D9E0D8E70B82D4B694D')
MASKED('1551F350AAEB30ADFC1527F25CAA935732243858AF1C5D724A78B997A4394EAD')
 
    Unordered
         List
-------------
    82,000.24
    61,000.12
    93,000.36
 
 
      Ordered
         List
-------------
    61,000.12
    82,000.24
    93,000.36

If you want to retest this, make sure you drop the SORT_DEMO table first. Hope this helps any interested in an elaborate solution.