Using Java Reflection in PeopleCode - Part 2

If you had referred to part 1 of our post on Using Java Reflection in PeopleCode, we create a very small class file, 'image.class', and tried invoking a method 'square' in that class. When doing so, PeopleSoft was unable to identify which 'square' method to invoke (the class had two square methods, with different inputs) and throwed a nice error on the screen "more than one overload matches".  We decided to spoon-feed PeopleSoft by using Java Reflection techniques. This would help us to use the right method that we are after to get the desired result.

If you had referred to the sample Java code in Part 1, the square method that we are trying to invoke accepts a "double" parameter as input.  The right code to use in such a situation is presented below.

Local JavaObject &Obj_class_l = GetJavaClass("java.lang.Class");
Local JavaObject &Obj_classloader_l = GetJavaClass("java.lang.ClassLoader");
Local JavaObject &Obj_getcoreclass_l = &Obj_class_l.forName("image", False, &Obj_classloader_l.getSystemClassLoader());
Local JavaObject &Obj_doubletype_l = GetJavaClass("java.lang.Double").TYPE;
Local JavaObject &Obj_argtypes_l = CreateJavaObject("java.lang.Class[]", &Obj_doubletype_l);
Local JavaObject &Obj_getcorrectmethod_l = &Obj_getcoreclass_l.getDeclaredMethod("square", &Obj_argtypes_l);
Local JavaObject &Obj_consargarray_l = CreateJavaObject("java.lang.Object[]", CreateJavaObject("java.lang.Double", "2.5"));
Local JavaObject &Obj_finalinvoke_l = &Obj_getcorrectmethod_l.invoke(&Obj_getcoreclass_l, &Obj_consargarray_l);
Error &Obj_finalinvoke_l.toString();

Don't worry, this is just the full code. How I arrived to this approach would be explained shortly. I would prefer to explain this from Top - Bottom. The output we are expecting in this case is 6.25.

For us to know which method to invoke, we need to get into the metadata of our class file. To do that, we create an object  &Obj_class_l of java.lang.Class. Using this object, we will be loading our class file "image.class" and then get into its metadata. 

Now, for us to get into the metadata of "image.class", we need to use  the "forName" method of java.lang.class and pass the class file name.  I first tried the following piece of code

Local JavaObject &Obj_getcoreclass_l = &Obj_class_l.forName("image");

When attempting to run this piece of code, the following error was observed on the screen;

Java Exception: java.lang.ClassNotFoundException: image: during call of java.lang.Class.forName.

This error means, PeopleSoft is not able to locate the class file "image". This could be because of the location of java.lang.class and image.class.  When I tried the following command to get the location; "java -verbose | grep java.lang.Class", I got the following output;

Loaded java.lang.Class from /… /PT846/jre1.4.2/lib/rt.jar]

Now, the class file "image.class" is located in /PT846/appserv/classes directory. This means, we cannot use "forName" directly but go for an alternate option where we tell  PeopleSoft, the exact location of the class file. There is another "forName" method in java.lang.Class, that takes the following inputs

public static Class forName(String name,
                            boolean initialize,
                            ClassLoader loader)

This method will help us to avoid the error, and load the Class file through PeopleCode. So, we declare an object for this method using

Local JavaObject &Obj_classloader_l = GetJavaClass("java.lang.ClassLoader");

and then use the second forname method as per below;

Local JavaObject &Obj_getcoreclass_l = &Obj_class_l.forName("image", False, &Obj_classloader_l.getSystemClassLoader());

This code tells PeopleSoft to load the core class file image, and to locate "image.class" , it needs to refer to the location returned by the method Obj_classloader_l.getSystemClassLoader(). Now, getSystemClassLoader method of java.lang.ClassLoader, returns the following output for me;

sun.misc.Launcher$AppClassLoader

I feel that provides the ability for the "forName" method, to look into the CLASSPATH to get the "image.class" file. So, the object &Obj_getcoreclass_l now has a reference to "image.class". With this reference, we will now be able to probe into the metadata of image.class file and choose the right method to execute..for our case, it is the "square" method with an input parameter type of "double".

We can now move into part 3 of our post, where we will explain how to identify the right method and then invoke it to get the output we are after.

Using Java Reflection in PeopleCode - Part 1

When working with GetJavaClass today in PeopleCode, I got a strange Error. I was trying to invoke the "getworksheet" method in JExcel workbook class, and PeopleCode greeted me with this error;

Calling Java jxl.Workbook.getWorkbook: more than one overload matches.

Now the key is, "more than one overload matches". I'm a beginner in Java and felt that I would do some more R & D to understand why PeopleSoft is throwing this error. Information on this error is scattered across the web, but nothing helps a beginner to grasp it clearly. To make things worse, the fix requires some understanding of reflection concepts in Java which makes it bad. To make things simple for a common PeopleSoft user, let me try to explain this error and the procedure to fix this in PeopleSoft. Create a simple class file image.class as per the code below

import java.util.*;
public class image {
public static void main(String[] args){
}
public static int square( int x )
         {
         return x * x;
     }
   
     public static double square( double y )
         {
         return y * y;
     }

}

Note that we have two methods "square" with the same name but the input to those methods are different; an int and a double. Compile this file and place it in your application server class directory. Now, if you run the following code from PeopleSoft

&ars_out_l = GetJavaClass("image").square(3.5); => You will get a neat error as shown below

Calling Java image.square: more than one overload matches.
While calling the noted Java method, the PeopleCode interface found more than one method signature that could match the given parameter types.

What this means to us is PeopleSoft application layer has detected more than one overload (method) and does not know which one to "invoke". When this is in Java, Java intelligently decides which method to invoke by understanding the nature of the inputs passed to the method. Unfortunately, PeopleSoft lacks this intelligence.Now, providing this intelligence to PeopleSoft would mean, the developer will have to find a way to tell PeopleSoft to "choose" the right method to invoke depending on the programming requirements.

To help PeopleSoft choose the right one, we will use a concept called "Reflection". What reflection means in simple terms is to read the class file, its methods and help in choosing the right one to call. In the next part of this post, we will see how to select the right method for the same example and get the right output.

How To Read Excel Files in Java

In this post we will present a working solution, that explains how to read and modify a Microsoft Excel Document in Java. The objective for us would be to read an excel file, write some data on a different column based on the input we read and modify the excel document.

As an example, let us assume that we have an Excel document (ReadExcelUsingJava.xls) with the following content in column 'A';

1
2
3
4
5

The output that I'm after should be written to a new Excel document, ModifyExcelUsingJava.xls, with the following content in both column 'A' and 'B'.

1
1MOD
2
2MOD
3
3MOD
4
4MOD
5
5MOD

As I said earlier, we will attempt to present a working example in Java to modify the Excel document. We will be using the JExcel API for this example, as I felt it is very easy to use for a beginner in Java. So, download JExcel and create a folder 'JExcel' and make sure that you have the 'jxl.jar' file available in your CLASSPATH. This is required and must, otherwise, you will not be able to run the code example we are going to show below.We will create a simple CLASS file, ExcelJavaReader.java.  We will show the code that needs to be placed in the JAVA file,and then run through it for you;

import java.io.*;
import java.util.Date;
import jxl.*; /* Import declarations to the JExcel JAR file */
import jxl.write.*; /* Import declarations to the JExcel JAR file */
public class ExcelJavaReader /* The name of our class file */
{
    public static void main(String args[])
    {
        try
        {
            Workbook ReadExcel = Workbook.getWorkbook(new File("ReadExcelUsingJava.xls")); /* Open the excel document in read only mode */
        WritableWorkbook ModifyExcel = Workbook.createWorkbook(new File("ModifyExcelUsingJava.xls"), ReadExcel);
           /*Create a writable version of the Excel Document*/
            Sheet sheet = ReadExcel.getSheet(0); /* Read the first worksheet of the Excel Workbook */
           WritableSheet sheet2 = ModifyExcel.getSheet(0); /* get the first worksheet for writing */
            for (int i=0;i<5;i++) /* Run a for loop to iterate over the CELLS in Excel */
            {
                Cell a1 = sheet.getCell(0,i); /* Get the first cell of Column A , 0 maps to A */
                WritableCell cell = sheet2.getWritableCell(1, i); /* Get column B first cell for writing output */
                String ReadExcelCellData = a1.getContents(); /* Read contents of CELL in Column A to a string variable */
                Label label = new Label(1, i, ReadExcelCellData +"MOD"); /* Append MOD to string and prepare data for stamping in Column B */
                sheet2.addCell(label); /* Write data to Excel worksheet CELL */
            }
            ModifyExcel.write();
            ModifyExcel.close();
            ReadExcel.close();
        }
        catch (Exception i)
        {
            System.out.println(i);
        }
    }
}

If you prepare an Excel document with the input we have given earlier and name it "ReadExcelUsingJava.xls" it will be used by the code to read the data from Excel and create the output Excel document. To compile the code you can use something as shown below

javac -classpath . ExcelJavaReader.java

To run the example, execute the code with the following statement

java -classpath . ExcelJavaReader

If you have done everything correctly, you will find an output file with the name ModifyExcelUsingJava.xls sitting inside the same directory. Give it a try and let us know if you run across any issues.

Oracle WIDTH BUCKET Function Example


In this post, we will explain the usage of WIDTH_BUCKET analytic function in Oracle with a simple example. The function WIDTH_BUCKET may sound odd, but the example that we will provide to explain it would make sure that you crack its usage without complexities.


To understand what WIDTH_BUCKET is all about, consider a scenario where you have a set of different brands of laptops with their Price marked. Out of all the laptop models you have, you know the minimum price and maximum price. Now, you would like to define some baskets and place all your laptops in appropriate ones, based on the price. WIDTH BUCKET function does this automatically for you, all you need to do is to help that function with your requirements.


Create a simple table and insert the data into the table as shown below, in a few minutes we will explain WIDTH BUCKET.

CREATE
TABLE WIDTH_BUCKET_TEST

(

PRODUCT_ID NUMBER,

PROD_DESCR VARCHAR2(50),

PRICE NUMBER

)

INSERT INTO WIDTH_BUCKET_TEST VALUES(1,'Pavilion dv6-3079TX Notebook',1200)

INSERT INTO WIDTH_BUCKET_TEST VALUES(2,'Aspire AS5820TG-524G50Mn Timeline Notebook',1150)

INSERT INTO WIDTH_BUCKET_TEST VALUES(3,'Satellite A660/07R 3D Notebook',1700)

INSERT INTO WIDTH_BUCKET_TEST VALUES(4,'HP Pavilion dv6-3138TX Notebook' ,900)

INSERT INTO WIDTH_BUCKET_TEST VALUES(5,'Aspire AS5742 Notebook' ,900)

INSERT INTO WIDTH_BUCKET_TEST VALUES(6,'HP G62-454TU Notebook' ,500)

INSERT INTO WIDTH_BUCKET_TEST VALUES(7,'Presario CQ62-402AX Notebook' ,480)

INSERT INTO WIDTH_BUCKET_TEST VALUES(8,'Satellite L640/05K Notebook',2000)

INSERT INTO WIDTH_BUCKET_TEST VALUES(9,'Asus N61 16 Notebook',1800)

INSERT INTO WIDTH_BUCKET_TEST VALUES(10,'Sony Vaio Notebook' ,1000)

If you look at the data above, the lowest Notebook price is 500 and highest is 2000. If I specify the number of baskets (i.e. buckets) as 3, then price range [500,999.99] would be in first bucket, [1000,1499.99] would be in the second bucket and [1500,1999.99] will be in third bucket. Oracle takes the number of buckets parameter and automatically decides the size of the bucket. Anything less than 500, will be in 0th bucket (i.e. not mapped) and greater than 1999.99 will come as 4th bucket. 

So, the SQL shown below explains how to use WIDTH BUCKET function to achieve this result


SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 500, 2000, 3)

FROM WIDTH_BUCKET_TEST
1
Pavilion dv6-3079TX Notebook
1200
2
  
2
Aspire AS5820TG-524G50Mn Timeline Notebook
1150
2
  
3
Satellite A660/07R 3D Notebook
1700
3
  
4
HP Pavilion dv6-3138TX Notebook
900
1
  
5
Aspire AS5742 Notebook
900
1
  
6
HP G62-454TU Notebook
500
1
  
7
Presario CQ62-402AX Notebook
480
0
Less than 500; 0th Bucket
8
Satellite L640/05K Notebook
2000
4
Greater than 1999.99, 4th Bucket
9
Asus N61 16 Notebook
1800
3
  
10
Sony Vaio Notebook
1000
2
Greater than 999.99, 2nd Bucket


The output is as per our explanation and we have got a total of five buckets i.e. 3 for our requirement and two by default to define the boundaries; both beyond and below. If the low value and high value are interchanged as shown below, then the output boundaries also get changed..As an example




SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 2000, 500, 3)
FROM WIDTH_BUCKET_TEST
1
Pavilion dv6-3079TX Notebook
1200
2
2
Aspire AS5820TG-524G50Mn Timeline Notebook
1150
2
3
Satellite A660/07R 3D Notebook
1700
1
4
HP Pavilion dv6-3138TX Notebook
900
3
5
Aspire AS5742 Notebook
900
3
6
HP G62-454TU Notebook
500
4
7
Presario CQ62-402AX Notebook
480
4
8
Satellite L640/05K Notebook
2000
1 (takes first bucket)
9
Asus N61 16 Notebook
1800
1
10
Sony Vaio Notebook
1000
3 (takes third bucket)


A value of 1000.01 would still have taken the second bucket in the second SQL example. To check this you can insert another record and run the same SQL..the new row's value will be as shown below


INSERT INTO WIDTH_BUCKET_TEST VALUES(10,'Sony Vaio Notebook 2' ,1000.01)
10    Sony Vaio Notebook 2    1000.01     2


If you attempt to give a negative value in WIDTH BUCKET for the number of buckets (parameter 4), then you get the following error


ORA-30494: The argument [4] of WIDTH_BUCKET function is NULL or invalid.


This error means, you have either specified a NULL or invalid value for number of buckets parameter. Also, if you give same values for both low value (parameter 2) and high value (parameter 3), you don't end up getting any classified buckets for our case. The 480 price row gets a value of 0, and all other (including 500) gets a value of 2, for the SQL example below


SELECT PRODUCT_ID, PROD_DESCR, PRICE, WIDTH_BUCKET(PRICE, 500, 500, 1)

FROM WIDTH_BUCKET_TEST

You also get an invalid identifier or invalid number error when the fourth parameter to this function is passed as a character etc.

India Australia World Cup Quarterfinal 2011 Watch Online

India Vs Australia, the second Quarterfinal match of the world cup 2011 takes place today at Ahmedabad..Either teams would be keen to make a match out of it and give themselves a chance to enter the semi-final of this cricket tournament. Lots of links are spread across the web to watch this match live for free..We will provide a list of links through which the viewers can watch this entertaining encounter online with high quality..stay tuned to this webpage and we will try to grab some  online links for the match before the match starts..If you have a link to share, just provide it here and we will publish it immediately..

Oracle XMLExists Function Example

We earlier discussed existsNode function in Oracle with examples. During our discussion, we mentioned that existsNode is deprecated with 11g Release 2 version of Oracle and Oracle recommends using XMLExists function instead. In this blog post, we will see what XMLExists is all about and run through some examples of using XMLExists against an XMLTYPE column in Oracle. To start with, consider a very simple set of XMLs as provided below;

<Laptop>
<Notebook>

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>

<Price>450</Price>

</Notebook>

<Notebook>

<Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title>

<Price>800</Price>

</Notebook>

</Laptop>
<Laptop>
<Notebook>

<Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>

<Price>700</Price>

</Notebook>

<Notebook>

<Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>

<Price>1200</Price>

</Notebook>

</Laptop>

Make sure that you push these two XMLs to our Orders table in the XMLTYPE column ORDERDATA. We have two rows of XMLData with us now and we can run some XMLExists Query on that to understand XMLExists better.

Objective: List only those rows that have a brand "Sony"

SQL: The SQL to achieve this objective with XMLExists is provided below. As shown in the SQL, we use XMLExists, where we get into the attribute of the Title Tag i.e. Brand with value of Sony. The PASSING clause of XMLExists tells the Query the exact input XMLDATA that needs to be used for the check.

SELECT ORDERDATA FROM ORDERS
WHERE
XMLExists('/Laptop/Notebook/Title[@Brand="Sony"]' /* Catch rows with Sony Brand */
PASSING ORDERDATA)

 

The output of this SQL will have only one row i.e.the XML that has the Sony as an attribute. As another example of using XMLExists, to list all the rows where at-least one of the Price tag has a value less than 500, the SQL provided below can be used

SELECT ORDERDATA FROM ORDERS
WHERE
XMLExists('/Laptop/Notebook[Price<500]' PASSING ORDERDATA)

Some important differences between XMLExists and ExistsNode are documented below

XMLExists
ExistsNode
Returns TRUE or FALSE
Returns 0 or 1
Cannot be directly used in a Query Select List, but can be used with a CASE expression
Can be used in a Query Select List
Supports XQuery Expressions
No support for XQuery Expressions
Latest
Deprecated with 11g R2

Row # 2 in the difference table indicates that XMLExists can be used with a CASE expression. An example of such an usage is provided below:

SELECT CASE
WHEN
XMLExists('/Laptop/Notebook[Price<=600][1]'
PASSING ORDERDATA) THEN

EXTRACT(ORDERDATA,'/Laptop/Notebook[1]/Title')
.getstringval()
ELSE
'NOT FOUND'
END
FROM ORDERS

In the SQL above, we check if the Price of the first Notebook element is less than or equal to 600. If so, then we use EXTRACT to dump the Title value of the "Notebook" element. Otherwise we dump 'NOT FOUND' in the output. This SQL gives an output as shown below (HP for first row, and NOT FOUND for the second row)

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>
NOT FOUND

This is a simple example for XMLExists function. In the upcoming blog posts, we will see how to use XQuery in XMLExists function.