SQL - SUBSTR

Giriş
Belirtilen konumlar arasındaki string'i döner. İlk parametre string değeridir. İkinci ve üçüncü parametre konum değeridir.

Örnek
Şöyle yaparız. ":" karakterine kadar olan kısım isteniyor.

SQL> SELECT substr(VALUE, 1, instr(VALUE, ':')-1) FROM MY_PROPERTIES WHERE KEY = 'IP2';
--------------------------------------------------------------------------------
10.179.66.73

Örnek
Şöyle yaparız

with t as
(
select 'red/green/blue' as str from dual
)
select substr(str,1,instr(str,'/',1,1)-1) str1, -- birinci / karakterine kadar
       substr(str,1,instr(str,'/',1,2)-1) str2, -- ikinci / karakterine kadar
       str as str_whole
  from t;

STR1    STR2        STR_WHOLE
----    ----------  --------------
red     red/green   red/green/blue

v$session Sistem Tablosu

Giriş
TCP bağlantısı eşittir session gibi düşünülüyor. Ancak böyle olmak zorunda değil. Açıklaması şöyle

Connections vs. Sessions

It surprises many people to discover that a connection is not synonymous with a session. In most people’s eyes they are the same, but the reality is they do not have to be. A connection may have zero, one, or more sessions established on it. Each session is separate and independent, even though they all share the same physical connection to the database. A commit in one session does not affect any other session on that connection. In fact, each session using that connection could use different user identities! In Oracle, a connection is simply a physical circuit between your client process and the database instance — a network connection, most commonly. The connection may be to a dedicated server process or to a dispatcher. As previously stated, a connection may have zero or more sessions, meaning that a connection may exist with no corresponding sessions.

v$session Tablosu Nedir

Açıklaması şöyle

The standard views to check for database performance are v$session, v$sql, and others. The v$ views work well if the database is hosted on a single node. However, if the database is split into multiple nodes, we need to look at the gv$ views. The INST_ID column will tell us which exact node is fulfilling the request.

Örnek - All Established Connections

Şöyle yaparız

elect status, count(1) 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
group by status;

Örnek - Active Connections

Şöyle yaparız

select last_call_et, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait 
from gv$session
where module = '<module-name>' 
and type = 'USER' 
and status = 'ACTIVE' 
order by last_call_et desc, module, sql_id, event, blocking_instance, blocking_session,
  seconds_in_wait;

select * from gv$sql where sql_id = '<above-sql-id>';

Örnek - Inactive Connections

Şöyle yaparız

select last_call_et, module, prev_sql_id   from gv$session 
  where module = '<module-name>' 
  and type = 'USER' 
  and status = 'INACTIVE';

select * from gv$sql where sql_id = '<above-prev-sql-id>';  

Örnek
Şöyle yaparız.

SQL> select sid, process, port, paddr from v$session where username = 'BP';

       SID PROCESS                        PORT PADDR
---------- ------------------------ ---------- ----------------
       395 31251                         35298 0000000066E75338
       399 31251                         35298 0000000066E75338




JSON_VALUE

Giriş
Açıklaması şöyle.

Oracle Database 18c fully supports schemaless application development using the JSON data model. This allows for a hybrid development approach: all of the schema flexibility and speedy application development of NoSQL document stores, combined with all of the enterprise-ready features in Oracle Database 18c.

Örnek
Şöyle yaparız.

SELECT CASE WHEN SBM_MESSAGE_BODY IS JSON THEN 1 ELSE 0 END AS IS_JSON,  
       JSON_VALUE(SBM_MESSAGE_BODY, '$.CRMId') AS CRMId
  FROM SBM_SERVICEBUS_MESSAGE 
 WHERE SBM_ID=1;

SYSDATE Function

Giriş
Sistem saatini döner. SYSTIMESTAMP metoduna da bakılabilir.

Örnek
Date tipindeki bir sütun ile karşılaştırmak için şöyle yaparız.

SELECT *
FROM my_table
WHERE mydate_column >= TRUNC(SYSDATE - 3) AND mydate_column < TRUNC(SYSDATE + 1);

CREATE TABLE

Örnek
Başka tablodan getirilen veri ile yaratmak için şöyle yaparız.

CREATE TABLE T1 AS
SELECT TIDSPUNKT
  FROM T2
 where TIDSPUNKT >= '2018-04-02'
UNION ALL
SELECT TIDSPUNKT
  FROM T3
 where TIDSPUNKT between trunc(sysdate - 756) and '2018-04-01';

SYS_REFCURSOR

Giriş
Stored Procedure'larda sonucu cursor olarak döndürmek için kullanılır. Aslında bir struct listesi döndürülür gibi düşünebilir.
Örnek
Sonuç şu sql gibidir.

select 1 as id_col, 'My Name ' as name , 1000 as sal from dual;

Örnek
Şöyle yaparız.

CREATE or replace PROCEDURE P_TEST (role_list_o OUT SYS_REFCURSOR) AS
 BEGIN 
   OPEN role_list_o FOR SELECT id, name FROM role , role_details ; 
  END;

Oracle JDBC Thin Driver - OracleDataSource Sınıfı

Giriş
Şu satırı dahil ederiz.

import oracle.jdbc.pool.OracleDataSource;

DataSource arayüzünü gerçekleştirir.

Kullanım
1. Default constructor ile nesne yaratılır

2. setX metodları ile bağlantı ayarları belirtilir.

3. setX metodlarını teker teker çağırmak istemiyorsak, setConnectionProperties() ile hepsi bir Property nesnesi olarak geçilir.

4. setXEnabled metodları ile bazı özellikler etkinleştirilir.

5. getConnection() ile connection alınır

constructor
Şöyle yaparız.

OracleDataSource dataSource = new OracleDataSource();

getConnection metodu
Şöyle yaparız.

Connection connection = dataSource.getConnection();

setConnectionProperties

Şöyle yaparız

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

Properties info = new Properties();
info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
info.put(OracleConnection.CONNECTION_PROPERTY_FAN_ENABLED, false);

OracleDataSource ods = new OracleDataSource();
ods.setURL(DB_URL);
ods.setConnectionProperties(info);

setDriverType metodu

Şöyle yaparız.

OracleDataSource dataSource = new OracleDataSource();
dataSource.setUser(userName);
dataSource.setPassword(password);
dataSource.setURL(dataSourceUrl);
dataSource.setImplicitCachingEnabled(true);
dataSource.setFastConnectionFailoverEnabled(true);
dataSource.setDriverType(driverClass);

setFastConnectionFailoverEnabled metodu
Bu metod yeni sürümlerde artık yok. Açıklaması şöyle. Veri tabanı kapanıp açılırsa geçersiz hale gelen bağlantılar temizlenir.

When the DB restarts, do you remove the invalid connections from the pool using a ClearPool type API or Oracle Fast Connection Failover (FCF)? If the answer is no, that's the likely reason for the timeout or error when pooling is on.

The invalid connections remain in the pool and the app is picking up one of these connections.

The ClearPool APIs and/or FCF are intended to resolve this HA issue. ClearPool is a manual process unfortunately and is not a great solution for this specific HA situation. Most customers prefer to automate connection cleanup with FCF.

To use FCF, your DB needs to enable Fast Application Notification, then turn on FCF on the server side. On the client side, you just turn HA Events=true in the connection string, which should already be turned on by default if you are using ODP.NET 12.2.

Şöyle yaparız.

dataSource.setFastConnectionFailoverEnabled(true);

setImplicitCachingEnabled metodu

Şöyle yaparız.

dataSource.setImplicitCachingEnabled(true);

setPassword metodu
Şöyle yaparız.

String password =...;
dataSource.setPassword(password);

setUrl metodu
Şöyle yaparız.

String url = "jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl");
dataSource.setURL(url);

Bu metod yerine ayrı ayrı setter'lar kullanılabilir. Şöyle yaparız.

dataSource.setServerName("xxx");
dataSource.setPortNumber(xxx);
dataSource.setDriverType("thin");
dataSource.setNetworkProtocol("tcp");
dataSource.setDatabaseName("xxxx");

setUser metodu
Şöyle yaparız.

String username = ...;
dataSource.setUser(username);

Diğer
Örnek
Şöyle yaparız.

<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
    <property name="dataSourceName" value="ds" />
    <property name="URL"
        value="jdbc:oracle:thin:@127.0.0.1:1521:test" />
    <property name="user" value="system" />
    <property name="password" value="3123312257" />
</bean>

Örnek
Şöyle yaparız.

@Bean
DataSource dataSource() throws SQLException {
  OracleDataSource dataSource = new OracleDataSource();
  dataSource.setUser("yourusername");
  dataSource.setPassword("yourpassword");
  dataSource.setURL("jdbc:oracle:thin:@yourserver:1521:xe");
  dataSource.setImplicitCachingEnabled(true);
  dataSource.setFastConnectionFailoverEnabled(true);
  return dataSource;
}    

Örnek

Şöyle yaparız

import oracle.jdbc.pool.OracleDataSource;

public static void main(String[] args) {
  OracleDataSource ods;
  try {
    ods = new OracleDataSource();
    // jdbc:oracle:thin@[hostname]:[port]/[DB service/name]
    ods.setURL("jdbc:oracle:thin:@localhost:1521/FREEPDB1");
    ods.setUser("[Username]");
    ods.setPassword("[Password]");
    Connection conn = ods.getConnection();
    PreparedStatement stmt = conn.prepareStatement("SELECT 'Hello World!' FROM dual");
    ResultSet rslt = stmt.executeQuery();
    while (rslt.next()) {
      System.out.println(rslt.getString(1));
    }
  } catch (SQLException e) {
    e.printStackTrace();
  }
}