Skip to content

Instantly share code, notes, and snippets.

View gregrahn's full-sized avatar

Greg Rahn gregrahn

166 followers · 14 following
View GitHub Profile
@gregrahn
gregrahn / query2csv.R
Created April 14, 2012 06:28
example of using R to save query results to csv file
library(RJDBC)
drv <-JDBC("oracle.jdbc.driver.OracleDriver","/Users/grahn/code/jdbc/ojdbc6.jar")
conn<-dbConnect(drv,"jdbc:oracle:thin:@grahn-dev.us.oracle.com:1521:orcl","scott","tiger")
data <-dbGetQuery(conn, "select * from emp")
write.table(data, file="emp.csv", sep = ",", row.names=FALSE)
@gregrahn
gregrahn / scott_emp2_cursors.txt
Created February 27, 2012 16:00
--
-- open EMP2 tab & browse DATA tab, notice there are 2 open cursors, noting the SQL_EXEC_ID numbers
--
PROGRAM SQL_ID SQL_EXEC_ID CURSOR_TYPE SQL_TEXT
------------------------------------ ------------- ----------- --------------- ------------------------------------------------------------
SQL Developer apdcq2s3sxn9y 16777216 OPEN SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
[email protected] (P000) apdcq2s3sxn9y 16777216 OPEN-RECURSIVE SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
[email protected] (P001) apdcq2s3sxn9y 16777216 OPEN-RECURSIVE SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
SQL Developer apdcq2s3sxn9y 16777217 OPEN SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", EMPNO EMPNO,
@gregrahn
gregrahn / scott_emp2_cursors.sql
Created February 27, 2012 15:58
col CURSOR_TYPE for a15
col PROGRAM for a36
select
s.program,
o.sql_id,
o.sql_exec_id,
o.cursor_type,
o.sql_text
from
@gregrahn
gregrahn / emp2.sql
Created February 27, 2012 15:35
create table emp2 as select * from emp;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
insert into emp2 select * from emp2;
commit;
alter table emp2 parallel 2;
@gregrahn
gregrahn / rowgen.sql
Created February 10, 2012 22:55
row generator using recursive subquery factoring (recursive with / recursive CTE) in Oracle. More: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#BABCDJDB
WITH RecursiveCTE (rnum) AS
(
SELECT 1 AS rnum
FROM dual
UNION ALL
SELECT rnum + 1
FROM RecursiveCTE
WHERE rnum < 24
)
SELECT rnum
@gregrahn
gregrahn / ASH_plots.R
Created December 20, 2011 18:15
Example on how to read ASH data from Oracle into R and do scatter plots
library(RJDBC)
#
# set up the JDBC connection
# configure this for your env
#
drv <-JDBC("oracle.jdbc.driver.OracleDriver","/Users/grahn/code/jdbc/ojdbc6.jar")
conn<-dbConnect(drv,"jdbc:oracle:thin:@zulu.us.oracle.com:1521:orcl","grahn","grahn")
#
# import the data into a data.frame
@gregrahn
gregrahn / triangle_counting.sql
Created October 15, 2011 07:01
--
-- triangle_counting.sql
--
-- get flat file from http://www.vertica.com/benchmark/TriangleCounting/edges.txt.gz
-- gunzip & place in /tmp
--
create or replace directory GRAPH_DATA as '/tmp';
DROP TABLE ET_EDGES;