an alternative to memcached - ramdisk

ramdisk makes things even easier.

[sql ]get quarter end date of a given date

/*
Get the quarter end day of the given date if shift is zero
if shift is -1, it gets the previous quarter end day of the given date
if shift is 1, it gets the next quarter end day of the given date
shift can be +/- 1,2,3,...
*/
FUNCTION getQuarterEnd(ai_date IN DATE, shift INTEGER) RETURN DATE IS
BEGIN
return last_day(add_months(trunc(ai_date,'Q'), 2+shift*3));
END getQuarterEnd;

Win32 WMI

Get available memory:
get-wmiobject -query "select AvailableMBytes from Win32_PerfFormattedData_PerfOS_Memory"

NTSDB Performance Challenge

Essentially it's about finding the fastest way to identify a specific time point in an irregular time series.

SQLiteJDBC 5 row/s vs. CSV

Using SqliteJDBC v056, inserting around 3000 records into a SQlite table, no index, no trigger or any thing, just a plain table - and it took over 10 minutes to finish the job. Roughly 5 records per second.

Switched back to pure text file(CSV), 3000 records, it only took a couple of seconds.

Artificial stupidity in Oracle SQL Developer

Oracle SQL Developer automatically generates GROUP BY clause when it senses there's any aggregation functions in select clause. This might be helpful when writing a new SQL, but when it comes to debugging a huge complex PL/SQL, it is really annoying.

Fortunately there's an option to disable it, Tools > Preferences > Code Editor > Completion Insight > Autogenerate GROUP BY clause.

A template script for debuging PL/SQL procedures

When I get used to the convenience of Eclipse JDT, I can't stand for the awkwardness of Oracle PL/SQL and its IDE. Switching between today's technology and technology from 20 years ago is really a pain. So here is a script template to relief the pain a little bit.

SET serveroutput ON;
DECLARE
ai_instanceid NUMBER;
v_return curType;
rec recType;
BEGIN
ai_instanceid := 5000606;
v_return := plsql_pkg.getparameters(ai_instanceid);
LOOP
FETCH v_return INTO rec;
EXIT
WHEN v_return%notfound;
dbms_output.put_line(rec.portfolionm || ' ' || rec.templateid || ' ' || rec.statementenddt);
END LOOP;
CLOSE v_return;
END;

The mystery of two double quotes and join command(unix)

Two double quotes "" will be interpreted differently by join command.

So when comparing two CSV files, make sure avoiding lines like:

""field1"",""field2""

join will not process these fields.

This is not documented in the manual.

Hard coding

Hard coding (also, hard-coding or hardcoding) refers to the software development practice of embedding input or configuration data directly into the source code of a program or other executable object, or fixed formatting of the data, instead of obtaining that data from external sources or generating data or formatting in the program itself with the given input.

-Wikipedia http://en.wikipedia.org/wiki/Hard_coding

Hard coding has been widely known as a bad practice but I've seen in a lot of programs that constants are being misused to resolve "hard coding" issues. For example, in a java class that has SQL queries, people tend to break down the SQL into pieces and define java constants for the table names or even column names. Their reason is, if the table name or column name changed, it will be easy to change in java class - since there's only one constant for that table or column name. It seems making perfect sense. But think it twice, how often does a table or a column change, how often does the code need to be altered than viewed? Needless to say how hard it is to jumping over the variables when reading the code. Also when there's a change needs to be done, how do you navigate to the places? Isn't it a massive search and replace? So what's the difference between replacing it in one place and replacing it in multiple places? No difference, it's automatic search and replace!
Moreover, embedding SQL in java class isn't really hard coding if you think of the table name as a variable name.

Two ways to use a cursor in a SQL query

To use a cursor in a query, there are two possible ways I’ve used so far:

The first one is using the nested table(or varray). The idea is to read the cursor into the nested table and convert the nest table into a normal table in SQL.
Here is an example:

set serveroutput on;

-- These two types has to be global types, otherwise it will give “PLS-00642 local collection types not allowed in SQL statements”
-- Or maybe you can try using “record” instead of “object”, if it works, it will ease the cursor->nested table part(i.e. using “fetch bulk into” instead of the loop).
-- I’ve tried for a few hours and I gave up…
--create or replace type csrRow as object ( user_nm varchar2(20) );
--create or replace type csrTable as table of csrRow;

declare
cursor csr is select user_nm from table1; -- this is a sample cursor
tmpCsrRow table1.user_nm%type;
csrRow1 csrRow;
csrTable1 csrTable := csrTable(); -- initialize the nested table
counter number;
begin

-- Read the cursor into the nested table(csrTable1)
open csr;
loop
fetch csr into tmpCsrRow;
exit when csr%notfound;
csrTable1.extend;
csrTable1(csrTable1.last) := csrRow(tmpCsrRow);
end loop;
close csr;

--Use the nested table in a regular SQL statement
select count(*) into counter from table(cast(csrTable1 as csrTable));
dbms_output.put_line('Line count:' || counter);

end;



Another way is using pipelined table.
The idea is to create a wrapper for the ref cursor, the wrapper is a pipelined table. And in sql, use “select * from table(pipelinedTable)” to get the data.
Example:

create or replace package pipelinedTest as
type refCsrRec is record ( user_nm varchar2(20) );
type refCsrTable is table of refCsrRec;
function wrapper return refCsrTable pipelined;
end pipelinedTest;
/
create or replace package body pipelinedTest as
function wrapper return refCsrTable pipelined is --This wrapper function converts a cursor into a pipelined table
cursor csr is select user_nm from table1;
tmpRec refCsrRec;
begin
open csr;
loop
fetch csr into tmpRec;
exit when csr%notfound;
pipe row(tmpRec);
end loop;
close csr;
return;
end wrapper;
end pipelinedTest;
/
set serveroutput on;

declare
counter number;
begin
select count(*) into counter from table( pipelinedTest.wrapper() );
dbms_output.put_line('Line count:' || counter);
end;

I tried to write the wrapper() function as an inner function, but got some problems(I think it’s about SQL engine can’t see the dynamically defined PL/SQL function) so I just put it in a package like what we did for Cognos.


Conclusion
PL/SQL is short for a Pathetic Language/Seems Quite Lame

Software Development, $ by time makes simple things complex

A software development company shouldn't be like a garage - doing repeating jobs and charge clients on how much time developers have spent on it.

This makes a simple job complex, this impairs efficiency, discourages innovation... I don't think it is necessary to give any examples to proof this. You know what it is like if you've worked in a traditional software company.

Then what should software development look like?
I don't know yet.

Java is a good tool to do prototyping

when it comes to production, it is not fast enough.

Cognos 8 doesn't support ORACLE REF CURSOR

So to get around with this, use wrapper function and PIPELINED table.

JUnit is taken out of NOWING projects

JUnit is a very convenient library. Like an automatic transmission for a family car - You press the paddle, it runs.

But NOWING projects will go back to the previous unit testing method - each class that needs to be tested will have a static main method and the test will be done there.

CAUTION: Oracle treats empty string as null

select 'oracle empty string is equal to null' from dual where '' is null;

Apache htaccess password

1.AccessFileName .htaccess in httpd.conf (if windows)

2.httpd.conf or vhost.conf
<directory "path/to/webfolder>
Options None
AllowOverride all
Order deny,allow
</directory>
3.htpasswd -c -b password.txt name password

4.Create .htaccess under webfolder
AuthUserFile /path/to/passwd.txt
AuthType Basic
AuthName "some text"

<limit GET POST>
require valid-user
</limit>

NOWING Automated Currency Trading System Structure

Hey! Smile!

• Book Center
  stores historical quotes, transaction records and periodical analysis results.

• Broker Gateway
  communicates with FX brokers' systems, sends/receives transactions.

• Automated Traders
  hold different trading strategies and send out trading signals.

• Communication Interface
  controls and monitors the system.

• Web Server
  provides a fancy interface, shows charts, reports to end users.

• End User
  unhappy investors.

SQL to check Oracle version

select * from v$version;

or

select * from product_component_version;

Use market activity instead of trading hours to decide when to run the automated FX scripts

Some automated trading programs only trade during certain time periods. That's because at certain time period, the market usually has a relatively stable pattern, either volatile or nonvolatile.

Comparing with trading hour, market activity is a more essential factor for measuring a stable pattern.

market activity can be measured as(I can think of right now):
1. tick counts per minute
2. price net absolute change per minute
3. price gross absolute change per minute