CREATE TRIGGER

Örnek
Şöyle yaparız.

CREATE TABLE x (i NUMBER(10));

CREATE TRIGGER x_int_trg
 BEFORE INSERT OR UPDATE ON x
 REFERENCING NEW AS new
 FOR EACH ROW
  BEGIN
   IF :new.i NOT BETWEEN -2147483648 AND 2147483647 THEN
    raise_application_error(-20000, 'Not a 32 bit integer');
  END IF;
END x_int_trg;
/

Örnek
Şöyle yaparız.

CREATE OR REPLACE TRIGGER SYS.LOGON_TRIGGER
  AFTER LOGON ON DATABASE
DECLARE
  THIS_USER VARCHAR2(50);
BEGIN
  SELECT OSUSER INTO THIS_USER FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV','SID');
  IF THIS_USER NOT IN (<List of Users>)
    THEN RAISE LOGIN_DENIED;
  ENDIF;
END;
/

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;