MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for January, 2012

Function or Procedure?

with 7 comments

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to 1/2.

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

CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value.
  lv_result   BINARY_DOUBLE;
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    lv_result := POWER(pv_number,pv_power);
  ELSE
    lv_result := POWER(pv_number,(1 / pv_power));
  END IF;
 
  RETURN lv_result;
END find_root_function;
/

You can test it with these to queries against the dual table:

SELECT TO_CHAR(find_root_function(4,3),'99,999.90') FROM dual;
SELECT TO_CHAR(find_root_function(125,3,1),'99,999.90') FROM dual;

The procedure does the same thing as the function. The difference is that the fourth parameter to the procedure returns the value rather than a formal return type like a function.

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

CREATE OR REPLACE PROCEDURE find_root_procedure
( pv_number   IN     BINARY_DOUBLE
, pv_power    IN     BINARY_DOUBLE
, pv_inverse  IN     BINARY_INTEGER DEFAULT 0
, pv_return      OUT BINARY_DOUBLE ) IS
 
BEGIN
 
  -- If the inverse value is anything but zero calculate the inverse of the power.
  IF pv_inverse = 0 THEN
    pv_return := POWER(pv_number,pv_power);
  ELSE
    dbms_output.put_line('here');
    pv_return := POWER(pv_number,(1 / pv_power));
  END IF;
 
END find_root_procedure;
/

You can test it inside an anonymous block PL/SQL program, 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
24
25

DECLARE
 
  -- Declare input variables.
  lv_input   BINARY_DOUBLE;
  lv_power   BINARY_DOUBLE;
  lv_inverse BINARY_INTEGER;
  lv_output  BINARY_DOUBLE;
 
BEGIN
 
  -- Assign input values to variables.
  lv_input := '&1';
  lv_power := '&2';
  lv_inverse := '&3';
 
  -- Test raising to a power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
  -- Test raising to an inverse power.
  find_root_procedure(lv_input, lv_power, lv_inverse, lv_output);
  dbms_output.put_line(TO_CHAR(lv_output,'99,999.90'));
 
END;
/

You can test it inside an anonymous block PL/SQL program, like the following example. For reference, the difference between PL/SQL and the SQL*Plus environment is large. The EXECUTE call is correct in SQL*Plus but would be incorrect inside a PL/SQL block for a Native Dynamic SQL (NDS) call. Inside a PL/SQL block you would use EXECUTE IMMEDIATE because it dispatches a call from the current running scope to a nested scope operation (see comment below).

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

-- SQL*Plus Test.
VARIABLE sv_input BINARY_DOUBLE
VARIABLE sv_power BINARY_DOUBLE
VARIABLE sv_inverse BINARY_DOUBLE
VARIABLE sv_output  BINARY_DOUBLE
 
-- Verify the null value of the session variable.
SELECT :sv_output AS ":sv_output" FROM dual;
 
BEGIN
 
  -- Prompt for local assignments and initialize output variable.
  :sv_input   := '&1';
  :sv_power   := '&2';
  :sv_inverse := '&3';
  :sv_output  := 0;
 
END;
/
 
-- Run the procedure in the SQL*Plus scope.
EXECUTE find_root_procedure(:sv_input, :sv_power, :sv_inverse, :sv_output);
 
-- Query the new value of the session variable.
SELECT TO_CHAR(:sv_output,'99,999.90') AS ":output" FROM dual;

As usual, I hope this helps folks beyond the one who asked. Comments are always welcome.

Written by maclochlainn

January 31st, 2012 at 5:00 pm

Posted in Oracle,Oracle 11g,Oracle XE,pl/sql,sql

Java Generics in Oracle

without comments

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. 😉

Written by maclochlainn

January 11th, 2012 at 12:49 am

Posted in Java,Oracle,Oracle 11g,Uncategorized

Understanding Java Enum

with 4 comments

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.

Written by maclochlainn

January 2nd, 2012 at 11:33 pm

Posted in Java,Objects