Sunday, April 25, 2010

Oracle EBS Applications Manager log

I recently came across some strange things on a new Oracle e-Business suite environment release 11.5.10.2 which was just installed as a clone from production on a new server. The issue was that if you wanted to look into the ICM files to check the status of an internal concurrent manager you would be presented a non descriptive error. The error stated “An error has occurred!” and no log would appear in the screen.


If you open the log via a forms session you would be presented with the correct log.


The issue is definitely related to the HTML screens only, so I decided to do a trace of it when the error appears so we would be able to get some more meaning besides the error message “An error has occurred!”. In Oracle Application Manager you can set the log level to dev. Via “Site Map”> “Applications Manager Log”. Here you can set the log level to dev.


After setting the log level to dev I reproduced the error and found the following stack:

[Thread-1046][10:11:44:986, 4/22/10] IN: getFileContentsForURL
[Thread-1046][10:11:44:986, 4/22/10] urlStr=http://INTERNAL.SERVERNAME.COM:11415/OA_CGI/FNDWRR.exe?temp_id=2080498240
[Thread-1046][10:11:44:986, 4/22/10] in getTmpDir(), dir:/opt/oracle/oont/apps/D301O/ora/iAS/Apache/Jserv/logs/iAS/Apache/Jserv/logs/oam/tmp
[Thread-1046][10:11:44:986, 4/22/10] LogHandler:BEGIN=22 Apr 2010 08:11:44 GMT
[Thread-1046][10:11:44:986, 4/22/10] urlStr=http://INTERNAL.SERVERNAME.COM:11415/OA_CGI/FNDWRR.exe?temp_id=2080498240
[Thread-1046][10:11:44:992, 4/22/10] java.net.UnknownHostException: INTERNAL.SERVERNAME.COM
at java.net.InetAddress.getAllByName0(InetAddress.java:1133)
at java.net.InetAddress.getAllByName0(InetAddress.java:1098)
at java.net.InetAddress.getAllByName(InetAddress.java:1061)
at HTTPClient.HTTPConnection.getSocket(HTTPConnection.java:1907)
at HTTPClient.HTTPConnection.sendRequest(HTTPConnection.java:2922)
at HTTPClient.HTTPConnection.handleRequest(HTTPConnection.java:2150)
at HTTPClient.HTTPConnection.setupRequest(HTTPConnection.java:3561)
at HTTPClient.HTTPConnection.ExtensionMethod(HTTPConnection.java:1570)
at HTTPClient.HttpURLConnection.connect(HttpURLConnection.java:252)
at HTTPClient.HttpURLConnection.getInputStream(HttpURLConnection.java:481)
at oracle.apps.fnd.oam.sdk.util.log.LogHandler.getFileContentsForURL(LogHandler.java:388)
at oracle.apps.fnd.oam.sdk.util.log.LogHandler.getFileContents(LogHandler.java:628)
at oracle.apps.fnd.oam.sdk.util.log.LogHandler.refreshLogFile(LogHandler.java:936)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at oracle.cabo.servlet.event.MethodEventHandler.handleEvent(Unknown Source)
at oracle.cabo.servlet.event.TableEventHandler.handleEvent(Unknown Source)
at oracle.cabo.servlet.event.TableEventHandler.handleEvent(Unknown Source)
at oracle.cabo.servlet.event.BasePageFlowEngine.handleRequest(Unknown Source)
at oracle.apps.fnd.oam.servlet.ui.oamPageFlowEngine.handleRequest(oamPageFlowEngine.java:791)
at oracle.cabo.servlet.AbstractPageBroker.handleRequest(Unknown Source)
at oracle.cabo.servlet.ui.BaseUIPageBroker.handleRequest(Unknown Source)
at oracle.cabo.servlet.PageBrokerHandler.handleRequest(Unknown Source)
at oracle.apps.fnd.oam.servlet.ui.OAMServlet.doGet(OAMServlet.java:224)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:499)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:595)

What we notice here is a URL named http://INTERNAL.SERVERNAME.COM:11415/OA_CGI/FNDWRR.exe?temp_id=2080498240 and this happens to be the URL that will open if you try to get the page via a forms session. So what basicly happens is that java is trying to parse a URL into a document and for some reason the java code is unable to resolve the domain name where your browser is able to.

In our case it turned out that the resolution was to add the hostname/domainname to /etc/hosts and the issue was resolved.




Wednesday, April 21, 2010

Oracle flexfields as LOV

The issue with most systems where users have the option to enter data in a free form is that as soon as they enter the information you have the possibility that they make a typo. For a textfield like a description this is from a system point of view not a very big issue, however if you want to base logic on the information entered it can be a very big problem. To prevent this you should design applications and interfaces always in such a way that a user can make as little as possible mistakes. You can for example use dropdown menus where a user can select values instead of entering them by hand. This will reduce the number of possible mistakes.

In Oracle E-Business suite you have besides the standard fields where users can enter information also a option to add extra fields (on most of the screens, not all). Those fields are referred to as flexfields. You have the option to define a Oracle flexfield as a free text field however you can also make it a dropdown menu or LOV (List Of Values).

Most Oracle consultants are aware of how to create a free text field to be used as a flexfield. The option of creating a flexfield as a LOV is however somewhat less commonly know. Please find below an example of how to create a DFF (Dynamic FlexField) with a LOV (List Of Values). We used in this example a request to add a LOV as a Flexfield to a order line in order management.

Step 1:
Identify where you want to create a flexfield. In our case this is in Oracle Order Management at order line level. Just query any random order in the screen and get the record history information of the line via “Help” > “Record History”. You will get a screen like the one below:


Here you will see that the table name is OE_ORDER_LINES_V. This is however not correct, the _V is indicating that this is not a table, it is a view. And as flexfield information can only be stored in a table we have to find out which table is used. In 99 percent of the cases you can remove the _V and you have your table name or in some cases search for something else than the V In our example the table is OE_ORDER_LINES_ALL.

Step 2:
Select the responsibility “Application Developer” and navigate to “Flexfield” > “Descriptive” > “Register”. Here we will query the descriptive flexfield which is already defined for this table. We can do so by executing a query against the table name which we just learned is OE_ORDER_LINES_ALL.


From this screen you can see that the application is indeed “Order Management” and that the title is “Additional Line Attribute Information”. We note this down as we need it in a following step.

Step 3:
Select the responsibility “Application Developer” and navigate to “Flexfield” > “Descriptive” > “Segments”. Here you can query the needed segment (or create one). In our case we search for Application = “Order Management” and Title = “Additional Line Attribute Information”.


Select a Context Field Value you want your new flexfield to appear in (in our case we use Global Data Elemenst) and click the “Segments”button.

Step 4:
Here you can enter a new line (in our case line 90) with the information you need.

However, before you enter information in the “value set” field click on the “value Set” button. This will open a screen where you can enter the information about the value set that later will hold your preset data for your LOV which will appear in your flexfield.
It is smart to pick a name starting with XX_VS where XX stands for custom setup and the VS is representing that it is a value set. After you have set all the information you want you can save it and return to the previous screen where you can enter the new “value set name” into the “value set” field. Save you work and close the screen.

Step 5:
Select the responsibility “Application Developer” and navigate to “Flexfield” > “Descriptive” > “Values”. Query for your new Value Set.

Here you can enter the preset information as you whish. After you are done, save your work.

Step 6:
Go to the screen where you intended your flexfield to be a check it. If all is ok you should now have your flexfield as a List Of values in your screen.

Saturday, April 17, 2010

Oracle database geocoding with Google earth

Recentley someone asked me if I could help with a project where he was needed to gecode addresses and store this information in a Oracle database. For those who do not know what geocoding is, wikipedia has the following to say about it;

"Geocoding is the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or zip codes (postal codes). With geographic coordinates the features can be mapped and entered into Geographic Information Systems, or the coordinates can be embedded into media such as digital photographs via geotagging."


His original plan was to use a BASH script to download information from Google Earth and grep the needed information from the results from Google. The second step would be to load the data in the database and start using it. However I used a different technique some time ago to do something similar and we came to the conclusion all could be done from the inside the database itself. In Oracle 11G (and other releases) you can use the function HTTPURITYPE to retrieve information from a web address using the HTTP protocol. As google maps is using the HTTP protocol you can also use it to download the needed information. This is working the best if you retrieve the information in a XML format.

Google is providing you the option to download information from the google maps website in a XML format. The XML format used to described the information you are getting from Google is named KML.

"Keyhole Markup Language (KML) is an XML-based language schema for expressing geographic annotation and visualization on existing or future Internet-based, two-dimensional maps and three-dimensional Earth browsers. KML was developed for use with Google Earth, which was originally named Keyhole Earth Viewer. It was created by Keyhole, Inc, which was acquired by Google in 2004. The name "Keyhole" is an homage to the KH reconnaissance satellites, the original eye-in-the-sky military reconnaissance system first launched in 1976. KML is an international standard of the Open Geospatial Consortium. Google Earth was the first program able to view and graphically edit KML files, and other projects such as Marble have also started to develop KML support."

Lets say for example I want to have information about the location of my office I can check the google maps website by entering the following URL http://maps.google.com/maps?f=q&source=s_q&geocode=&q=Netherlands,Utrecht,papendorpseweg 100&sie=UTF8 now we do however still have the information in a HTML format while we want to have the coordinates in a XML format. By adding &output=kml to the url we are not served a website we are served a XML file http://maps.google.com/maps?f=q&source=s_q&geocode=&q=Netherlands,Utrecht,papendorpseweg 100&sie=UTF8&output=kml . If we look in the file we will find all the information about the coordinates we need. The file looks as below:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Placemark>
<name>Papendorpseweg 100, 3528 Utrecht, The Netherlands</name>
<address>Papendorpseweg 100, 3528 Utrecht, The Netherlands</address>
<styleUrl>root://styleMaps#default+nicon=0x304+hicon=0x314</styleUrl>
<Point>
<coordinates>5.089984,52.064688,0</coordinates>
</Point>
<LookAt>
<longitude>5.089984</longitude>
<latitude>52.064688</latitude>
<range>1000.000000</range>
</LookAt>
</Placemark>
</kml>


Now we need to get this information into the database, the first step is to download the file using HTTPURITYPE and after that we will not store the XML file, we will query it in the same statement that downloads the file and extract the information by making use of EXTRACTVALUE. This will result in a example query as below:

SELECT
EXTRACTVALUE(value(googleData), '/Placemark/name', 'xmlns="http://earth.google.com/kml/2.0"') as "NAME"
,EXTRACTVALUE(value(googleData), '/Placemark/address', 'xmlns="http://earth.google.com/kml/2.0"') as "ADDRESS"
,EXTRACTVALUE(value(googleData), '/Placemark/styleUrl', 'xmlns="http://earth.google.com/kml/2.0"') as "STYLURL"
,EXTRACTVALUE(value(googleData), '/Placemark/Point/coordinates', 'xmlns="http://earth.google.com/kml/2.0"') as "COORDINATES"
,EXTRACTVALUE(value(googleData), '/Placemark/LookAt/longitude', 'xmlns="http://earth.google.com/kml/2.0"') as "LONGITUDE"
,EXTRACTVALUE(value(googleData), '/Placemark/LookAt/latitude', 'xmlns="http://earth.google.com/kml/2.0"') as "LATITUDE"
,EXTRACTVALUE(value(googleData), '/Placemark/LookAt/range', 'xmlns="http://earth.google.com/kml/2.0"') as "RANGE"
FROM
TABLE(
XMLSEQUENCE(
EXTRACT(
HTTPURITYPE(
'http://maps.google.com/maps?f=q&'||'source=s_q&'||'geocode=&'||'q=netherlands,Utrecht,papendorpseweg+100&'||'ie=UTF8&'||'output=kml'
).getXML()
,'/kml/Placemark', 'xmlns="http://earth.google.com/kml/2.0"'
)
)
) googleData;


This query itself is not very useful for the solution I proposed however if you add this query to a package that will do a lot more like getting a list of addresses and adding the address to the query as a variable you will be able to geocode lots of addresses directly from your database by making use of Google without any outside scripts. One thing you have to remember is that you will need access to outside locations, you might retrieve a error like "ORA-24247: network access denied by access control list (ACL)". In this case you will have to make sure you have set the access control list correct for the user you are using to execute the query. You will need to be able to lookup maps.google.com and earth.google.com . To set the access control list correct you can have a read in this blogpost I have been writing some time ago.

Friday, April 16, 2010

emctl ulimit: 25: bad number

I have recently installed a Oracle 11G R2 database on my Ubuntu laptop. All is working fine however one little issue is still bugging me. Whenever I start the dbconsole with emctl it is giving me a "ulimit: 25: bad number" warning. Even do all is starting up fine the issue is somehow bugging me. I have done some research and found that the rootcause thanks to some people at the oracle forums page.

The issue can be found in the file $ORACLE_HOME/bin/commonenv which is been called when you use emctl.

"If you type this line into bash, it works just fine, but emctl begins with the line #!/bin/sh, which on Ubuntu systems is symbolically linked to /bin/dash. Now, ulimit is a built-in shell command, not an executable file, and bash and dash seem to have a different syntax. If you type "dash" at a shell prompt to start a new dash shell, and type that particular ulimit command, it will complain about a bad number. (I'm not sure what the "25" represents, that number seems to be related to the number of lines in the shell script in which the command is found.) Be sure to type "exit" when finishing this test to return to bash or whatever your preferred shell is."

What you can do to solve the issue is to change the first line of the emctl script itself from:
#!/bin/sh

into:
#!/bin/bash

this way you will call the bash shell explicitly and the bash version of ulimit is called with the correct syntax. Even do it is not a big issue and you can ignore the error message I just want to have a tidy system which is not bugging me with error messages. As Ubuntu is not a certified system (still) you can encounter things like this and you just have to find a way around it.

Monday, April 12, 2010

Pick the right addwords

Addwords is one of the money machines from Google. This is a way for every business to place adds on inside the Google search results and on pages where people have placed Google adds. For a pre-set amount you can show your advertisement text to the world. All this is done based upon keywords you have to provide or as Google states it in the below video:

“You choose your keywords, words and phrases that relate to your business and your ass appear when people search for that terms.”





So one of the most important things is to pick keywords that relate to your business and as this is costing you money and it will be money wasted if your adds will be shown by keywords that do not relate to your business or the people searching for something. So Picking them can be a tricky business. You can find a short video below on picking them correct:



So the keywords are for the people who are looking for you, you might know all the ins and outs of your business and your products however your potential customers might not. So pick words people are searching for and that might be in many cases not be a product name or something like that. People are looking for an ISP not for yourISPname.


Friday, April 09, 2010

Oracle SAAS

SAAS or Software As A Service, also Oracle is working hard on the SAAS part. I even worked some time at a company providing Oracle solutions like E-Business suite as a SAAS solution. Wikipedia has the following to say about SAAS:

Software as a service (SaaS, typically pronounced 'sass') is a model of software deployment over the internet. With SaaS, a provider licenses an application to customers for use as a service on demand, either through a time subscription or a “pay-as-you-go” model. Also known as “software on demand,” the SaaS model allows vendors to develop, host and operate software for customer use. Rather than purchase the hardware and software to run an application, customers need only a computer or a server to download the application and internet access to run the software. The software can be licensed for a single user or for a group of users

Oracle is already running a large datacenter in Austin Texas so they can host their own SAAS solutions called the Austin datacenter for Oracle On Demand.




Oracle has to state the following about SAAS:
"The Oracle Platform for SaaS includes Oracle Database, Oracle Fusion Middleware, Oracle Enterprise Manager, and Oracle VM, a comprehensive, open and integrated set of technologies that allow ISVs to build, deploy and manage SaaS and cloud-based applications. The Oracle Platform for SaaS provides ISVs a single, integrated platform for both on-premise and cloud-based deployments, allowing ISVs to offer their customers a choice in where to run their software. Additionally, the Oracle SaaS program provides business and technology support to ISVs, hosting service providers and system integrators."

I just came across a nice recording about Oracle SAAS solutions you might want to check.


Tuesday, April 06, 2010

Not a valid responsibility for the current user

Some behavior I noticed in Oracle EBS release 11 (11.5.10.2) turns out to be still in Oracle release 12. When you add yourself a responsibility which makes use of JSP pages it might happen that if you try to enter one of those screens you get a error message something like:

“iStore and Sales Management is not a valid responsibility for the current user. Please contact your System Administrator. “

Here can substitute “iStore and Sales Management” with whatever responsibility you have added and makes use of JSP pages. It also turns out that from the JSP page menu you are unable to see the newly added responsibility.

The issue is that the webserver cache is still thinking you do not have been granted this responsibility and because of this it is throwing you this error page. A solution is to flush the cache. You can flush the cache by (A)rebooting the apache webserver, (B)deleting the cache object by hand or (C) using the Oracle EBS Applications Administration pages.

The Applications Administration responsibility is providing you the option to flush the cache manually without having to reboot the apache webserver or diving into the cache objects. A downside of this is that those pages are also not form object but JSP pages so if you do not have this responsibility already you will not be able to use it.

Go to Application Administration and the “Core Services” tab, select the sub-tab “Caching Framework” and on this page select “Global Configuration”. On this page you will find a button “Clear All Cache” You will receive a warning like this:

“Clicking on 'Yes' will clear all the caches across all the mid-tiers. This will impact performance. Are you sure you want to proceed? “

This is not a false warning, as all java objects will have to be JIT compiled again and placed in the cache your applications will be slower until they are used at least once. So you might want to time the moment you do this. However, after clicking the button the cache will be flushed and you will have the ability to access the responsibility without any issue.






Become a software specialist at the NHTSA

The Morning Call is reporting on US senators who are slamming against the NHTSA, National Highway Traffic Safety Administration. The NHTSA is responsible for making sure the vehicles driving on highways are save for use. They should test and check new cars and equipment and clear it to be used in the US.


The issue the senators are bringing up is that the NHTSA are more concerned on low tech things and are not looking enough into the technical details of “new” technology. Things like checking the chips and computer software in the Toyota cars were not looked into enough or in a correct way.


"I don't know if NHTSA turned a blind eye because they didn't understand chips or the electronics," said LaHood, who has been in his position since early 2009 and oversees NHTSA.


This makes one wonder if government agencies like the NHTSA are (ever) able to pick up speed on new technologies like the complex computer software of a new type of car. Possibly a government, and not only the US government, should outsource things like this to a company who might be better equipped and who has better trained and qualified people to look deeply into software and chip technology.


The software bugs found in the software from Toyota is something you should not detect easily. Toyota has tested it extensively and the bug will be most likely something hard to spot. Maybe it is a good idea that a organization like the NHTSA demands a second software test and debugging from a third party who is specialized in testing and car software.


In the “old” days testing was done by developers and groups of users who tested the software and reported the results back so developers could repair the bugs. Today a lot of software testing is done automatically by executing millions of scenarios and receiving automated reports on what went wrong under which conditions. A company like Vectorcast for example is specialized automated testing of embedded systems written in C, C++ or Ada.

TinyMe 2010 'Acorn'

Some time ago I had a “discussion” with my manager regarding Linux distributions. He owns a old home PC and wanted to use it as a Linux workstation. A issue he was facing was that the current Linux distributions are asking more and more from your workstation. The same as new windows versions Linux will demand more computing power from your workstation.

As computers grow in power this is not a real issue however if you have an old workstation you would like to use a distribution that is not demanding a lot of your workstation. You can look into old versions of distributions however they might have old bugs and not all the functionality that you want from it.

Now, you have a alternative TineyMe is a Mandriva based Linux distribution. Currently you can download "TinyMe 2010 'Acorn' i586 Release Candidate 1. TineyMe is especially for those cases where you would like to install a light Linux version on a small footprint or when you have a old PC and still would like to keep all the new features.

Running Linux on a old platform, you might want to check TineyMe.