Oracle

http://docs.oracle.com/cd/B19306_01/index.htm

JDBC driver for JDK 1.6 (ojdbc6.jar): http://download.oracle.com/otn/utilities_drivers/jdbc/11204/ojdbc6.jar

Maven local repository install

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=true

   1 <dependency>
   2     <groupId>com.oracle</groupId>
   3     <artifactId>ojdbc6</artifactId>
   4     <version>11.2.0.3</version>
   5 </dependency>

Get schemas in DB

   1 SELECT DISTINCT USERNAME FROM DBA_USERS;

Get columns from table

   1 SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME='XYZ';
   2 SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='AAA';

Get indexes from table

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm

   1 SELECT index_name, table_owner, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'XYZ';
   2 SELECT index_name, table_owner, table_name, uniqueness FROM ALL_INDEXES WHERE table_name = 'XYZ';

Get code from stored procedure

   1 SELECT * FROM all_source WHERE name = 'myprocedure'
   2 ORDER BY owner,name,line

Show schema and table

   1 SELECT distinct owner,table_name 
   2 FROM all_tables
   3 ORDER BY owner,table_name;

Partinioning

http://www.orafaq.com/wiki/Interval_partitioning

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin005.htm

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm

   1 select table_name,partition_name,num_rows
   2 from user_tab_partitions
   3 where table_name='TEST';

Other views:

DBA_TAB_PARTITIONS
ALL_TAB_PARTITIONS
USER_TAB_PARTITIONS

Sequence creation

   1 CREATE SEQUENCE sample_seq  START WITH 1 INCREMENT BY 1;
   2 CREATE SEQUENCE sample_seq2 MINVALUE 1 START WITH 1 INCREMENT BY 1;
   3 SELECT * FROM ALL_SEQUENCES; -- show all sequences
   4

Sequence usage sample

   1 -- table creation
   2 CREATE TABLE Employee 
   3 ( idEmployee number(12), name varchar2(255), lastName varchar2(255),
   4 constraint pkIdEmployee PRIMARY KEY(idEmployee)
   5 );
   6 -- sequence creation
   7 CREATE SEQUENCE seqEmplyee 
   8 start with 1 increment by 1 minvalue 1 maxvalue 65536;
   9 -- insert values
  10 INSERT INTO Employee (idEmployee,name,lastName) 
  11 values(seqEmplyee.nextval,'Bart','Simpson'); 
  12 INSERT INTO Employee (idEmployee,name,lastName) 
  13 values(seqEmplyee.nextval,'Peter','Griffin'); 
  14 INSERT INTO Employee (idEmployee,name,lastName) 
  15 values(seqEmplyee.nextval,'Homer','Simpson'); 
  16 -- check sequence current value
  17 SELECT seqEmplyee.currval FROM dual;

User and table space creation in Oracle XE

   1 -- login with system user
   2 create tablespace christs datafile 'c:\christs.dbf' size 100m;
   3 create user chris identified by 12345678 default tablespace christs;
   4 alter user chris quota unlimited on christs;
   5 grant create session to chris;
   6 grant create table to chris;
   7 
   8 -- login with user chris
   9 create table CHRIS.test ( name varchar(255) ) tablespace chris;
  10 insert into CHRIS.test (name) values('Test name 1234');
  11 
  12 -- login with system user
  13 -- user alice to just run selects
  14 create user alice identified by 12345678 default tablespace christs;
  15 grant create session to alice;
  16 grant select on CHRIS.test to alice;
  17 
  18 -- login with alice
  19 select * from CHRIS.test;

Oracle Database 11g Express Edition

Download from Oracle site

Run setup.exe
Next
Accept
Next
c:\oraclexe
Port TNS 1521
Port MTS 2030
Port Http 8081
Next
For user sys and system password ********
Next
Install
Finish

Info

Default SID xe .
Default JDBC driver JAR ojdbc6.jar .
JDBC Url jdbc:oracle:thin:@//localhost:1521/XE

Returning a JDBC result set from an Oracle stored procedure

http://enterprisedt.com/publications/oracle/result_set.html

Create function:

   1 CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER) RETURN sys _refcursor
   2 AS stock_cursor sys_refcursor;
   3 BEGIN
   4     OPEN stock_cursor FOR 
   5         SELECT ric,price,updated FROM stock_prices
   6         WHERE price < v_price;
   7     RETURN stock_cursor;
   8 END;

Test function:

   1 var results refcursor
   2 exec :results := sp_get_stocks(20.0)
   3 print results

JDBC invocation

   1 //String query = "begin ? := sp_get_stocks(?); end;";
   2 //JDBC syntax
   3 String query = "{ call ? := sp_get_stocks(?) }";
   4 
   5 CallableStatement stmt = conn.prepareCall(query);
   6 
   7 // register the type of the out param - an Oracle specific type
   8 stmt.registerOutParameter(1, OracleTypes.CURSOR);
   9 
  10 // set the in param
  11 stmt.setFloat(2, price);
  12 
  13 // execute and retrieve the result set
  14 stmt.execute();
  15 ResultSet rs = (ResultSet)stmt.getObject(1);
  16 
  17 // print the results
  18 while (rs.next()) {
  19     System.out.println(rs.getString(1) + "\t" +
  20         rs.getFloat(2) + "\t" +
  21         rs.getDate(3).toString());
  22 }

http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleCallableStatement.html

   1 // SQL92 syntax
   2   CallableStatement cs1 = conn.prepareCall
   3   ( "{call proc (?,?)}" ) ; // stored proc
   4   CallableStatement cs2 = conn.prepareCall
   5   ( "{? = call func (?,?)}" ) ; // stored func
   6   // Oracle PL/SQL block syntax
   7   CallableStatement cs3 = conn.prepareCall
   8   ( "begin proc (?,?); end;" ) ; // stored proc
   9   CallableStatement cs4 = conn.prepareCall
  10   ( "begin ? := func(?,?); end;" ) ; // stored func
  11