Select Star From Where?

Monday, October 19, 2009

guide to installing cx_Oracle on Mac OSX

Ran into some trouble after I installed python 2.6, and found this helpful blog entry. Kept getting "Symbol not found: ___divdi3" error. Unfortunately, the advice didn't work for me, but it does seem to have worked for a number of people. The only thing that worked for me was ditching the 2.6 install and reverting back to OSX's default 2.5.1 version.

Build and install cx_Oracle on MacOS X Leopard Intel

Friday, October 16, 2009

python script to list oracle tables and indexes


import cx_Oracle
orcl = cx_Oracle.connect('username/password@db')
curs = orcl.cursor()

schema='my_schema'

curs.execute("""
select table_name from all_tables where owner=:own
order by 1
""",own=schema)

table_names=curs.fetchall()

tables=[]

class TableInfo:
def __init__ (self,tname):
self.name=tname
indexes={}
partitions={}


for tn in table_names:
t=TableInfo(tn[0])
indexlist={}
curs.execute("""
select index_name from all_indexes where table_name=:tbl
""",tbl=t.name)
index_names=curs.fetchall()

colnames=[]

for xn in index_names:
curs.execute("""
select column_name from all_ind_columns where index_name=:ind
""",ind=xn[0])

col_names=curs.fetchall()

for cn in col_names:
colnames.append(cn[0])

indexlist[xn[0]]=cn[0]

t.indexes=indexlist
tables.append(t)

for t in tables:
print t.name,t.indexes

curs.close()
orcl.close()

Wednesday, September 30, 2009

some useful (and relatively uncommon) R tips

http://www.win-vector.com/blog/2009/09/survive-r/

Wednesday, September 16, 2009

pl/sql function to round down to nearest minute interval

create or replace function nearest_min(dt IN DATE, interval_minutes NUMBER)
return DATE
is
newdate DATE;
BEGIN
newdate := trunc(dt,'HH')+(trunc((dt-trunc(dt,'HH'))*24/(interval_minutes/60)))/(60/interval_minutes)/24;
return newdate;
END;
/


Thanks to http://www.freelists.org/post/oracle-l/Fun-with-SYSDATE-TRUNC-and-rounding,1 for the logic behind the calcuation.

Tuesday, August 25, 2009

Using Imagemagick to create an animated gif from .png files

With R, I created a series of charts, each representing a sample snapshot at a given time.  I wanted to animate them, and found a wonderful command-line set of utlities called ImageMagick.  I had to jump through quite a few hoops to install ImageMagick on Mac OS X (will post details if/when I get the time), but I'm glad I did.

Here's a sample session where I combine the files into an animate gif:

~/r/tmat>ls
tmat02.png  tmat04.png tmat06.png  tmat08.png tmat10.png  tmat12.png tmat14.png  tmat16.png tmat18.png  tmat20.png
tmat03.png  tmat05.png tmat07.png  tmat09.png tmat11.png  tmat13.png tmat15.png  tmat17.png tmat19.png
~/r/tmat>convert -delay 20 -loop 0 * page_transition.gif

Thursday, July 23, 2009

pig on hadoop: count all rows


pt = LOAD 'data'
B = GROUP pt ALL;
C = FOREACH B GENERATE COUNT(pt);
DUMP C;

actionscript 3: dynamically add attribute to xml element

pf is an XML object, cols is an array of attribute values, and headers is an array of attribute names.

The @[variable name] syntax works the magic.


for(var cn:int=0;cn<cols.length;cn++){
var col_name:String = headers[cn];
pf.ROW[rn-1].@[col_name]=cols[cn];
}