Invoking Java in the Database

Giriş

Kendi kodumdan Stored Procedure çağrısı yaparak aslında bir Java kodunu çalıştırmak mümkün.

Örnek

Şöyle yaparız

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED MYSCHEMA.JavaTools
AS 
package util.oracle;
import java.io.*;
import java.net.InetAddress;  
import java.util.List;

public class JavaTools {

  public static double pingOSCommand(String Command){
    double time = -1;
    try {
      Process process = Runtime.getRuntime().exec(Command);
      process.waitFor();
      BufferedReader stdInput = new BufferedReader(
        new InputStreamReader(process.getInputStream()));
      BufferedReader stdError = new BufferedReader(
        new InputStreamReader(process.getErrorStream()));
      while ((String s = stdInput.readLine()) != null) {
        if (s.contains("time=")) {
	  if (s.contains("ms")){
            time = Double.parseDouble(s.substring(s.indexOf("time=")+5,
              s.indexOf("ms")-1));
	  }
        }
      }
      while ((s = stdError.readLine()) != null) {
        System.out.println(s);
      }
      return time;
    } catch (Exception e){
      System.out.println(e.getMessage());
      return -1;
    } 
  }
};
/ 
/******************************************************************************/
GRANT EXECUTE ON JAVA SOURCE MYSCHEMA.RailwayJavaTools TO MYUSER;
COMMIT;

Execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.io.FilePermission',
  '<<ALL FILES>>', 'execute');
execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission',
  'writeFileDescriptor', '*' );
execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.lang.RuntimePermission',
  'readFileDescriptor', '*' );
COMMIT;

Bunu çağırmak için şöyle yaparız. Sonuç olarak double döndürür

CREATE OR REPLACE PACKAGE MYSCHEMA.MyTools 
AS
  function f_pingOSCommand(command varchar2) return number;
END MyTools;
/

CREATE OR REPLACE PACKAGE body MYSCHEMA.MyTools
AS
  function f_pingOSCommand(command varchar2) 
  return number 
  as
  language java
  NAME 'util.oracle.JavaTools.pingOSCommand(java.lang.String) return double';
		
end MYTools;
/

Bunu çağırmak için şöyle yaparız. Sonuç olarak double döndürür

SELECT MYSCHEMA.MyTools.f_pingOSCommand(?) FROM DUAL

Oracle Streams Senkron Kullanım

Giriş

Şeklen şöyle

Senkron kullanım için adımlar burada

1. Create User & Setup Privileges : Stream Administrator rolü yaratılır

2. Create Database Links at source and target

3. Create Queue at source and target

4. Create Capture at source

5. Create Apply Process at target

6. Create Propagation at source

7. Instantiation at target

8 Start Apply at target

9. Start Capture at source

Kullanılan PL/SQL paketleri şöyle

DBMS_APPLY_ADM

DBMS_CAPTURE_ADM

DBMS_FLASHBACK

DBMS_OUTPUT

DBMS_STREAMS_ADM

DBMS_APPLY_ADM Paketi

START_APPLY(), STOP_APPLY(), DELETE_ALL_ERRORS() gibi metodlar kullanılabilir

DBMS_APPLY_ADM.START_APPLY(apply_name => 'SYNC_APPLY_1');
...
DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'SYNC_APPLY_1');
DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => 'SYNC_APPLY_1');

Duplicate Rows

Açıklaması şöyle

1. You cannot use Oracle Streams to replicate a table which has duplicate rows. It will not work.
2. If you don’t have unique constraint on your table in some form, you will have duplicate rows.

Create Database Links

Şöyle yaparız

--on source
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK REP01 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'REP01';
select count(*) from dba_tables@REP01;

-- on target
CONNECT strmadmin/strmadminpw;
CREATE DATABASE LINK DB05 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DB05';
select count(*) from dba_tables@DB05

Create Queue

Örnek

Şöyle yaparız

DBMS_STREAMS_ADM.SET_UP_QUEUE(
 queue_table =>'STRADMIN.SQT_IMMEDIATE1_S', 
 queue_name => 'STRADMIN.SQ_IMMEDIATE_S');

Apply

streams_type olarak APPLY verilebilir

Örnek

Şöyle yaparız.

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => ...,
  streams_type => 'apply',
  streams_name => ...,
  queue_name => ...
);

Capture

streams_type olarak CAPTURE, SYNC_CAPTURE verilebilir. Açıklaması şöyle

Synchronous Mode

Synchronous Capture uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real-time on the source database. The change tables are populated when DML operations on the source table are committed. Hence it is titled “Synchronous” Change Data Capture.

New change data arrives automatically as DML operations on the source tables are committed. Publishers can define new change sets in the predefined SYNC_SOURCE change source or use the predefined change set, SYNC_SET. The SYNC_SET change set cannot be altered or dropped.

While the “invasive” nature of the approach of the synchronous mode of Change Data Capture adds overhead to the source database at capture time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section) by simplifying the extraction of change data.

Örnek - Senkron

Şöyle yaparız. inclusion_rule FALSE verilerek capture işlemi yapılmıyor

DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name=>'DEV110.APP_QUEUE',
  streams_type=>'capture',
  streams_name=>'CAPTURE_110DEV',
  queue_name=>'REP_CAPTURE_QUEUE',
  include_dml=>TRUE,
  include_ddl=>TRUE,
  include_tagged_lcr=> FALSE,
  source_database=>'DB05',
  inclusion_rule=>FALSE,
  and_condition=>NULL);

Server Control - srvctl komutu

status

Örnek

Şöyle yaparız

srvctl status listener

Örnek

Şöyle yaparız

srvctl status database -db MYDB

Oracle Clusterware Control - crsctl komutu

Açıklama yaz

Oracle Recovery Manager - RMAN

Açıklama yaz

Maven İle Oracle JDBC Thin Driver

JDBC Thin Driver vs OCI

Açıklaması şöyle. Yani Java kullanıyorsak JDBC Thin Driver yeterli.

Oracle provides four types of drivers for their database, but I'll only enumerate the two you asked about.

The OCI driver is a type 2 JDBC driver and uses native code to connect to the database. Thus, it is only an option on platforms that have native Oracle drivers available and it is not a "pure" Java implementation.

Oracle's JDBC Thin driver is a type 4 JDBC Driver that uses Java sockets to connect directly to Oracle. It implements Oracle's SQL*Net TCP/IP protocol directly. Because it is 100% Java, it is platform independent ...

Maven

ojdbc "Oracle JDBC Driver" anlamına gelir

ojdbc11

Örnek

Şöyle yaparız

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc11</artifactId>
  <version>23.2.0.0</version>
</dependency>

ojdbc10

Örnek

Şöyle yaparız

Java 11
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc10</artifactId>
    <version>${oracle.version}</version>
</dependency>

Java 8
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>${oracle.version}</version>
</dependency>

Java 6
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>${oracle.version}</version>
</dependency>

ojdbc8

Java 8 içindir

Örnek

Şöyle yaparız

<dependency>
  <groupId>com.oracle</groupId>
  <artifactId>ojdbc8</artifactId>
  <version>19.7.0.0</version>
</dependency>

Şöyle yaparız

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc8-production</artifactId>
  <version>21.7.0.0</version>
  <type>pom</type>
</dependency>

ojdbc7

Java 8 öncesi içindir

Örnek 

Şöyle yaparız. 

<dependency>
  <groupId>com.oracle</groupId>
  <artifactId>ojdbc7</artifactId>
  <version>12.1.0.2</version>
</dependency>

Sınıflar

Bazı sınıflar şöyle 

OracleDataSource

OracleConnection

Connection Pool

Oracle Universal Connection Pool kullanılabilir.

Universal Connection Pool - PoolDataSource Sınıfı yazısına bakabilirsiniz.

Universal Connection Pool - PoolDataSource Sınıfı

Giriş

Açıklaması şöyle. Yani 12c'den sonra JDBC sürücüsü de en az 12.1.0.2 olmalı

Prior to 12c (i.e., 12.1.0.1.0), UCP could work with any version of Oracle JDBC driver. With the new pool, UCP 12.1.0.2, it is dependent on Oracle JDBC driver 12.1.0.2.

Örnek

Şöyle yaparız. OracleDataSource sınıfını kullanıyor

import oracle.ucp.jdbc.PoolDataSource;
import java.sql.Connection;
import java.sql.SQLException;

PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
ds.setUser("...");
ds.setPassword("...");
ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
ds.setURL("jdbc:oracle:thin:@exampledb?TNS_ADMIN=/msdataworkshop/creds");
Connecton con = ds.getConnection();

Örnek

Şöyle yaparız.

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

pds.setURL(...);
pds.setUser(...);
pds.setPassword(...);
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");

//Setting pool properties
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(20);

Connection conn = pds.getConnection();

Şöyle yaparız.

int usedConnectionCount = pds.getBorrowedConnectionsCount();
int availableConnectionCotun = pds.getAvailableConnectionsCount();