Using iReport with Query Output

Because Oracle has stopped delivering Crystal Reports with PeopleSoft, I heard a few discussions about whether or not to license it for a new installation.  So, it’s an perfect timing to explorer an alternative tool.

Let me introduce JasperReports.  It is open-source, Java-based report generation tool.  The reports are XML files, but they have a tool called iReport that, in my opinion, is very similar to Crystal Reports.

Here’s the descriptions from their website:

iReport:

iReport is the free, open source report designer for JasperReports. Create very sophisticated layouts containing charts, images, subreports, crosstabs and much more. Access your data through JDBC, TableModels, JavaBeans, XML, Hibernate, CSV, and custom sources. Then publish your reports as PDF, RTF, XML, XLS, CSV, HTML, XHTML, text, DOCX, or OpenOffice.

Jasper Reports:

JasperReports is the world’s most popular open source reporting engine. It is entirely written in Java and it is able to use data coming from any kind of data source and produce pixel-perfect documents that can be viewed, printed or exported in a variety of document formats including HTML, PDF, Excel, OpenOffice and Word.

So, as an introduction, let me give you a walk through introduction of using iReport to design a report based on a Query.

Read More

Great Links: Oracle Database Administration Commands

This article is just to remember some links that I found while working on some database issues.  Maybe they will help someone else:

Viewing the Large Pool Size (from here):

SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool='large pool'
GROUP BY ROLLUP(name);

Here are the command to view the different memory related settings/parameters (from here):

show parameter sga_target

show sga

show parameter pool

Flushing cache, etc. (from here)

alter system flush buffer_cache;

alter system flush shared_pool;

alter system switch log_file;

Resources

Integration Broker error: Cannot establish HTTP connection

I have been fighting with this error message:

IB Error: Cannot  establish HTTP connection

Here’s the text of the message:

Cannot establish HTTP connection (158, 2842)

Can be cause by any of the following:
1. Cannot load certificates due to keystore password (not encrypted)
2. Keystore file not found
3. Proxy server not configured

Finding IB Logging

One thing I found was the error log thanks to the PeopleSoft wiki.  The wiki has a very good article on Integration Broker Logging.

For me, the logs where located at:

  • http://skphcm90/PSIGW/errorLog.html
  • http://skphcm90/PSIGW/msgLog.html

On the Advanced Properties page, I adjusted the ig.log.level from 2 to 5:

IB Setting Log Level

While that was a good tip to learn, that didn’t really help me.  It didn’t produce any extra information.

Finding a Solution

I actually found my solution on Oracle support in article [ID 1270683.1].

The fix is back on the Advanced Gateway Properties.  Go to PeopleTools > Integration Broker > Configuration > Gateways and click on the Gateway Setup Properties.

Gateway Properties

After logging in, click on the Advanced Properties Page link:

Advanced Properties Page

Then, find the secureFileKeystorePasswd option and copy the password down to the Password Encryption box.  Next, click the Encrypt button to get your encrypted version.

Copying password for IB

Finally, copy the encrypted version back up to the setting.

Applying Encrypted Password

Click OK, and exit out of the Gateway setup saving along the way.  Now, your error should be fixed!

Integration Broker: Message Already Used in WSDL

I ran across this error message the other day, and my web search didn’t return any results.  So, in case anyone else tries the same thing, here’s some info …

Error Message:

Message cannot be changed. Message used in exported WSDL.

Message cannot be changed. Message used in exported WSDL.

If you are like me, you want to change this message that you created, but you can’t.  The message is read only because you just created a WSDL.

You can delete the WSDL at: PeopleTools > Integration Broker > Service Utilities > Service Administration

Service Administration where you can delete your WSDL

You just search for your Service that includes the Message.  Then, you check the box for that service and click the delete button.  Now, you can head back to your message and you should find that you can edit it!

Configuring Integration Broker for PTF

Before you can start using the PeopleSoft Testing Framework, you have to get Integration Broker setup.  The last post walked you through installing the PTF client.  This one shows how I configured Integration Broker so that I could login with the client.

Much of this information is from PeopleSoft Test Framework Installation Guide.  PeopleBooks is also another good resource if you are looking for more help.

Read More

PTF Startup Error

This is a nice introduction to PTF as I have started playing with it.  Fortunately, the fix turned out pretty simple, so I thought it would make a nice simple blog post.

Here’s the error message:

If you have trouble viewing the image, it reads:

Unhandled exception has occurred in your application.  If you click Continue, the application will ignore this error and attempt to continue.  If you click Quit, the application will close immediately.

Could not find a part of the path ‘C:\Documents and Settings\psadmin\Application Data\PeopleSoft\PeopleSoft Test Framework\localoptions.xml’.

When I looked for the directory, the folder does not exist:

The simple fix: Create the path manually:

Now, it starts (and, it creates the localoptions.xml):