MacLochlainns Weblog

Finally, I finished writing the MySQL Workbench book. It’ll be available next spring. Now it’s time to leave for the plane, fly to San Francisco, and see everyone at MySQL Connect.

I look forward to meeting folks, I’ll be presenting after MySQL Connect for those staying for Oracle Open World. My presentation is at Oracle Develop on Monday, 10/1/12 from 16:45 – 17:45, in the Marriott Marquis – Foothill F. As I mentioned in an earlier post, you can probably catch me in Moscone West at the bookstore. The publisher requests we attend book signings. 😉

Unfortunately as a speaker I need to convert my Keynote to Powerpoint, and had to purchase, install, and update Microsoft Office 2011 on my Mac. Open Office and Keynote weren’t on the approved list, alas …

Update: The book published 4/9/2013 (a bit of a delay from completing the write, eh?). It’s available on Safari as of yesterday.

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.

Heading out to speak on Comparative SQL between Oracle and MySQL at the Utah Oracle User’s Group Fall Symposium. I’ll look forward to meeting old and new friends. Also, I’ve got a couple promotional copies of my Oracle Database 11g and MySQL 5.6 Developer Handbook to give out at the end of the session.

I’m also presenting Monday evening at Oracle Develop during Oracle Open World 2012. I’ll also have copies of the Oracle Database 11g PL/SQL Programming and Oracle Database 11g PL/SQL Programming Workbook books to give away at the end of the session. Naturally, McGraw-Hill wants me to stop by the bookstore for a signing. If you have time and are in Moscone West, stop by and visit.

Naturally, I’m also attending MySQL Connect to see the brilliant sessions being presented. Hope to see you in San Francisco! 🙂

It’s amazing to me that there is yet another outcome from installing Oracle Database 11g on Windows 7. This one installs without an error but then raises the following error message dialog when you try to launch SQL Developer from the menu. Naturally, it seems to depend on having more than one Java JVM installed when you run the Oracle Universal Installer (OUI) without setting the %JAVA_HOME% environment variable.

Text of error message from image:

Windows is searching for SQLSERVER.BAT. To located the file yourself, click Browse.

While the installation release notes (E10842-02) that download or ship with the product don’t mention the %JAVA_HOME% or %DEV_TOOLS% environment variables, it appears you should set them before the installation. You can discover this by double clicking on the SQL Developer menu option, and then choose Properties. You should see the following in the dialog.

Windows 7 searches for the sqldeveloper.bat file when the OUI installation fails to configure the SetJavaHome parameter in the sqldeveloper.conf file. OUI does configure the SetJavaHome parameter when OUI resolves the JVM address.

You should put this in the Target field:

C:\app\<user_name>\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe

Although, that may be a simplifying assumption of what causes the problem, I didn’t go through all the Oracle Universal Installer (OUI) background tasks to discover the actual code element. Also, I didn’t take the time to retest by setting those parameters. I solved this problem by replacing the sqldeveloper.bat value with a sqldeveloperw.exe value in the menu link, applying the change, and saving it. If you’ve a better solution, please share it.

The configuration file is found here:

C:\> app\<user_name>\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

While you can provide a sqldeveloper.bat file, it’s much simpler to simply put the JVM physical address in the sqldeveloper.conf file. That’s what I did to solve the problem and what I think the development team expects.

A valid SetJavaHome value points to a 32-bi JVM, and in my testing environment that is:

SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Another post on this topic can be found here. As always, I hope this saves somebody time working with the Oracle product stack.

Using a lowercase hostname is typical but I got sloppy on a Windows 7 installation, after all Windows is case insensitive, and I used a mixed case hostname. It raised an interesting error when installing Oracle Database 11g Release 2.

After verifying the ports were available, it required testing the ability to form a socket. The quickest way to do that was installing ActiveState Perl and test the socket.

Server-side Perl code (server.pl):

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

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 LocalAddr => 'mclaughlinmysql',
                 LocalPort => '5500',
                 Proto => 'tcp',
                 Listen => 5500,
                 Reuse => 5500);
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Set socket to listen for incoming request and loop while waiting.
my $new_sock = $sock->accept();
while(<$new_sock>) {
print $_; }
 
# Close the socket.
close($sock);

Server-side Perl code (client.pl):

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

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 PeerAddr => 'mclaughlinmysql',
                 PeerPort => '5500',
                 Proto => 'tcp');
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Send string to socket.
print $sock "Hello there!\n";
close($sock);

These scripts help you check connectivity on a port. Run the server first in one command shell and the client second in another command shell. Then, the server-side program prints the “Hello There!” message sent from the client-side program.

You run the server with the following:

perl server.pl

and the client with this:

perl client.pl

Hope they help you verify viability through server ports.

It’s amazing the moving parts in Oracle Database 11g, and the ones that I don’t use after installation for a while often reach out to teach me something new. That’s what happened when I went to launch SQL Developer on my Windows 7 Professional 64-bit installation.

I got the message Unable to find a Java Virtual Machine, as shown in the image below. It really means unable to find a 32-bit Java 5 or Java 6 (1.6.0_4+) SDK home. Although the installation appeared fine, it pointed the SQL Developer configuration file to an incompatible 64-bit Java 7 SDK.

You fix this error by following these steps:

1. Open the sqldeveloper.conf file and check the SetJavaHome parameter value. You find the sqldeveloper.conf file in the following directory:

%ORACLE_HOME\sqldeveloper\sqldeveloper\bin

2. Install the 32-bit Java 6 (1.6.0_4+) SDK on the operating system if not installed already. You can check whether it’s installed by looking for it in the Program Files (x86) folder.
3. Change the value of the SetJavaHome parameter to point to the new 32-bit Java 6 home directory (or folder). The following change to line 18 in the sqldeveloper.conf file should fix it on your installation (provided that’s your version of the JVM).

18

SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Hope this helps those who run into the same issue.

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

• A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
• A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

1. Download and install the Java Software Development Kit (JSDK) for Java 6.
2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:

set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

5. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
6. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:

javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB

7. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
8. When it runs, you’ll see the following tabbed JOptionPane.

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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287

// --------------------------------------------------------------------
// WriteReadCLOB.java
// by Michael McLaughlin
//
// This code demonstrates reading a large text file and displaying
// the text stream in a JLabel in a JFrame.
//
// The UPDATE and SELECT statements have dependencies on the
// create_store.sql script.
// --------------------------------------------------------------------
 
// Java Application class imports.
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
 
// Generic JDBC imports.
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
 
// Oracle JDBC import.
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.pool.OracleDataSource;
 
// Include book libraries (available at publisher website).
import plsql.jdbc.DataConnectionPane;
import plsql.fileio.FileIO;
// -------------------------------------------------------------------/
public class WriteReadCLOB extends JFrame {
  // Define database connections.
  private String host;
  private String port;
  private String dbname;
  private String userid;
  private String passwd;
 
  // Define data connection pane.
  private DataConnectionPane message = new DataConnectionPane();
 
  // Construct the class.
  public WriteReadCLOB (String s) {
    super(s);
 
    // Get database connection values or exit.
    if (JOptionPane.showConfirmDialog(this,message
          ,"Set Oracle Connection String Values"
          ,JOptionPane.OK_CANCEL_OPTION) == 0) {
 
      // Set class connection variables.
      host = message.getHost();
      port = message.getPort();
      dbname = message.getDatabase();
      userid = message.getUserID();
      passwd = message.getPassword();
 
      // Print connection to console (debugging tool).
      message.getConnection();
 
      // Create a JPanel for data display.
      ManageCLOB panel = new ManageCLOB();
 
      // Configure the JPanel.
      panel.setOpaque(true);
      setContentPane(panel);
 
      // Configure the JFrame.
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setLocation(100,100);
      pack();
      setVisible(true); }
    else
      System.exit(1); }
  // -------------------------------------------------------------------/
  private class ManageCLOB extends JPanel {
 
    // Define display variables.
    private String clobText;
    private JScrollPane scrollPane;
    private JTextArea textArea;
    // -----------------------------------------------------------------/
    public ManageCLOB () {
      // Set layout manager.
      super(new GridLayout(1,0));
 
      // Assign file read to String.
      clobText = FileIO.openFile(FileIO.findFile(this));
 
      // Insert record before querying it.
      if (clobText.length() > 0) {
        if (insertClob(host,port,dbname,userid,passwd,clobText))
          clobText = getQuery(host,port,dbname,userid,passwd);
        else
          clobText = null; }
      else
        System.exit(2);
 
      // Construct text area and format it.
      textArea = new JTextArea(clobText);
      textArea.setEditable(false);
      textArea.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14));
      textArea.setLineWrap(true);
      textArea.setRows(10);
      textArea.setSize(400,100);
      textArea.setWrapStyleWord(true);
 
      // Put the image in container, and add label to panel.
      scrollPane = new JScrollPane(textArea);
      add(scrollPane); }
    // ---------------------------------------------------------------/
    private Boolean insertClob(String host,String port,String dbname
                              ,String user,String pswd,String fileString) {
 
      try
      {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Create statement.
        CallableStatement stmt =
          conn.prepareCall("UPDATE item "+
                           "SET    item_desc = ? "+
                           "WHERE  item_title = "+
                           "'The Lord of the Rings - Fellowship of the Ring'"+
                           "AND    item_subtitle = 'Widescreen Edition'");
 
        // Set string into statement.
        stmt.setString(1,fileString);
 
        // Execute query.
        if (stmt.execute())
          conn.commit();
 
        // Close resources.
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return true; } // End of connection try-block.
 
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return false; }
        else {
          System.out.println(e.getMessage());
 
          // Return an empty String on error.
          return false; }}}
    // -----------------------------------------------------------------/
    private String getQuery(String host,String port,String dbname
                           ,String user,String pswd) {
 
      // Define method variables.
      char[] buffer;
      int count = 0;
      int length = 0;
      String data = null;
      String[] type;
      StringBuffer sb;
 
      try {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Define metadata object.
        DatabaseMetaData dmd = conn.getMetaData();
 
        // Create statement.
        Statement stmt = conn.createStatement();
 
        // Execute query.
        ResultSet rset =
          stmt.executeQuery(
            "SELECT item_desc " +
            "FROM   item " +
            "WHERE  item_title = " +
            "'The Lord of the Rings - Fellowship of the Ring'"+
            "AND    item_subtitle = 'Widescreen Edition'");
 
        // Get the query metadata, size array and assign column values.
        ResultSetMetaData rsmd = rset.getMetaData();
        type = new String[rsmd.getColumnCount()];
        for (int col = 0;col < rsmd.getColumnCount();col++)
          type[col] = rsmd.getColumnTypeName(col + 1);
 
        // Read rows and only CLOB data type columns.
        while (rset.next()) {
          for (int col = 0;col < rsmd.getColumnCount();col++) {
            if (type[col] == "CLOB") {
              // Assign result set to CLOB variable.
              Clob clob = rset.getClob(col + 1);
 
              // Check that it is not null and read the character stream.
              if (clob != null) {
                Reader is = clob.getCharacterStream();
 
                // Initialize local variables.
                sb = new StringBuffer();
                length = (int) clob.length();
 
                // Check CLOB is not empty.
                if (length > 0) {
 
                  // Initialize control structures to read stream.
                  buffer = new char[length];
                  count = 0;
 
                  // Read stream and append to StringBuffer.
                  try {
                    while ((count = is.read(buffer)) != -1)
                      sb.append(buffer);
 
                    // Assign StringBuffer to String.
                    data = new String(sb); }
                  catch (Exception e) {} }
                else
                  data = (String) null; }
              else
                data = (String) null; }
            else {
              data = (String) rset.getObject(col + 1); }}}
 
        // Close resources.
        rset.close();
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return data; }
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return data; }
        else {
          System.out.println(e.getMessage());
          return data; }}
        finally {
          if (data == null) System.exit(1); }}}
  // -----------------------------------------------------------------/
  public static void main(String[] args) {
    // Define window.
    WriteReadCLOB frame = new WriteReadCLOB("Write & Read CLOB Text"); }}

Six years ago, I wrote Oracle Database 10g Express Edition PHP Web Programming for the release of the express edition. It was a lot of fun to write because I enjoy the PHP programming language, but unfortunately sales didn’t measure up too well. That’s probably because the population of PHP developers working with Oracle was small.

Today it seems there are more PHP developers working with Oracle 11g. While the population of PHP community for Oracle 11g is still smaller than for MySQL, it continues to grow year-over-year.

The FREE Underground PHP and Oracle Manual can help those converting PHP to run in the Oracle Call Interface, which is the replacement for MySQLi Interface. Chris Jones (an Oracle Open Source Product Manager) and Alison Holloway (an Oracle Senior Product Manager) write and maintain this book. It’s a great place to start if you’re migrating to Oracle Database 11g from MySQL.

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require aliases for inline views (what Oracle calls MySQL derived tables and Microsoft calls a Common Table Expression (CTE)).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) dt;

MySQL requires that every derived table have its own alias. This make sense when you think about query optimization engines work, but that’s a story for a much longer post.

You can find more about inline views, derived tables, and common table expressions in this older post of mine. This post was promised so that future students can simply google and answer rather than ask me or a class tutor.

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE parameter is set to OFF.

The SQL*Plus ESCAPE parameter default value is a backslash. and when the ESCAPE parameter is enabled a statement like this raises the following exception:

ERROR at line 3:
ORA-01425: escape CHARACTER must be CHARACTER string OF LENGTH 1

If you drop the ESCAPE '\' clause with the ESCAPE parameter enabled it will return all rows from the table not just those strings with an underscore in the string. Hope this helps if need to look for an underscore in a table.