MacLochlainns Weblog

Somebody posed the question about using a Comparator in the sorting examples provided in this earlier post on Updating Table View Columns (columns using a Varray or Nested Table of a single scalar data type). It seems the individual thought that you can’t use Java Generics inside an Oracle Database 11g’s Java libraries. It’s seems odd since they’ve been around since Java 5.

You can use Generics like those shown in the following example. It builds on explanation from the prior post. If you want to get the whole set of facts click the link above but you should have all the code you need in this post.

An example like this requires you first define a collection of strings in the database. This one uses the following definition:

1
2

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

This creates the Java library source, and line 21 shows the use of Generics in the instantiation of a anonymous Comparator class:

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import java.util.Comparator;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class Sorting {
    public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
    // Convert Oracle data type to Java data type.
    String[] unsorted = (String[])list.getArray();
 
    // Sort elements.
    Arrays.sort(unsorted, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
      // Declare a sorting key integer for the return value.
      int sortKey;
 
      // Check if lowercase words match and sort on first letter only.
      if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
         sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
      else
        sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
      // Return the sorting index.
      return sortKey; }});
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
 
      // Return the sorted list.
      return sorted; }
  }
/

The PL/SQL wrapper for this class would be:

1
2
3
4

CREATE OR REPLACE FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'Sorting.sortNaturalCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

You can test the code with the following query:

1
2

SELECT column_value
FROM TABLE(sortTitleCaseList(stringlist('Oranges','apples','Apples','Bananas','Apricots','apricots')));

It sorts the strings based on a title case sort, like:

COLUMN_VALUE
------------------------
Apples
apples
Apricots
apricots
Bananas
Oranges
 
6 rows selected.

If you want a quick example of a Generic Collection sort operation outside of the database, here a sample file.

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

// Import required classes.
import java.util.Arrays;
import java.util.Comparator;
 
/**
 * An example of using a Comparator sort.
 */
public class SortStaticString {
 
  /*
   * Sort the instance array.
   */
  public static String[] sortTitleCaseList(String[] list) {
 
    // Sort elements by title case.
    Arrays.sort(list, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
        // Declare a sorting key integer for the return value.
        int sortKey;
 
        // Check if lowercase words match and sort on first letter only.
        if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
          sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
        else
          sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
        // Return the sorting index.
        return sortKey; }});
 
    // Return the sorted Index.     
    return list;
  }
 
  /*
   * Test case.
   */
  public static void main(String[] args) {
 
    // Construct and instance and apply sort method.     
    args = SortStaticString.sortTitleCaseList(args);
 
    // Print the title case sorted list.
    for (int i = 0; i < args.length; i++) {
      System.out.println(args[i]); }
  }
}

You would call the SortStaticString class as follows:

java SortStaticString apples Oranges Pears Apples orange Grapefruit

I hope this helps the interested party and any others looking for a sample file.

Somebody wanted an example of how to write an Enum class in Java 7 (a bit late since its introduced in Java 5) because they found the Enum tutorial unhelpful (not as helpful to their purpose at hand). They wanted to understand how to use an Enum type in another class. Here’s an example set of files to do that and here’s the link to the jazzed up Java 7 API online docs).

First, you need to understand that while the equals(), toString(), and hashCode() override methods should always be provided in your classes. The exception is when they’re designated final, like the toString() and hashCode() methods of the Enum class. Second, you can write an Enum class with or without private variables. The inclusion of private instance variables makes the Enum a complex Enum (that’s just the vocabulary for the Java certification tests.

Sample Enum Class

The AppleComputer class is a complex Enum, and you should note that the constructor is private, and must always define the values of instance variables. The instance variables are defined within the parentheses after the name in the enumeration list.

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

/**
 * Enumeration class AppleComputer - write a description of the enum class here
 * 
 * @author  Michael McLaughlin
 * @version 1.0
 */
public enum AppleComputer
{
  /**
   * This is an entry-level desktop computer.
   */
  IMAC("Entry-level Desktop",1199),
  /**
   * This is an manager-level laptop computer.
   */
  MACBOOKAIR("Manager-level Laptop",999),
  /**
   * This is an manager-level laptop computer.
   */
  MACBOOKPRO("Developer-level Laptop",1199),
  /**
   * This is an developer-level laptop computer.
   */
  MACMINI("Mini-Desktop",599),
  /**
   * This is a mini-desktop computer.
   */
  MACPRO("Desktop",2499);
 
  /**
   *  Private variable definitions.
   */
  private double cost;
  private String description;
 
  /**
   *  Constructs an instance with a cost and description. 
   */
  private AppleComputer(String description, double cost) {
    this.description = description;
    this.cost = cost; }
 
  /**
   *  Returns the cost field of an Apple Computer. 
   */
  public double getCost() {
    return this.cost; }
 
  /**
   *  Returns the description field of an Apple Computer. 
   */
  public String getDescription() {
    return description; }
 
  /**
   *  Returns the description field of an Apple Computer. 
   */
  public String getDescription(String name) {
    return this.description; }
 
  /**
   *  Returns the equality of between two AppleComputer Enum types. 
   */
  public boolean equals(AppleComputer ac) {
    // First comparision on primitives and second on String instances.
    if ((this.cost == ac.getCost()) && (this.description.equals(ac.getDescription())))
      return true;
    else
      return false; }
 
  /**
   *  Method to test class integrity.
   */
  public static void main(String[] args) {
    if (args.length == 1) {
      System.out.printf("Apple Computer : %s is %s\n", AppleComputer.valueOf(args[0]).toString(), AppleComputer.IMAC.getDescription(args[0]));
      System.exit(0); } 
    else {
      for (AppleComputer ac : AppleComputer.values())
        System.out.printf("Apple Computer : %s is %s\n", ac, ac.description);  }}    
}

Sample Class that uses the Enum Class

The EnumTextUse class demonstrates how to use and identify the instance of a complex Enum class in another class. The setState() method has two approaches, one where you pass the name and discover viable enumeration possibilities, and two when you pass an Enum instance.

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

/**
 * The EnumTextUse class demonstrates how to identify and use an Enum
 * class that contains a text value.
 * 
 * @author  Michael McLaughlin 
 * @version 1.0
 */
 
/**
 * Import classes.
 */
import java.text.NumberFormat;
import java.text.DecimalFormat;
 
public class EnumTextUse {
 
  /**
   * Declare class level variables.
   */
  private AppleComputer ac;
  private String desc;
 
  /**
   * Constructor for objects of class TestEnum
   */
  public EnumTextUse() {}
 
  /**
   * An example of a method that takes a text string to find the ENUM
   * element value.
   */
  public int setState(String name) {
 
    // Declare local variable for false.
    int returnValue = 0;
 
    // put your code here
    try {
      // Verify that it's a valid instance of AppleComputer.         
      if (AppleComputer.valueOf(name) instanceof AppleComputer) {
        if (AppleComputer.IMAC == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.IMAC; }
        else if (AppleComputer.MACBOOKAIR == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACBOOKAIR; }
        else if (AppleComputer.MACBOOKPRO == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACBOOKPRO; }
        else if (AppleComputer.MACPRO == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACPRO; }
        else if (AppleComputer.MACMINI == AppleComputer.valueOf(name)) {
          this.ac = AppleComputer.MACMINI; }}
 
      // Return -1 as the truth indicator state was set or unnecessary.
      returnValue = -1; }
 
    catch (Exception e) {
      System.out.println(name + " is no longer sold."); }
 
    // Return the int value for true or false.
    return returnValue; 
  }
 
  /**
   * An example of a method that takes a text string to find the ENUM
   * element value.
   */
  public int setState(AppleComputer ac)
  {
    // Assign the AppleComputer fields to a local variable.
    double costSavings;
    double localCost = this.ac.getCost();
    String localDesc = this.ac.toString() + " : " + this.ac.getDescription();
 
    // Define format mask for output.
    NumberFormat f = new DecimalFormat("##,###,##0.00");
 
    // Declare local variable for false.
    int returnValue = 0;
 
    // put your code here
    try {
 
      // Check for an instance of the Enum.
      if (this.ac instanceof AppleComputer) {
 
      // Find different (unequal) instances and update with the new one.
      if (this.ac.equals(ac)) {
        // Print message on match between prior and set value of AppleComputer.
        this.desc = this.ac + " is the authorized platform and no cost difference."; }
 
      else {
 
        // Assign the new Enum value, calculate and display cost savings message.
        this.ac = ac;
        costSavings = this.ac.getCost() - localCost;
 
        // Determine the message based on a reduced or increased cost of replacement.
        if (costSavings > 0) {
          this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(costSavings) + " more than planned."; }
        else {
          this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(Math.abs(costSavings)) + " less than planned."; }}}
 
        // Return -1 as the truth indicator state was set or unnecessary.
        returnValue = -1; }
 
    catch (Exception e) {
      System.out.println(e.getMessage()); }
 
    // Return the int value for true or false.
    return returnValue; 
  }
 
  /**
   * Return the current description value.
   */
  public String getState() {
    return this.desc; }
 
  /**
   * Allows testing the program.
   */
  public static void main(String [] args)
  {
    // Declare a string of possible enumeration types.
    String [] list = {"IMAC","MACBOOK","MACBOOKAIR","MACBOOKPRO","MACMINI","MACPRO"};
 
    // Construct a test instance.
    EnumTextUse etu = new EnumTextUse();
 
    /**
     * Read through the list of enumeration types, printing output from
     * the inherited or overridden toString() method.
     */ 
    for (int i = 0; i < list.length; i++) {
      if (etu.setState(list[i]) != 0) {
 
        // The company standard must apply in all cases.
        if (etu.setState(AppleComputer.IMAC) != 0)
          System.out.println(etu.getState()); }
    }
  }
}

You can run the EnumTextUse class from the command-line or tool of your choice, like:

$ java EnumTextUse

It’ll print the following text:

IMAC is the authorized platform and no cost difference.
MACBOOK is no longer sold.
IMAC is substituted for MACBOOKAIR : Manager-level Laptop at $200.00 more than planned.
IMAC is substituted for MACBOOKPRO : Developer-level Laptop at $0.00 less than planned.
IMAC is substituted for MACMINI : Mini-Desktop at $600.00 more than planned.
IMAC is substituted for MACPRO : Desktop at $1,300.00 less than planned.

You can check this Java Community Process page for the nuts and bolts of the Enum class. As always, I hoped this helped. Let me know if anything requires more clarity or any correction.

Here are step-by-step instructions for installing Oracle Database 11g Release 2 on Windows 7. It’s provided in response to questions posted on my step-by-step instructions for installing Oracle 11gR1 on Windows 7. For reference, I posted the former because it didn’t work without intervention. I hadn’t updated step-by-step instructions because Oracle Database 11g Release 2 has always worked for me when installing on Windows 7.

A number of students and blog readers have mentioned that it didn’t work for them. My guess is that they had configuration issues within the Windows 7 environment. There 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 (the latter typically requires 8 GB of real memory to avoid extensive disk caching of memory).
2. Install Oracle’s SJDK and run time for Java 6 or 7 on Windows 7 (I installed Java 7).
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.

1
2
3
4

127.0.0.1      localhost
::1            localhost
127.0.0.1      McLaughlinMySQL McLaughlinMySQL.techtinker.com
172.26.126.131 McLaughlinMySQL McLaughlinMySQL.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.

Oracle 11g Release 2 Installation Steps

1. The Oracle Database 11g Release 2 files are broken down into two zip files on the Oracle site. That means you need to download both files, and then expand them into an installation directory. I called my installation directory C:\OracleInstall, but you can call it whatever works for you. Here’s a screen shot of the directory after expanding both compressed files (done with WinZip 15).

2. It’s a good idea to provide an email address for security updates no matter what. You can uncheck the box if you don’t want updates via Oracle Support Services. Click the Next button to continue.

3. Most developers want to Create and configure a database. If that’s your desire, leave the default radio button checked. Click the Next button to continue.

4. Most developers install their test instance on a desktop or laptop. If that’s your desire, leave the default Desktop Class radio button checked. Click the Next button to continue.

5. These are default settings and generally the easiest to use. I’d suggest you change the Character Set drop down to Unicode. Then, enter a password twice. Oracle requires that you use at least one capital letter and one number in a 6 character or more long password. Click the Next button to continue.

6. The next screen is a progress bar that checks for prerequisites. You shouldn’t have to do anything here because it automatically advances you to the next dialog screen. This generally takes less than a minute to run but can take two or so. If you’re much beyond 3 minutes there may be a resource problem with your Windows PC or virtual machine.

7. This shows you all the selected values for the installation. Unless you want to abort the installation, click the Finish button to proceed.

8. This screen is the main progress bar, and you’ll be here somewhere between 5 and 10 minutes. The downside is that there are some dialogs that will popup during this phase and you need to authorize them, so it’s a bad time to take a break.

9. The first popup screen requires you to authorize the Java runtime to call home. You should click the Allow Access button to proceed.

10. When the progress bar starts configuring the instance, failures can occur. The first may occur during the network configuration, which typically happens if you didn’t preconfigure the hosts file. You don’t need to do anything but watch here unless the installer triggers an error.

11. The following progress bar is launched by the Oracle Database Configuration Assistant. It typically runs without a problem. You don’t need to do anything but watch here unless the installer triggers an error. This takes a few minutes, and unfortunately requires you to stick aroung to again authorize Java.

12. The second popup screen requires you to authorize the Java runtime to call home. You should click the Allow Access button to proceed.

13. The third and last popup screen asks you to whether you want to open other scheme. Generally, you should click the OK button to proceed.

14. The next dialog shows you that the network and database instances are configured. It’s also running the OEM (Oracle Enterprise Manager) installation. You can wait here but it won’t be a long wait.

15. This is the last dialog and says you’ve installed Oracle Database 11g Release 2 successfully. You can the Close button to complete the installation.

As always, I hope this helps folks.

Answering a reader’s question: How can you sort data inside an Oracle table view column? This blog post shows you how to perform the trick, but for the record I’m not a fan of nested tables. A table view column is an Oracle specific user-defined type (UDT), and is nested table or varray of a scalar data type.

This blog post reviews table view columns, and extends concepts from Oracle Database 11g & MySQL 5.6 (by the way virtually everything in the book is relevant from MySQL 5.1 forward). It demonstrates how you can use PL/SQL user-defined functions (UDFs) to supplement the SQL semantics for updating nested tables, and then it shows how you can reshuffle (sort) data store the sorted data in table view columns.

Before you implement table view columns, you should answer two design questions and one relational modeling principal. You should also understand that this direction isn’t portable across database implementations. It currently supported fully by the Oracle database and mostly by PostgreSQL database. You can find how to join nested tables helpful in understanding the UPDATE statements used in this posting, and this earlier post on UPDATE and DELETE statements.

Design Questions:

• Should you implement full object types with access methods in PL/SQL? The object type solution says there is no value in the nested data outside of the complete object. While choosing the table view column solution says that there is value to just implementing a nested list without element handling methods.
• Should you embed the elements in an XML_TYPE? An XML solution supports hierarchical node structures more naturally, like when you only access child nodes through the parent node. While choosing the table view column solution says that you want to avoid the XML Software Development Kit and that the data set is small and more manageable in a table view column.

Design Principal:

• Should you implement an ID-dependent relational modeling concept? An ID-dependent model replaces the primary and foreign keys with the relative position of parent and child elements. This is the design adopted when you choose a table view column, and it is more complex than single subject relational tables.

You should note that table view columns are inherently static at creation. You must also update the entire nested table view column when using Oracle SQL. Oracle SQL does let you modified attributes of object types in nested tables, as qualified in my new book (page 252).

Any attempt to modify a table view column element in SQL raises an ORA-25015 error. The error message states that (you) cannot perform DML on this nested TABLE VIEW COLUMN.

You can update the table view column value by replacing it with a new collection, and that’s done with a PL/SQL function. This type of function preserves the ordered list in the table view column by finding and replacing an element in the collection.

Unfortunately, developers who use nested tables typically design table view columns with an internal ordering scheme. That means the collection is ordered during insert or update. This type of design relies on the fact that you can’t change the order without re-writing the stored structure.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. All that aside, here’s how you ensure element updates while preserving element position:

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

CREATE OR REPLACE FUNCTION update_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2
, new_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
    ELSE
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := new_element_value;
    END IF;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  RETURN lv_element_collection;
END update_collection;
/

Then, you can use the user-defined function (UDF) inside a SQL UPDATE statement, like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee e
              WHERE   e.employee_id = 1) e
SET   e.street_address = update_collection(e.street_address, 'Suite 525','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The UPDATE_COLLECTION function replaces Suite 525 with Suite 522, and preserves the sequence of elements in a new nested table. The UPDATE statement assigns the modified nested table to the table view column. You can find the code to create the employee table in Chapter 6 (pages 148-149), and the code to insert the default data in Chapter 8 (page 229) of Oracle Database 11g & MySQL 5.6.

The lv_counter variable could be replaced with a reference to the for loop’s iterator (i) because the counts of both collections are the same. I opted for the local variable to make the code easier to read.

While common for those you use these, it is a bad practice to rely on the ordering of elements in a collection. At least, it’s a bad practice when we’re trying to work within the relational model. Along the same line of thought, you also have the ability of removing elements from a table view column with a similar PL/SQL function. You could write the function like this:

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

CREATE OR REPLACE FUNCTION delete_from_collection
( old_element_collection  STREET_LIST
, old_element_value   VARCHAR2 ) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  FOR i IN 1..old_element_collection.COUNT LOOP
    IF NOT old_element_collection(i) = old_element_value THEN
      lv_element_collection.EXTEND;
      lv_element_collection(lv_counter) := old_element_collection(i);
      lv_counter := lv_counter + 1;
    END IF;
  END LOOP;
 
  RETURN lv_element_collection;
END delete_from_collection;
/

Then, you can use the user-defined function (UDF) to delete an element from the collection inside a SQL UPDATE statement, like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = delete_from_collection(e.street_address,'Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;

After understanding all that, let’s examine how you sort data in a nested table or varray of a scalar data type (the basis of a table view column). The easiest way is a BULK COLLECT INTO statement nested inside a function, like this:

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

CREATE OR REPLACE FUNCTION sort_collection
( old_element_collection  STREET_LIST) RETURN STREET_LIST IS
 
  -- Declare and initial a new counter.
  lv_counter  NUMBER := 1;
 
  -- Declare local return collection variable.
  lv_element_collection  STREET_LIST :=  street_list();
 
BEGIN
 
  -- Sort a collection alphabetically based on case sensitivity.
  SELECT   column_value BULK COLLECT INTO lv_element_collection
  FROM     TABLE(old_element_collection)
  ORDER BY column_value;
 
  RETURN lv_element_collection;
END sort_collection;
/

You could test it with this:

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'Lewis', 'Clark', 'Fallon')));

Then, you can use the user-defined function (UDF) to update a table view column like this:

1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM    employee1 e
              WHERE   e.employee_id = 1) e
SET   e.street_address = sort_collection(e.street_address)
,     e.city = 'Oakland'
WHERE e.address_id = 1;

The funny thing about database solutions these days is that some Java developers don’t appreciate the simplicity of SQL and PL/SQL and would solve the problem with Java. Especially, if it was an case insensitive sort operation. That’s the hard way (easy way at the bottom), but I figured it should be thrown in because some folks think everything is generic if written in Java. Though, I thought making it proprietary would increase the irony and wrote it as a Java library for Oracle.

Here’s the Java library, which you can run from the SQL*Plus command line, SQL Developer, or that pricey Toad:

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

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortOracleList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class DemoSort {
    public static ARRAY getList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
      // Convert Oracle data type to Java data type.
      String[] unsorted = (String[])list.getArray();
 
      // Sort elements.
      Arrays.sort(unsorted, String.CASE_INSENSITIVE_ORDER);
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
    return sorted; }}
/

Then, you write the PL/SQL wrapper like this:

1
2
3
4

CREATE OR REPLACE FUNCTION sortTable(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'DemoSort.getList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

You could test the case insensitive sort with this:

1
2

SELECT   column_value
FROM     TABLE(sort_collection(street_list('Adams', 'adams', 'Lewis', 'Clark', 'Fallon')));

Naturally, it ignores the fact you could do it like this without Java by using the UPPER function in the purely PL/SQL SORT_COLLECTION function shown earlier in this post:

12
13
14
15

    -- Sort a collection alphabetically based on case insensitive comparison.
    SELECT   column_value BULK COLLECT INTO lv_element_collection
    FROM     TABLE(old_element_collection)
    ORDER BY UPPER(column_value);

Anyway, it’s a bunch of thoughts about writing solutions for table view columns. Hope it helps those interested in nested tables.

Working with a Fedora 16 VM for my students (next term) and found that the MySQL Server’s my.cnf file worked with a Linux socket as opposed to a listener port, and that several configuration options where missing from the file. Here’s the default /etc/my.cnf file after the package installation from the Red Hat site:

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Without rebuilding the log files, this seemed like the cleanest replacement for the MySQL Server my.cnf for a development instance running on Fedora 16. If you’ve other suggestions, please let me know.

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
# Default directory.
datadir=/var/lib/mysql
 
# The TCP/IP Port the MySQL Server listens on.
port=3306
 
# The Linux Socket the MySQL Server uses when not using a listener.
# socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# The default storage engine that will be used when creating new tables.
default-storage-engine=INNODB
 
# Set the SQL mode to strict.
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
# Set the maximum number of connections.
max_connections=100
 
# Set the number of open tables for all threads.
table_cache=256
 
# Set the maximum size for internal (in-memory) temporary tables.
tmp_table_size=26M
 
# Set how many threads should be kept in a cache for reuse.
thread_cache_size=8
 
# MyISAM configuration.
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=52M
key_buffer_size=36M
read_rnd_buffer_size=256K
sort_buffer_size=256K
 
# InnoDB configuration.
innodb_data_home_dir=/var/lib/mysql
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=25M
innodb_log_file_size=5M
innodb_thread_concurrency=8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As always, I hope this helps somebody.

Back in September, I showed how to setup Oracle Database 11g XE on Fedora. It was straightforward more or less, but tonight I ran into a problem while working with the Oracle Stop Database menu option. When I selected it form the menu, I got the user must be in the DBA OS group to stop the database.

Since the database started automatically on boot and shutdown when closing the operating system, I missed this nuance in the setup. The screen shot for the error is:

Oracle Database 11g XE automatically creates an oracle user with a dba group. While the mclaughlinm user was in the wheel group and an authorized sudoer, the mclaughlinm user needed to also be in the dba group. That’s more tricky in Fedora 15 and 16 because they’ve removed the System menu and the options that let you install and modify users.

Here are the steps to add your user to the dba group. Open a terminal session, and launch the User Manager application with the following command:

system-config-users

You have to enter the root password to get this to work. Then, it launches the User Manager application. Click on the target user, and click the Properties button.

The User Manager application launches the User Properties dialog. Click on the Groups tab.

Scroll in the groups list to the dba group. Click the checkbox for the dba group and the OK button.

Now, you’ll be able to navigate to through the menu to Stop Database to shutdown and Start Database to restart the Oracle database.

Hope this helps a few folks.

Fedora 16 is clearly better than Fedora 15 but I found Menu Editing (Alacarte package) was broken in it because of a missing library dependency, and I’ve updated Fedora Bug 734442 with the work around. Here’s what’s wrong and how to fix it.

After installing the Menu Editing (Alacarte) package, you’ll encounter this error when trying to launch the menu editor:

MainWindow.py:19:<module>:Import Error: No module named gmenu

That error occurs because the gnome-menus-3.2.0.1-1.fc16.x86_64 is missing the /usr/lib64/python2.7/site-packages/gmenu.so library. So, I copied the version of gmenu.so from a Fedora 15 release as the root user. Naturally, at this point you’d test if it was fixed, I did. It wasn’t, and I got a new error:

MainWindow.py:19:<module>:Import Error: libgnome-menu.so.2: cannot open shared object file: No such file or directory

That error occurs because the gnome-menus-3.2.0.1-1.fc16.x86_64 is missing the /usr/lib64/libgnome-menu.so.2 symbolic link to the /usr/lib64/libgnome-menu.so.2.4.13 library. While the package meets the dependency check, the libraries fail the run time validation.

You can get a copy of the Fedora 15 package with the following command, which you should connect as the root user in navigate to the /tmp directory. Then, create a copy directory and change the /tmp/copy directory before running either of the next two commands.

Use this for 32-bit Installs

# wget http://download.fedora.redhat.com/pub/fedora/linux/releases/15/Fedora/i386/os/Packages/gnome-menus-3.0.1-1.fc15.i686.rpm

Use this for 64-bit Installs

# wget http://download.fedora.redhat.com/pub/fedora/linux/releases/15/Fedora/x86_64/os/Packages/gnome-menus-3.0.1-1.fc15.x86_64.rpm

That command only a copy of the RPM file, but the following converts it into an exploded directory. Assuming you created a copy directory in the /tmp directory, execute the following command from within the /tmp/copy directory. It will create a directory tree with the required files. After you copy the files, you can remove (rm) the copy directory from the /tmp directory.

Use this for 32-bit Installs

# rpm2cpio http://download.fedora.redhat.com/pub/fedora/linux/releases/15/Fedora/i386/os/Packages/gnome-menus-3.0.1-1.fc15.i686.rpm | cpio -ivd

Use this for 64-bit Installs

# rpm2cpio http://download.fedora.redhat.com/pub/fedora/linux/releases/15/Fedora/x86_64/os/Packages/gnome-menus-3.0.1-1.fc15.x86_64.rpm | cpio -ivd

You can now copy the files with these files. The target location differs between the 32-bit and 64-bit versions of the operating system.

Use this for 32-bit Installs

# cp /tmp/copy/usr/lib/libgnome-menu.so.2* /usr/lib
# cp /tmp/copy/usr/lib/python2.7/site-packages/gmenu.so /usr/lib/python2.7/site-packages

Alternatively, you can copy the following two files from any valid 32-bit Fedora 15 instance into a Fedora 16 instance, and manually create the symbolic link.

# /usr/lib/libgnome-menu.so.2.4.13
# /usr/lib/python2.7/site-packages/gmenu.so

Use this for 64-bit Installs

# cp /tmp/copy/usr/lib64/libgnome-menu.so.2* /usr/lib64
# cp /tmp/copy/usr/lib64/python2.7/site-packages/gmenu.so /usr/lib64/python2.7/site-packages

Alternatively, you can copy the following two files from any valid Fedora 64-bit 15 instance into a Fedora 16 instance, and manually create the symbolic link.

/usr/lib64/libgnome-menu.so.2.4.13
/usr/lib64/python2.7/site-packages/gmenu.so

After you copy the two files into the right directories as root, you can create the necessary symbolic link with the following command (this isn’t necessary with the wildcard instruction provided earlier in the post). You need to ensure that you’re in the /usr/lib directory when you run the ln command, as noted by Gavin’s comment:

Use this for 32-bit Installs

# ln -s /usr/lib/libgnome-menu.so.2.4.13 libgnome-menu.so.2

Use this for 64-bit Installs

# ln -s /usr/lib64/libgnome-menu.so.2.4.13 libgnome-menu.so.2

As mentioned by Darr247, don’t forget to remove the /tmp/copy directory when you’re done making the changes.

Somebody asked me to add the Red Hat Package Manager (RPM) commands that let me find these dependencies. That seemed like a good idea, here they are:

If you want to set a menu item up manually, check this blog post. You also have the LXMenuEditor that’s available in beta as an alternative. Hope this helps those in need, as always.

The distorted video problems introduced by NVIDIA on MacBook Pro is old news because it affected early 2008 machines. Alas, those intermittent little gray lines at the bottom of my MacBook Pro early 2008 model now transcend my screen 4 out of 5 boot cycles, as shown below:

The screen shot from the MacBook Pro shows the image is fine when emailed to another machine. As Pooh Bear (my daughter’s favorite) would say, “Oh, bother …”

Now I’m urgently moving all files over to other media. That includes conference presentations, code, and several VMs.

While waiting on the USB transfers, I checked out what it might run to fix it and ran into this Apple Support page. It basically says my inaction at the annoyance during my AppleCare period, which just lapsed in July, won’t make it my problem. That’s because I’m within the extended 4 year window from date of purchase.

Living in Eastern Idaho has a number of perks, like the environment and community. It does have some downsides, like a 250 miles drive to the nearest Apple Store.
It looks like I’ll be making a little drive to Salt Lake City next week.

Actually got this fixed at the University’s help desk. Apple sent the exact same logic board that it had previously, and guess what the bars are hemming me in now intermittently. Ouch!

A little more research and it appears that this may be related to the Black Vertical Lines on MacBook Pro 15″ Display and from MacRumor’s archive. It’s odd there’s no recall on the monitor. Maybe that’s because Apple would pay for it, and NVIDIA paid for the other?

As with every purchase in technology, there’s an upside and downside. Clearly, the failure drove me to opt for the less expensive 13″ MacBook Pro upgrade with a 2.8 GHz CPU, 8 GB of memory, and 750 MB harddisk. I’ll probably avoid ever purchasing the leading, and pricier, Apple technology again.

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

• A FileIO.jar library that lets you enter MySQL connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
• A mysql-connector-java-3.1.14-bin.jar file, which is MySQL’s library for JDBC communication with the MySQL 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 mysql-connector-java-3.1.14-bin.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\mysql-connector-java-3.1.14-bin.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 WriteReadCLOBMysql.java code from the bottom of this posting and also put it into the JavaTest directory.
6. Compile the WriteReadCLOBMysql.java source code with the javac utility, as shown below:

javac WriteReadCLOBMysql.java

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

java WriteReadCLOBMysql

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

// --------------------------------------------------------------------
// WriteReadCLOBMysql.java
// by Michael McLaughlin
//
// This code demonstrates reading an image file and displaying
// the image 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.*;
 
// Mysql JDBC import.
import com.mysql.jdbc.Driver.*;
 
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
 
 
// Include book libraries (available at publisher website).
import plsql.jdbc.DataConnectionPane;
import plsql.fileio.FileIO;
// -------------------------------------------------------------------/
public class WriteReadCLOBMysql 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 WriteReadCLOBMysql (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
      {
        // Define connection.
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd);
 
        // Create statement.
		PreparedStatement prest;
		String sql = "UPDATE item SET item_desc = ? WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring' AND item_subtitle = 'Widescreen Edition'";
		prest = conn.prepareStatement(sql);
		prest.setString(1,fileString);
 
        // Execute query.
        if (prest.execute())
          conn.commit();
 
        // Close resources.
        prest.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("mysql Client Net8 Connection Error.",
                      "mysql-" + e.getErrorCode() +
                      ": Incorrect Net8 thin client arguments:\n\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 {
        // Define connection.
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd);
 
        // 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("mysql Client Net8 Connection Error.",
                      "mysql-" + e.getErrorCode() +
                      ": Incorrect Net8 thin client arguments:\n\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.
    WriteReadCLOBMysql frame = new WriteReadCLOBMysql("Write & Read CLOB Text"); }}

Somebody was trying to query Oracle via Microsoft Excel 2007, and didn’t have an Oracle ODBC Data Source that enable them to choose Oracle. I’d referred them to this post on adding MySQL’s ODBC driver to Windows 7 but that didn’t help enough. Posting this screen shot may not have helped either.

It appears I assumed too, like everyone would know that it’s on Windows 7 when Oracle 11g (or another Oracle database) is installed locally. The person who posed the question doesn’t have Oracle installed locally and can’t install it.

For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site. It’s in the driver sections, as shown in the screen capture below:

This driver contains the necessary OLE DB and ODBC drivers, as you can see in this screen shot.

Once you’ve downloaded it, you can return to this post where you set up an Excel query against a remote Oracle database. You should take note that Microsoft’s future direction adopts Oracle ODBC, like the approach they’ve chosen with MySQL’s ODBC driver. As always, I hope this helps.