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

Ojdbc11 - 23.3.0.23.09, Oracle JDBC Driver compatible with JDK11, JDK12, JDK13, JDK14 and JDK15

https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc11/23.3.0.23.09/ojdbc11-23.3.0.23.09.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

http://www.informit.com/articles/article.aspx?p=26251&seqNum=6

   1 CREATE OR REPLACE PROCEDURE p_highest_paid_emp
   2        (ip_deptno NUMBER,
   3        op_empno OUT NUMBER,
   4        op_ename OUT VARCHAR2,
   5        op_sal OUT NUMBER)
   6 IS
   7  v_empno NUMBER;
   8  v_ename VARCHAR2(20);
   9  v_sal NUMBER;
  10 BEGIN
  11  SELECT empno, ename, sal
  12  INTO v_empno, v_ename, v_sal
  13  FROM emp e1
  14  WHERE sal = (SELECT MAX(e2.sal)
  15      FROM emp e2
  16      WHERE e2.deptno = e1.deptno
  17      AND e2.deptno = ip_deptno)
  18   AND deptno = ip_deptno;
  19  op_empno := v_empno;
  20  op_ename := v_ename;
  21  op_sal := v_sal;
  22 END;
  23 /

   1 import java.sql.*;
   2 
   3 public class StProcExample {
   4  public static void main(String[] args)
   5  throws SQLException {
   6 int ret_code;
   7 Connection conn = null;
   8  try {
   9   //Load and register Oracle driver
  10   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  11  //Establish a connection
  12 
  13  conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
  14  Oracle", "oratest", "oratest");
  15  int i_deptno = 10;
  16  CallableStatement pstmt = conn.prepareCall("{call p_highest_
  17  paid_emp(?,?,?,?)}");
  18  pstmt.setInt(1, i_deptno);
  19  pstmt.registerOutParameter(2, Types.INTEGER);
  20  pstmt.registerOutParameter(3, Types.VARCHAR);
  21  pstmt.registerOutParameter(4, Types.FLOAT);
  22  pstmt.executeUpdate();
  23 
  24  int o_empno = pstmt.getInt(2);
  25  String o_ename = pstmt.getString(3);
  26  float o_sal = pstmt.getFloat(4);
  27  System.out.print("The highest paid employee in dept "
  28  +i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
  29  pstmt.close();
  30  conn.close();
  31   } catch (SQLException e) {ret_code = e.getErrorCode();
  32    System.err.println(ret_code + e.getMessage()); conn.close();}
  33  }
  34 }

http://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

   1 CREATE OR REPLACE
   2 PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
   3                       p_recordset OUT SYS_REFCURSOR) AS 
   4 BEGIN 
   5   OPEN p_recordset FOR
   6     SELECT ename,
   7            empno,
   8            deptno
   9     FROM   emp
  10     WHERE  deptno = p_deptno
  11     ORDER BY ename;
  12 END GetEmpRS;
  13 /

   1 import java.sql.*;
   2 import oracle.jdbc.*;
   3 
   4 public class TestResultSet  {
   5   public TestResultSet() {
   6     try {
   7       DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
   8       Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
   9       CallableStatement stmt = conn.prepareCall("BEGIN get_emp_rs(?, ?); END;");
  10       stmt.setInt(1, 30); // DEPTNO
  11       stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
  12       stmt.execute();
  13       ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
  14       while (rs.next()) {
  15         System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 
  16       }
  17       rs.close();
  18       rs = null;
  19       stmt.close();
  20       stmt = null;
  21       conn.close();
  22       conn = null;
  23     }
  24     catch (SQLException e) {
  25       System.out.println(e.getLocalizedMessage());
  26     }
  27   }
  28 
  29   public static void main (String[] args) {
  30     new TestResultSet();
  31   }
  32 }

Exceptions

   1 --
   2 BEGIN
   3   EXCEPTION WHEN others THEN
   4   dbms_output.put_line('Ups ....!');
   5 END;

Convert to date

TO_DATE('2013/07/19', 'yyyy/mm/dd')
Result: date value of July 19, 2013

TO_DATE('071913', 'MMDDYY')
Result: date value of July 19, 2013

TO_DATE('20120311', 'yyyymmdd')
Result: date value of Mar 11, 2012

Number updated rows

SQL%ROWCOUNT
numberRows := SQL%ROWCOUNT;

Return first non null expression

use NVL function , Oracle specific
use COALESCE function , ANSI 92

   1 UPDATE xyz set fieldx = COALESCE(fieldx_param, fieldx) where idx=idx_p;

Get unique identifier GUID

https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions153.htm

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

   1 select sys_guid() from dual;

Data types

number
nvarchar2
int
real

Limit number of rows (rownum)

   1 select * from tablex 
   2 where rownum <=10;

Docker container Oracle DB XE 21c

Should have oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm in the same folder of the Dockerfile and steps.sh file.

Dockerfile

   1 FROM oraclelinux:8.7
   2 ENV ORACLE_DOCKER_INSTALL=true
   3 CMD ["/bin/bash"]

steps.sh

   1 #!/bin/sh
   2 yum update -y
   3 yum install -y oracle-database-preinstall-21c
   4 yum -y localinstall /mnt/oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm
   5 echo -e "TestOracle01\nTestOracle01\n" | /etc/init.d/oracle-xe-21c configure
   6 echo "PATH=/opt/oracle/product/21c/dbhomeXE/bin/:$PATH" >> ~/.bashrc
   7 echo "export ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE/" >> ~/.bashrc

Steps

   1 docker build -t oracledb_test .
   2 docker run --rm -dit --name oraclelinux -p 1521:1521 -v $PWD:/mnt/ -e ORACLE_DOCKER_INSTALL=true oracledb_test 
   3 docker exec -it oraclelinux bash
   4 cd /mnt/
   5 time sh steps.sh # real 24m43.345s
   6 source ~/.bashrc
   7 sqlplus system/TestOracle01@localhost/xe
   8 SELECT table_name  FROM dba_tables;
   9 exit