Removing file extension in Windows

REM Use "ren/rename" as below, and it removes one extension at a time.

ren *.csv.tar.pgp  *.""

Dynamic Like Searches in Oracle

https://stackoverflow.com/questions/4612282/dynamic-like-statement-in-sql - Works great in Oracle 11.2.x - join "on column like ...".

Shifting of Oracle DBMS Job ScheduleTime

A daily job was scheduled to execute on top of the hour, but by the end of the week, was starting 6 seconds later. Leaving it for a few more weeks, it was by a minute.

Found out it was because of the interval value set as 'SYSDATE+1', making it go slower and slower as the job started later by a few seconds everyday. Adjusting the interval was able to fix it.


- Moving the hour part outside of day calculation:
                  


- Truncating up to the minute to lose seconds:
                 

Slow start for CISCO VPN Client

Pretty much detailed here http://superuser.com/questions/607090/cisco-vpn-client-hangs-at-opening-cert-store, and it was because of Fiddler certs.


The following setting works for me in Fiddler v2.6.2.0

Canceling Stuck Printer Jobs in Windows 7

If you are using a print server, then look under C:\Windows\System32\spool\SERVERS\ directory. If not, then follow this thread: https://social.technet.microsoft.com/Forums/windowsserver/en-US/b560cd2b-43b3-402c-a141-0c764963862d/system32-spool-printers-folder?forum=winserverprint

Not Being Able To Do Grid Edits For Tables in SQL Developer

Created a temp table in Oracle (create table t12 as select * from complex_view ...) to slice some data out of a complex and slow view.

 The newly created table allowed update statements to be run, but not in-place/grid edit in the TABLE/Data tab in SQL Developer. The cells did not allow data to be edited as if the new table is a view, so turned on SQL Developer logging as curiosity took over.

See for turning on SQL Developer logging: http://method-r.com/forum/technical/293-how-to-enable-logging-in-sql-developer

 Logging tab indicated the following SQL being issued when Data table is clicked. Executing this SQL revealed that this table is not even present in the view below.

      SQL> select column_name from  all_updatable_columns A where TABLE_NAME = :OBJECT_NAME and owner = :OBJECT_OWNER and A.updatable = 'YES'
 
Renaming the table did not help either, creating another temp table from this table 't12', allowed grid edits.

Wondering what went wrong with Oracle at the time table was created, and also any means to force update the view if it is to occur again ...

Switching From HTTP to HTTPS using Fiddler

Had to insert Fiddler between two servers to inspect SOAP over HTTPS, internal server acting as a client while the thrid party was the provider.

Since the client(server component) was not running on the Desktop, ebugging was not that easy as we did not have admin rights for the server.

In addition to that, the internal server(client) was not going to import Fiddler's certificate for some admin reasons, so the server(client) was configured to use SOAP over HTTP  while terminating on a Fiddler instance on a jump server.

Then custom rules were written in Fiddler to convert from HTTP(client) to HTTPS(target) as the target server only allowed  HTTPS.

 Fiddler CustomeRules.js:
 ...
static function OnBeforeRequest(oSession: Session) {
 ...
  if (!oSession.isHTTPS && !oSession.HTTPMethodIs("CONNECT") && oSession.HostnameIs("target-server.3rdparty.com")) {
   oSession.oRequest.headers.UriScheme = "https";
  }
 }

01722. 00000 - "invalid number"

Recently used SQLLDR to upload about 1.5M records from a number of files into a table supporting report queries using aggregate functions such as sum(...). Did not realize that the download file was in Windows format, so the carriage returns(\r) got into the column value as the control file was built for newline as follows.

           infile 'file1.csv' "str '\n'"

when running select sum(trim(amt)) ...., ran into
          ORA-01722: invalid number
          01722. 00000 -  "invalid number"
         *Cause:    The specified number was invalid.
         *Action:   Specify a valid number.

Ran many regexp_like(...) on the columns, and also egrep on file contents to look for patterns and did not catch any.
After some dabbling, finally found the rows having this issue out of 1.5M records.

Thanks to this post showing two ways to find \r or \n
http://stackoverflow.com/questions/12712480/sql-query-to-test-if-string-value-contains-carriage-return

Did a simple update as ... amt = replace(amt,CHR(13),'')...
        

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.