geek.soosoo.at

Jul 19

jzy3d – java library for scientific 3d plotting

Java No Comments »

Jzy3d stands for Java Easy 3d, and allows a rapid display of 3d scientific data. Users can define z=f(x,y) functions binded to (customizable) colormaps, as well as rendering predefined simple 3d shapes programmaticaly or from csv files. Several strategies can be applied to generate surface, such as a Delaunay tesselation that let you build a surface from unknown structured datasets.

The API has been written with the goal to hide the burden of working with OpenGL, 3d polygon ordering and transparency management. Jzy3d replies to the need of a simple tool like plot3d in Matlab and let the user concentrate on its data rather than UI programming.

It’s open-source (BSD license), fast and easy to use – fabulous library!

http://code.google.com/p/jzy3d/

Jun 30

Session Was Killed During Index Rebuild ORA-08104

Oracle No Comments »

Symptoms:
While running an online index rebuild your session was killed or otherwise terminated abnormally. You are now attempting to run the index rebuild again and is throwing the error: ORA-08104: this index object ##### is being online built or rebuilt

Cause:
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.

Solution:
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.

Reference:
Metalink 375856.1

Jun 10

Recreate Oracle database control without downtime

Oracle No Comments »

Do the following steps to recreate the database control which don’t place the database in quiesce state:

Make sure that the database and the listener is up and running.

1. Logon SQLPLUS as user SYS and drop the sysman account and mangement objects:

a. drop user sysman cascade;
b. drop role MGMT_USER;
c. drop user MGMT_VIEW cascade;
d. drop public synonym MGMT_TARGET_BLACKOUTS;
e. drop public synonym SETEMVIEWUSERCONTEXT;

2. Manually remove the following folders:

ORACLE_HOME/host_sid/
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid

3.Set the ORACLE_HOME and ORACLE_SID

From $ORACLE_HOME/bin issue the following command:

emca -config dbcontrol db -repos create

Jan 29

Query Finance@Yahoo with SQL

PL/SQL No Comments »

Here’s the way to go if you need to query financial data at Yahoo!Finance via Oracle SQL.

Pre-Requirements:

* Database version must be >= 10g
* HTTP access to *.yahoo.com. (You’ll need an appropriate ACL when you are on 11g!)
* Access to UTL_HTTP, DBMS_LOB, SYS.ANYTYPE and SYS.ANYDATASET
* STR Package (because of the Split functions -> can be found here)

Implementation:

The solution is implemented with the Oracle pipelined table function feature, so it’s possible to use it in a SQL statement with the TABLE keyword:

select * from table(finyho([REQUEST]))

Furthmore the returned resultset of FINYHO is dynamic (ANYDATASET) and only contains the requested data fields. This is accomplished with the interface implementation approach of the pipelined table function.

There are two possible request types:
* a single row result for each security. Returns the latest data (= GET Request)
* a historical data request which returns a predefined set of data fields for a specific time frame.
(= HISTORY Request)

Syntax of the GET Request:

Get|[SECURITIES]|[FIELDS]

SECURITIES is a + seperated list of security tickers (eg: MSFT or GOOG+APPL etc.)
FIELDS is a list of special tags which represents the data fields you would like to query

Here are the possible values for the FIELDS parameter:

a	Ask	a2	Average Daily Volume	a5	Ask Size
b	Bid	b2	Ask (Real-time)	b3	Bid (Real-time)
b4	Book Value	b6	Bid Size	c	Change & Percent Change
c1	Change	c3	Commission	c6	Change (Real-time)
c8	After Hours Change (Real-time)	d	Dividend/Share	d1	Last Trade Date
d2	Trade Date	e	Earnings/Share	e1	Error Indication
e7	EPS Estimate Current Year	e8	EPS Estimate Next Year	e9	EPS Estimate Next Quarter
f6	Float Shares	g	Day’s Low	h	Day’s High
j	52-week Low	k	52-week High	g1	Holdings Gain Percent
g3	Annualized Gain	g4	Holdings Gain	g5	Holdings Gain Percent (Real-time)
g6	Holdings Gain (Real-time)	i	More Info	i5	Order Book (Real-time)
j1	Market Capitalization	j3	Market Cap (Real-time)	j4	EBITDA
j5	Change From 52-week Low	j6	Percent Change From 52-week Low	k1	Last Trade (Real-time) With Time
k2	Change Percent (Real-time)	k3	Last Trade Size	k4	Change From 52-week High
k5	Percebt Change From 52-week High	l	Last Trade (With Time)	l1	Last Trade (Price Only)
l2	High Limit	l3	Low Limit	m	Day’s Range
m2	Day’s Range (Real-time)	m3	50-day Moving Average	m4	200-day Moving Average
m5	Change From 200-day Moving Average	m6	PCT Change From 200-day Moving Average	m7	Change From 50-day Moving Average
m8	PCT Change From 50-day Moving Average	n	Name	n4	Notes
o	Open	p	Previous Close	p1	Price Paid
p2	Change in Percent	p5	Price/Sales	p6	Price/Book
q	Ex-Dividend Date	r	P/E Ratio	r1	Dividend Pay Date
r2	P/E Ratio (Real-time)	r5	PEG Ratio	r6	Price/EPS Estimate Current Year
r7	Price/EPS Estimate Next Year	s	Symbol	s1	Shares Owned
s7	Short Ratio	t1	Last Trade Time	t6	Trade Links
t7	Ticker Trend	t8	1 yr Target Price	v	Volume
v1	Holdings Value	v7	Holdings Value (Real-time)	w	52-week Range
w1	Day’s Value Change	w4	Day’s Value Change (Real-time)	x	Stock Exchange
y	Dividend Yield

Examples:

Get the Ask, Bid and Last Trade of Microsoft:
select * from table(finyho('Get|MSFT|abl1'))

Get the Symbol, Name, Last Trade Date, Last Trade (Price only), Dividend Yield and P/E Ratio of Google and GE:
select * from table(finyho('Get|GOOG+GE|snd1l1yr'))

Syntax of the HISTORY Request:

History|[SECURITY]|[STARTDATE]|[ENDDATE]

SECURITY is the security ticker (only one allowed!)
STARTDATE is the beginning of the time frame (Syntax: YYYYMMDD)
ENDDATE is the end of the time frame (Syntax: YYYYMMDD)

The History Request returns the following data fields:

DATE, OPEN, HIGH, LOW, CLOSE, VOLUME and ADJ CLOSE

Examples:

Historical Data of Microsoft from 01/02/2009 till 01/20/2010:
select * from table(finyho('History|MSFT|20090102|20100120'))

Historical Data of Google from 01/02/2009 till 01/20/2010:
select * from table(finyho('History|GOOG|20090102|20100120'))

Code:

The YAHOO package contains two functions. The ParseRequest function parses the Request parameter and creates the Yahoo URL. The Query function executes the HTTP request and stores the CSV response in a CLOB. The YHO_IMPL object is the interface implementation of the pipelined table function FINYHO and creates the ANYDATASET from the CLOB data.

References:

Using Pipelined and Parallel Table Functions
Yahoo Data Download

SQL Script:

Download

Attention:

This code-snippet is for educational purpose only! I don’t know anything about possible Yahoo usage restrictions or licenses, copyrights etc. Use at your own risk!

Enjoy!

Jan 26

Exception Line Number

PL/SQL No Comments »

Sometimes it’s quite useful to know the line number of an exception:

BEGIN

RAISE NO_DATA_FOUND;

EXCEPTION WHEN NO_DATA_FOUND THEN
/*
Parse a line with this format:
ORA-NNNNN: at "SCHEMA.PROGRAM", line NNN
and return the line number of the exception
*/

DBMS_OUTPUT.put_line(
TRIM(trailing CHR(10) FROM
SUBSTR(DBMS_UTILITY.format_error_backtrace,
INSTR(DBMS_UTILITY.format_error_backtrace,’ ‘,-1,1)+1
)
)
);

END;

Download SQL Test Script

Jan 25

Parse a delimiter-seperated string / Split Function

PL/SQL No Comments »

This is a simple function which parses a delimiter-seperated string (varchar2 or clob) and returns a string array.

1. Create the string array type

CREATE OR REPLACE TYPE str_array IS TABLE OF VARCHAR2(4000)

2. Create a package with two functions

CREATE OR REPLACE PACKAGE Str IS

FUNCTION Split(sText IN VARCHAR2, sDel IN VARCHAR2 := ‘,’) RETURN str_array;
FUNCTION Split(sText IN clob, sDel IN VARCHAR2 := ‘,’) RETURN str_array;

END Str;

CREATE OR REPLACE PACKAGE BODY Str IS

FUNCTION Split(sText IN VARCHAR2, sDel IN VARCHAR2 := ‘,’) RETURN str_array IS
nStartIdx PLS_INTEGER := 1;
nEndIdx PLS_INTEGER := 1;
oRet str_array := str_array();
BEGIN

IF sText IS NULL THEN RETURN oRet; END IF;

LOOP

nEndIdx := INSTR(sText, sDel, nStartIdx);

IF nEndIdx > 0 THEN

oRet.Extend;
oRet(oRet.LAST) := SUBSTR(sText, nStartIdx, nEndIdx – nStartIdx);
nStartIdx := nEndIdx + 1;

ELSE

oRet.Extend;
oRet(oRet.LAST) := SUBSTR(sText, nStartIdx);
EXIT;

END IF;

END LOOP;

RETURN oRet;

END Split;

FUNCTION Split(sText IN clob, sDel IN VARCHAR2 := ‘,’) RETURN str_array IS
nStartIdx PLS_INTEGER := 1;
nEndIdx PLS_INTEGER := 1;
oRet str_array := str_array();
BEGIN

IF sText IS NULL THEN RETURN oRet; END IF;
IF DBMS_LOB.getlength(sText) = 0 THEN RETURN oRet; END IF;

LOOP

nEndIdx := DBMS_LOB.INSTR(sText, sDel, nStartIdx);

IF nEndIdx > 0 THEN

oRet.Extend;
oRet(oRet.LAST) := DBMS_LOB.SUBSTR(sText, nEndIdx – nStartIdx, nStartIdx);
nStartIdx := nEndIdx + LENGTH(sDel);

ELSE

oRet.Extend;
oRet(oRet.LAST) := DBMS_LOB.SUBSTR(lob_loc => sText, offset => nStartIdx);
EXIT;

END IF;

END LOOP;

RETURN oRet;

END Split;

END Str;

3. Test it

DECLARE
oStr str_array;
BEGIN
oStr := Str.Split(‘one,two,three,four,five’);
IF oStr.COUNT > 0 THEN
FOR i IN oStr.FIRST .. oStr.LAST
LOOP
DBMS_OUTPUT.put_line(oStr(i));
END LOOP;
END IF;
END;

Download SQL Script

Dec 05