Archive for the ‘Uncategorized’ Category
Collaborate 2012 – Day 3
Virtualization is important and Dave Welch from the House of Brick gave a great presentation of experiences with VMWare and Tier 1 databases. It was a comprehensive presentation, but the white paper was easier to follow. The slides were complete but the volume of information was a lot for an hour presentation. Well worth the time though.
Utah Oracle User Group (UTOUG) announced a call for Fall Symposium papers today. The Fall Symposium will be in Salt Lake City on 9/6/2012. If you’re interested in presenting on Oracle or MySQL, the call for presentations will be open until 6/15/2012.
The conference party was tonight, and it provided some nice orderves and pizza. The theme was a return to 1980s music, and some folks really dressed their parts. You can listen to a short snapshot of the band by clicking the image to launch a small video segment.
I’m looking forward to the APEX Behind the Scenes presentation at 8:30 a.m. tomorrow. When the conference is over, I won’t miss the smoke filled air that we walk through from the Luxor to the Mandalay. It’s really amazing that the complex is more than a mile in length. It runs from the Luxor to the Mandalay South Conference Center.
Java Generics in Oracle
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. 😉
Subscribe via RSS