Oracle APEX: Query to generate data for time selection

Needed a way to generate an APEX LOV(List of values) for selecting time of day to schedule a job.
The way the argument is passed to dbms_job.next_date had to deal with decimal values to indicate quarter of an hour.

e.g.
  -- dbms_job.next_date(job_number,TRUNC(NEXT_DAY(SYSDATE,p_day))+p_time/24)
  -- dbms_job.interval(job_number,'SYSDATE+1/24');


After fiddling with for..loop construct for some time, decided to settle for the following assuming sys.all_objects(it could be any table though as long as it has required number of rows) has enough rows, which is going to be typically higher than what is needed for this purpose.

select rownum,
          rownum -1,
         (rownum -1)/4,
         floor((rownum-1)/4) || ':' || substr(mod(((rownum-1)*60/4), 60) || '0', 1, 2)
from sys.all_objects
where rownum <= 96;

(This is not the eventual query put in APEX LOV as it needs only two columns to be returned)

SAP BPC - Catch-me-if-you-can problems during planning cycle

Reports used to run normally and then it would start crawling such as taking 5 to 10 mins. to complete. When one starts to check things out from IT, the problem usually goes away and reports were back to normal runtime. This problem used to aggravate towards the end of the planning cycle, and we concluded that it was due to the load as everyone jammed the system. This is the angle we pursued for some time, but could not nail it.

After a few days, we started learning from IIS logs, that this problem was evident even when one or two users were on the system. This invalidated the theory that the load was the sole reason for the hiccups.

After some more digging, we were able to tie the occurrence of the problem to the "stats" health light SAP BW was displaying for a cube under Manage Action/Performance tab.
With that, we started running "rsvc" transactions at periodic intervals during busy days.
Earlier runs of rsvc were reporting green whenever it was run, so did not think this was the likely cause.

Once we started running it more often, we netted the problem - the "F-Fact" (temporary or new record) table of a cube was having invalid stats when an input template was submitted.
The "E-Fact" (more permanent or old record) table of the same cube was having up-to-date stats, and it was not impacted at all.

Trapping some of the report queries from Oracle Grid Control showed that records from both "E and F" Fact tables were joined with master data dimension tables.
It was guessed that Oracle optimizer could not do well when it ran into a huge table(E-Fact - 20+ M records) with good stats and small tables (F-Fact - 4K records) with invalid stats.

So approached the DBAs to press a re-compute stats job on the affected F-Fact table every 15 mins and the problem is pretty much gone since then.
       execute dbms_stats.gather_table_stats(..., ...);

If it were to happen again, decided to increase the frequency to every 10 mins. or even 5 mins.

Break from Blogging and SAP BPC

Took a break from blogging as much of time was spent towards stabilizing an SAP BPC 7.5 environment.
Weeks of troubleshooting identified a number of performance hogs across the landscape.

We derived some tangible improvements on these fronts:

• Excel report template with more lines than enough had a direct impact on Excel performance due to EV-DRE macros - big saving on Desktops
• Newer Laptops with Intel i7-3740@2.7GHz performed better than Intel i5-3320@2.6GHz because of CPU requirements for BPC reports anyway.
• BW ABAP servers were re-sized to have 4 CPUs and 16GB memory each
• Running reports for YTD measure led to pinning Server CPUs down with the earlier version.
• This problem seemed to have lessened since the upgrade to 7.5 SP15 though
• Computing oracle table stats on an F-Fact table frequently also helped fix catch-me-if-you-can problems during planning cycles - will dedicate a blog for this topic soon.

SQLLDR, with Oracle OC4J 10.1.3.4, errors out.

sqlldr ($ORACLE_HOME/bin/sqlldr.exe), installed as part of OC4J 10.1.3.4, fails with the following error when executed.

..>sqlldr
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=UL.


Process explorer showed that it was looking for $ORACLE_HOME/rdbms/mesg/ulus.msb, and it did not find it there. Not sure whether the bundled version is same as the Oracle client install on a different computer, but found this file anyway and copied it over to OC4J/rdbms/mesg/ directory.

It worked!

Oracle BPEL - Slow "Search by Title" and "Tree Finder" Actions

Search by "Title" in the instances tab and also "Tree Finder" in an individual instance page got really slow - 10 - 24 secs, in 10.1.3.4 instance.

The following custom indexes were added to bring them below 0.06 secs. in our instance.

See the highlighted indexes added recently.

SQL> select index_name, index_type, table_name, uniqueness from all_indexes where table_name = 'CUBE_INSTANCE';

INDEX_NAME        INDEX_TYPE            TABLE_NAME     UNIQUENESS
----------------- --------------------- -------------- ----------
STATE_IND         NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_CUSTOM4        FUNCTION-BASED NORMAL CUBE_INSTANCE  NONUNIQUE  
CI_PARENT_ID_IDX  NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_TITLE_IDX      NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_ROOT_ID_IDX    NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_CONV_ID_IDX    NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_PK             NORMAL/REV            CUBE_INSTANCE  UNIQUE     
CI_CUSTOM2        NORMAL                CUBE_INSTANCE  NONUNIQUE  
CI_CUSTOM3        NORMAL                CUBE_INSTANCE  NONUNIQUE  

Multi-line match in Java RegEx

Recently had to format some messages for HTML display
     - by adding "<br>" to plain text where newlines were found.
     - If a line already ends with "<br>", then do not add extra "<br>".
     - break tags were appearing as "<br>", "<br/>", &quo
t;<br />" and also as "<BR>", "<BR/>", "<BR />
"
     - goto be a multi-line match.
     - wherever empty lines are that had to be replaced with "<br>"


...
import java.util.regex.Pattern;
...
Pattern pat = Pattern.compile("((?<!\\<(?i)br(?-i)\\s?/?\\s?\\>\\s{0,100})\\n)|(^\\s*$)", Pattern.MULTILINE);
System.out.println(pat.matcher(mystring.trim()).replaceAll("<br>\n")); // Adding a new line to easily spot the newly added "br" tag.


Without \\s{0,100} JVM threw
      "java.util.regex.PatternSyntaxException: Look-behind group does not have an obvious maximum length near index ..."

Tomcat7 Registry Settings

Needed to adjust the heap settings for Tomcat running as a service, but tomcat7w.exe did not launch on a Windows 2008 server, so had to dig out the registry settings.

Right click to open this image in a separate window to see the complete image.

Outlook Rule to Capture Replies and Forwards While Ignoring Original Email

I get copied on a lot of alerts from many systems and tools every day, which get filed away in specific folders by using Outlook rules.

Some of the alerts get responded to by a tech person, and I was interested only in those responses.
I thought I will share one of rules, so that responses and forwards get dropped in my "inbox", not in "File-Away" folders, to get my immediate attention.

CVS Tag plugin failure for Hudson

CVS Tag plugin fails with the following exception when it runs.
 This was due to cvs-tag version 1.5 being not compatible with Hudson 2.2. This required recompilation of CvsTagPlugin ver 1.5 to work with Hudson 2.2 API.

 If you need the modified source code for CvsTagPlugin, leave a message for me. It sounds like new objects such as Locations and Project have been introduced in Hudson 2.2 that support the method calls that used to fail.

Oct 21, 2011 4:44:43 PM hudson.model.Executor run 
SEVERE: Executor threw an exception java.lang.NoSuchMethodError: 
hudson.scm.CVSSCM.getCvsRoot()Ljava/lang/String; at hudson.plugins.cvs_tag.CvsTagPlugin.perform(CvsTagPlugin.java:81) at hudson.plugins.cvs_tag.CvsTagPublisher.perform(CvsTagPublisher.java:66) at hudson.tasks.BuildStepMonitor$3.perform(BuildStepMonitor.java:36) at hudson.model.AbstractBuild$AbstractRunner.perform(AbstractBuild.java:630) at hudson.model.AbstractBuild$AbstractRunner.performAllBuildSteps(AbstractBuild.java:608) at hudson.model.AbstractBuild$AbstractRunner.performAllBuildSteps(AbstractBuild.java:584) at hudson.model.Build$RunnerImpl.cleanUp(Build.java:168) at hudson.model.Run.run(Run.java:1410) at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:46) at hudson.model.ResourceController.execute(ResourceController.java:88) at hudson.model.Executor.run(Executor.java:145)

Multiple instances of Apex Listener (on Tomcat)

An instance of an APEX listener is configured for a single instance of Oracle database.
If you need to support mulitple environments such as Dev and QA running on different instances of Oracle databases,
then the listener can easily be redeployed with a new context name.

This is what is required on a Tomcat server hosting Oracle APEX listener.

- Stop Tomcat
- Copy apex.war as myapex.war under tomcat/webapps(images need not be deployed for this instance as they will be served from original /i/ location)
- Copy tomcat/temp/apex as tomcat/temp/myapex
- Remove tomcat/temp/myapex/apex-config.xml
- Start tomcat
- Configure the new listener by opening http:/tomcat_server:port/myapex/listenerConfigure

Olite: to_timestamp(...) not working

I am still running olite for an old version of OC4J. Turning on sensor values for in a BPEL process started throwing errors as the query used to to_timestamp(...) which was not working in my version of olite.

After going through Oracle Olite docs, this how I came up with a temp-fix.

- Created a Java Class.
import java.sql.*;
public class Formatter {
public static Timestamp to_X(String date, String format) {
return new Timestamp(System.currentTimeMillis());
}
}

- Loaded this class into olite
SQL> create java class using bfile ('C:\Oracle\10.1.3.1\OracleAS_1\bpel\bin', 'Formatter.class');

- Bound Formatter.to_X(...) to to_timestamp(...) in olite.
SQL> create or replace function to_timestamp(a varchar2, f varchar2)
return timestamp as language java
name 'Formatter.to_X(java.lang.String, java.lang.String) return java.sql.Timestamp';

- Do not forget a commit after this.

Very slow file open dialog/"Save As" on Windows 7

I was recently experiencing very long delays before the "File Open" dialog opened up whenever "Save As" was clicked from Word or Excel. The blue wheel was spinning and spinning for minutes, before the dialog allowed me to choose anything. Finally found out that it was all related to a recent installation of SAP Portal Drive Client on my Windows 7 x64 laptop. Removing this client solved the problem immediately. I will update this blog later when I find out what in the tool was causing such a horrible delay.

OC4J - File interaction spec and last modified date.

The last modified date used by the File or FTP Adapter on BPEL PM or ESB deployed in an OC4J container can be found in
OC4J/j2ee/home/fileftp/controlFiles/_process-name~ver_/_mangled_name_/inbound/lastModifiedDate.txt file.

This file seems to be maintained for every process version, so deploying a new version of the a process will process any residual files in the incoming directory for the second time if the file/ftp adpater is not configured to archive or delete the incoming file.

CVS update failures from Hudson

Found out that permission denied errors causing CVS checkout to fail in Hudson was related to Symantec auto-protect running on the build VM. Added security exception to Symantec for D:\hudson\builddir.
See screenshot for exception.

---------------------- CVS checkout failure in Hudson console

$ cvs -Q -z3 -d :pserver:user@cvsserver:/sourceCode co -P -r CVS_TAG -d projectDir -D "Friday, December 9, 2011 9:15:28 PM UTC" projectDir


cvs checkout: cwd=D:\hudson\builddir ,current=D:\hudson\builddir
cvs server: cwd=C:\cvsnt\Temp\cvs-serv1120a ,current=C:\cvsnt\Temp\cvs-serv1120a

cvs [checkout aborted]: cannot create CVS/Entries.Extra.Old for copying: Permission denied

FATAL: CVS failed. exit code=1
------------------------

Oracle SQL Developer Configuration

- Move <sqldeveloper_home>/sqldeveloper/bin/sqldeveloper.conf away so that a new JVM can be selected when SQL developer starts next time.

- All connection configurations are stored in %APPDATA%/SQL Developer/system<sql_dev_ver>/0.jdeveloper.db.connection.<jdev_mod_ver>/connections.xml

- If JDev windows is not visible (mapped outside the screen), then edit MainWindow.x or MainWindow.y property in these files.
%APPDATA%/SQL Developer/system3.0.02.37/o.sqldeveloper.11.1.2.2.37/ide.properties
There could be more than one file in your directory such as
%APPDATA%/SQL Developer/system3.0.04.34/o.sqldeveloper.11.1.2.4.34/ide.properties, so edit the appropriate one.

APEX and Active Directory(what is in bindDN?)

I was setting up Active Directory(AD) authentication for an Oracle APEX application. The settings seemed to be straight forward, but authentication did not succeed. So it was time to fire up ldapsearch from the Oracle db server serving APEX, but found same issues.

After a lot of googling, I did not find any issue being documented for this problem. Then I thought this may not be an issue per say, but how information is perceived by the AD server or how these accounts were set up in AD.

So went further digging on our side ...

I was using samaccountname for bindDN that seemed to work for some application(service) account, but not for my own account. Found some interesting info related to AD or how it was set up in our environment.

- If "samaccountname" to be used for bindDN, then it need to be suffixed with "@domain-name.com" as appropriate.
- One can use "cn" (sn, givenName) as the bindDN if it leads to a unique entry.
- One can use "dn" for bindDN, but this is not user friendly as it is a long one with all the OU and DC info.


Then there were a couple of service accounts that did not seem to have any problem authenticating using samaccountname only, without @domain-name.com appended to it. This one was very confusing initially, but found out that these service accounts were created with no last name(sn), so their first name(givenName) matched their cn and samaccountname. In essence, we thought we were specifying samaccountname for the service accounts, but it was reall their "cn".

So this is how APEX authentication scheme screen finally looked like.

Google search results were hijacked by search.search-tab.com

My wife told me a few days ago that the links from Google search results were hijacked on our home computer. Clicking on links from google keyword search results in Firefox showed unrelated pages, but cutting and pasting the URLs in the address bar seemed to work fine.


She logs in as a restricted Windows user and has been doing Alt+F4 on any ad-window or unrelated pop-ups that beat Fire-fox's block pop-up settings.

Malware bytes's Anti-malware some times showed that it blocked illegal access to an IP address, but it did not happen every time unrelated content showed up. Running Malware scan or Norton did not find anything whether it was a quick scan or full scan. After googling for some time, the problem was found to be related to "keyword.url" configuration property accessed through "about:config" URL. This URL was pointing to "search.search-tab.com" on our home computer. So I reset this property, restarted Firefox to see this problem come up again and the URL was still pointing to search-tab.com.

So I decided to manually clean up this entry found in both user.js and prefs.js under
Documents and Settings/<username>/Application Data/Mozilla/Firefox/Profiles/.default/ directory.

So it seems to be working since then, but I wonder how long?

Disk upgrade

As my old DELL has become slow to boot, started to crank the disk randomly for a long period of time and freezing during this time, I decided to try out an SSD like other people in the group. My laptop was blazing with SSDs, but it lasted for about 3 months as the SSD crashed one day.

So I decided to try out a 7200 RPM disk as the old one was 5400 RPM.

I went ahead and imaged the new disk and also collected manual stats for a normal reboot with no new applications installed. I did not want to use a tool to benchmark before and after as it would tell me the stats, but not how it feels like. So I picked up my phone, paper and pencil and then started the timer.

Here are the stats for a DELL latitude – D620

                                   5400-RPM 7200-RPM

Dell boot appears                   0         0

Windows XP boot screen disappears  27        21
Blue background appears            54        38
Welcome to Windows, login screen   76        54
appears


Entered username and password       0         0
while timer is reset with
left hand.
Custom background picture           6         1
appears                                     
Quick launch bar appears           38        33
Desktop flashes after loading      83        48
all the icons
Disk activity                      183       Stopped.
                                  Still

SQL pitfall ('NOT IN')

It was frustrating recently to see that the following simple SQLs behave fundamentally different though one result was supposed to be the negative of the other.

-- Lists emloyees present in the tmp table.
select * from empl empl
where empl.empl_id in (select empl_id from tmpTable);

-- Expected to list the employees that are not present in the tmp table
-- Very similar to the above query, but using 'not in' construct.
select * from empl empl
where empl.empl_id not in (select empl_id from tmpTable);

No results were returned from this query though a number of records were expected to be returned.

After digging into the basics and spending quite a bit of time googling, a record with null emloyee id was found in the tmpTable, which originated from the CSV source file used to populate this table.

So I put in a filter 'where empl_id is not null' for second query (based on 'not in') alone. Realized that I should have used 'not exists', and would have avoided this pitfall.

Moving BPEL Data and Files to a test environment - 2

We did another round of prod to test move of data and files, a few days ago, but found out this time that processes with wait activities did not wake up even after of expiry of the time.

After refreshing alarm table a few times in vain and  frustrated digging attempts here and there, found out that the column orabpel.work_item.cluster_node_id had the IP address and a port number of the production cluster nodes. I guess these entries prevented these processes from waking up in the test environment as the test server IP was different from production servers.

So this column was nulled, then restarted oc4j_soa and then all of those pending processes woke up to move further.