= 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

{{{#!highlight xml
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>
}}}

== Get schemas in DB ==
{{{#!highlight sql
SELECT DISTINCT USERNAME FROM DBA_USERS;
}}}

== Get columns from table ==
{{{#!highlight sql
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME='XYZ';
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

{{{#!highlight sql
SELECT index_name, table_owner, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'XYZ';
SELECT index_name, table_owner, table_name, uniqueness FROM ALL_INDEXES WHERE table_name = 'XYZ';
}}}

== Get code from stored procedure ==
{{{#!highlight sql
SELECT * FROM all_source WHERE name = 'myprocedure'
ORDER BY owner,name,line
}}}

== Show schema and table ==
{{{#!highlight sql
SELECT distinct owner,table_name 
FROM all_tables
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

{{{#!highlight sql
select table_name,partition_name,num_rows
from user_tab_partitions
where table_name='TEST';
}}}

Other views:
 * DBA_TAB_PARTITIONS
 * ALL_TAB_PARTITIONS
 * USER_TAB_PARTITIONS

== Sequence creation ==
{{{#!highlight sql
CREATE SEQUENCE sample_seq  START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE sample_seq2 MINVALUE 1 START WITH 1 INCREMENT BY 1;
SELECT * FROM ALL_SEQUENCES; -- show all sequences
}}}

== Sequence usage sample ==
{{{#!highlight sql
-- table creation
CREATE TABLE Employee 
( idEmployee number(12), name varchar2(255), lastName varchar2(255),
constraint pkIdEmployee PRIMARY KEY(idEmployee)
);
-- sequence creation
CREATE SEQUENCE seqEmplyee 
start with 1 increment by 1 minvalue 1 maxvalue 65536;
-- insert values
INSERT INTO Employee (idEmployee,name,lastName) 
values(seqEmplyee.nextval,'Bart','Simpson'); 
INSERT INTO Employee (idEmployee,name,lastName) 
values(seqEmplyee.nextval,'Peter','Griffin'); 
INSERT INTO Employee (idEmployee,name,lastName) 
values(seqEmplyee.nextval,'Homer','Simpson'); 
-- check sequence current value
SELECT seqEmplyee.currval FROM dual;
}}}

== User and table space creation in Oracle XE ==
{{{#!highlight sql
-- login with system user
create tablespace christs datafile 'c:\christs.dbf' size 100m;
create user chris identified by 12345678 default tablespace christs;
alter user chris quota unlimited on christs;
grant create session to chris;
grant create table to chris;

-- login with user chris
create table CHRIS.test ( name varchar(255) ) tablespace chris;
insert into CHRIS.test (name) values('Test name 1234');

-- login with system user
-- user alice to just run selects
create user alice identified by 12345678 default tablespace christs;
grant create session to alice;
grant select on CHRIS.test to alice;

-- login with alice
select * from CHRIS.test;

}}}

== Oracle Database 11g Express Edition ==
Download from [[ http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html | 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:
{{{#!highlight sql
CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER) RETURN sys _refcursor
AS stock_cursor sys_refcursor;
BEGIN
    OPEN stock_cursor FOR 
        SELECT ric,price,updated FROM stock_prices
        WHERE price < v_price;
    RETURN stock_cursor;
END;
}}}

Test function:
{{{#!highlight sql
var results refcursor
exec :results := sp_get_stocks(20.0)
print results
}}}

JDBC invocation
{{{#!highlight java
//String query = "begin ? := sp_get_stocks(?); end;";
//JDBC syntax
String query = "{ call ? := sp_get_stocks(?) }";

CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setFloat(2, price);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) {
    System.out.println(rs.getString(1) + "\t" +
        rs.getFloat(2) + "\t" +
        rs.getDate(3).toString());
}
}}}


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

{{{#!highlight java
// SQL92 syntax
  CallableStatement cs1 = conn.prepareCall
  ( "{call proc (?,?)}" ) ; // stored proc
  CallableStatement cs2 = conn.prepareCall
  ( "{? = call func (?,?)}" ) ; // stored func
  // Oracle PL/SQL block syntax
  CallableStatement cs3 = conn.prepareCall
  ( "begin proc (?,?); end;" ) ; // stored proc
  CallableStatement cs4 = conn.prepareCall
  ( "begin ? := func(?,?); end;" ) ; // stored func
}}}

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

{{{#!highlight sql
CREATE OR REPLACE PROCEDURE p_highest_paid_emp
       (ip_deptno NUMBER,
       op_empno OUT NUMBER,
       op_ename OUT VARCHAR2,
       op_sal OUT NUMBER)
IS
 v_empno NUMBER;
 v_ename VARCHAR2(20);
 v_sal NUMBER;
BEGIN
 SELECT empno, ename, sal
 INTO v_empno, v_ename, v_sal
 FROM emp e1
 WHERE sal = (SELECT MAX(e2.sal)
     FROM emp e2
     WHERE e2.deptno = e1.deptno
     AND e2.deptno = ip_deptno)
  AND deptno = ip_deptno;
 op_empno := v_empno;
 op_ename := v_ename;
 op_sal := v_sal;
END;
/
}}}

{{{#!highlight java
import java.sql.*;

public class StProcExample {
 public static void main(String[] args)
 throws SQLException {
int ret_code;
Connection conn = null;
 try {
  //Load and register Oracle driver
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 //Establish a connection

 conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
 Oracle", "oratest", "oratest");
 int i_deptno = 10;
 CallableStatement pstmt = conn.prepareCall("{call p_highest_
 paid_emp(?,?,?,?)}");
 pstmt.setInt(1, i_deptno);
 pstmt.registerOutParameter(2, Types.INTEGER);
 pstmt.registerOutParameter(3, Types.VARCHAR);
 pstmt.registerOutParameter(4, Types.FLOAT);
 pstmt.executeUpdate();

 int o_empno = pstmt.getInt(2);
 String o_ename = pstmt.getString(3);
 float o_sal = pstmt.getFloat(4);
 System.out.print("The highest paid employee in dept "
 +i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
 pstmt.close();
 conn.close();
  } catch (SQLException e) {ret_code = e.getErrorCode();
   System.err.println(ret_code + e.getMessage()); conn.close();}
 }
}
}}}


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

{{{#!highlight sql
CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/
}}}

{{{#!highlight java
import java.sql.*;
import oracle.jdbc.*;

public class TestResultSet  {
  public TestResultSet() {
    try {
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
      CallableStatement stmt = conn.prepareCall("BEGIN get_emp_rs(?, ?); END;");
      stmt.setInt(1, 30); // DEPTNO
      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
      stmt.execute();
      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
      while (rs.next()) {
        System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 
      }
      rs.close();
      rs = null;
      stmt.close();
      stmt = null;
      conn.close();
      conn = null;
    }
    catch (SQLException e) {
      System.out.println(e.getLocalizedMessage());
    }
  }

  public static void main (String[] args) {
    new TestResultSet();
  }
}

}}}


== Exceptions ==
{{{#!highlight sql
--
BEGIN
  EXCEPTION WHEN others THEN
  dbms_output.put_line('Ups ....!');
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
{{{#!highlight sql
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.
{{{#!highlight sql
select sys_guid() from dual;
}}}

== Data types ==
 * number
 * nvarchar2
 * int
 * real

== Limit number of rows (rownum) ==
{{{#!highlight sql
select * from tablex 
where rownum <=10;
}}}

== Docker container Oracle DB XE 21c ==

Should have [[https://www.oracle.com/database/technologies/oracle21c-linux-downloads.html|oracle-database-xe-21c-1.0-1.ol8.x86_64.rpm]] in the same folder of the Dockerfile and steps.sh file.

=== Dockerfile ===
{{{#!highlight sh
FROM oraclelinux:8.7
ENV ORACLE_DOCKER_INSTALL=true
CMD ["/bin/bash"]
}}}

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

=== Steps ===
{{{#!highlight sh
docker build -t oracledb_test .
docker run --rm -dit --name oraclelinux -p 1521:1521 -v $PWD:/mnt/ -e ORACLE_DOCKER_INSTALL=true oracledb_test 
docker exec -it oraclelinux bash
cd /mnt/
time sh steps.sh # real	24m43.345s
source ~/.bashrc
sqlplus system/TestOracle01@localhost/xe
SELECT table_name  FROM dba_tables;
exit
}}}