Oracle APEX

Giriş

Açıklaması şöyle

In a way, Oracle Forms can be considered the grand-grand-grandfather of Oracle APEX. The basic idea behind these technologies is the same — easy creation of data-driven web applications with a minimal amount of coding required. Oracle APEX takes this philosophy much further, however, and is a much more evolved and modern technology. Also, most (if not all) Forms modules have their APEX counterparts.

The history of Oracle APEX dates back to 1999 when it was known as Oracle HTML DB (there were also other names, such as Flows and Project Marvel). Over the years, many versions of this technology have been released, and a few groundbreaking features have been introduced — such as plug-ins, Interactive Grid, or REST and PWA support.

Just like Forms, Oracle APEX uses the PL/SQL programming language. However, in APEX, a simple data-driven application can be created entirely — or almost entirely — through the use of specialized Wizards that allow you to connect various ready-made elements and make them work together. You can set up basic business logic, create a layout and interface, and get things working without writing a single line of code. Of course, if you’re not afraid of the command line and have the necessary coding skills (or team up with someone skilled), you can still use them to deliver more complex and powerful solutions.

It’s also worth noting that APEX uses the same database that Forms does (Oracle DB), so it’s equally scalable, safe, and stable. You also retain access to important features, ML/AI modules, Spatial, and so on. The main difference is that, unlike Forms, APEX works in the cloud.

There are no plans to abandon the development of Oracle APEX. It’s quite the opposite: APEX is becoming popular in the world of low-code development and, thus, is actively developed and supported by Oracle – which is great news for your software. The current version of Oracle Application Express is 22.1, and it was released in May 2022.

Oracle Forms

Giriş

Açıklaması şöyle. Oracle Forms yerine APEX kullanılmalı

A Little Bit of History
Oracle Forms is a Rapid Application Development (RAD) tool that allows you to create data entry systems that use database objects (mostly information) from the Oracle Database. It uses the popular PL/SQL language. While originally Forms was a standalone product, currently, it’s a component of Oracle Fusion Middleware.

The first version of Oracle Forms was known by a different name — Interactive Application Facility (IAF) — and saw the light of day in 1981 (along with the second iteration of the Oracle Database). IAF was later renamed to FastForms and then SQL*Forms, which is where the current name comes from (it came along with version 4.0). The very first variant of this technology was only comprised of a compiler and a runtime interpreter, but it quickly grew and evolved.

The main reason Forms got popular is the relative simplicity and ease of use. Creating Oracle Forms applications is a very natural process — anyone with basic SQL and PL/SQL knowledge can handle it. The work usually comes down to modifying so-called triggers, which are event-handling functions used to generate specific effects (i.e., opening a form after a button is pressed, etc.). Because of this, many things can be done without or with minimal coding. You can also change the form layouts using object libraries.

The newest “big” version of Oracle Forms was delivered along with Fusion Middleware 12c and was released in October 2015. It’s commonly known as Oracle Forms 12c, but it’s worth pointing out that it has received several updates since its release — the current Forms version is 12.2.1.4.0 (dated September 2019).

In the Oracle Forms Statement of Direction from a year ago, the company openly states it has no plans for completely dropping support for Oracle Forms. However, the level of the provided support is another matter. Premier Support for Fusion Middleware 12c (and, thus, Oracle Forms 12c) will end in December 2023 (and Extended Support is next in line, ending in August 2025).

What’s more, Oracle Forms isn’t a future-proof technology. It won’t provide you with functionalities and features typical for current, cutting-edge web apps. It’s also hard to find people who can work with it effectively since young developers concentrate on other solutions for obvious reasons.

All of the above means that if you want your Oracle Forms-based software to stay stable and useful, you should start thinking about moving to another, more up-to-date technology now.

Debezium Connector

Giriş

Eğer Oracle, Debezium için hazır değilse ayarları yapmak için bir yazı burada.

Oracle LogMiner Paketi

Oracle LogMiner Paketi yazısına bakabilirsiniz

Örnek

Şöyle yaparız

curl -i -X POST -H “Accept:application/json” 
 -H “Content-Type:application/json” http://ip_kafka_connect:8083/connectors/ 
 -d ‘
{
 “name”:”debezium-ora-001",
 “config”: {
   “connector.class”:”io.debezium.connector.oracle.OracleConnector”,
   “db_type”:”oracle”,
   “tasks.max”:”1",
   “database.server.name”:”hostname_of_database",
   “database.tablename.case.insensitive”:”true”,
   “database.oracle.version”:”12+”,
   “database.hostname”:”10.23.131.132",
   “database.port”:”1522",
   “database.user”:”c##xstrm”,
   “database.password”:”xs”,
   “database.dbname”:”dbz”,
   “database.pdb.name”:”dbz1",
   “database.out.server.name”:”dbzxout”,
   “database.history.kafka.bootstrap.servers”:”10.23.131.131:9092",
   “database.history.kafka.topic”:”debezium.oracle”,
   “database.history.skip.unparseable.ddl”:”true”,
   “include.schema.changes”:”true”,
   “table.whitelist”:”DEBEZIUM.CUSTOMERS”,
   “errors.log.enable”:”true”
   }
 }’

exp komutu

Örnek

Şöyle yaparız

exp adv/adv@advfrw file=<dump-file-name>.dmp tables=ATGSR_%

CREATE DATABASE LINK

Örnek

Şöyle yaparız

CREATE PUBLIC DATABASE LINK CUST2DB.PRIMARY.COM
    CONNECT TO mydatabase IDENTIFIED BY "mypassword"
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=10.44.16.53)(PORT=1535))
                (CONNECT_DATA=(SERVICE_NAME=CUST2DB))
            )';

drop database link CUST2DB.PRIMARY.COM;

SHUTDOWN

Örnek

Şöyle yaparız

SHUTDOWN IMMEDIATE

expdp komutu

Giriş

expdp komutu "export  data pump" anlamına geliyor. Oracle Database 10g Release 1 (10.1)'den sonrası için kullanılır. Veri tabanının dump'ını alır. Daha eski bir veri tabanı varsa exp komutu kullanılır 

Örnek

Şöyle yaparız

C:\> set ORACLE_SID=ADV

C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

DBA_REGISTRY Sistem Tablosu - Oracle'daki Yüklü Bileşenleri Gösterir

Örnek

Şöyle yaparız

SELECT comp_name, version, status FROM dba_registry;

COMP_NAME                          VERSION    STATUS
---------------------------------- ---------- ------
Oracle Workspace Manager           11.2.0.4.0 VALID
Oracle Database Catalog Views      11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine       11.2.0.4.0 VALID

tnsping komutu

Örnek

Şöyle yaparız

$ tnsping CUST2DB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 30-MAR-2022 03:25:26

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/export/home/oracle/products/12.2.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = corelan-3-scanvip)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUST2DB)))
OK (0 msec)

MINUS Operator

Örnek

Şöyle yaparız

SELECT 'FirstPart' FROM mytable t
MINUS
SELECT'FirstPart', FROM myothertable t;

SET - System Variables

verify off

Açıklaması şöyle

The VERIFY setting controls whether or not SQL*Plus displays before and after images of each line that contains a substitution variable.

Örnek

Şöyle yaparız

SQL> DEFINE dummy_char = 'X'
SQL> SELECT * FROM dual
  2   WHERE dummy = '&&dummy_char';
old   2:  where dummy = '&&dummy_char'
new   2:  where dummy = 'X'
D
-
X

Açıklaması şöyle

Notice that line 2, which contained the reference to the &&dummy_char substitution variable, was displayed before and after the reference to the variable was replaced by its value. If you don’t like this display, you can turn it off with SET VERIFY OFF.

Örnek

Şöyle yaparız

DEFINE OBJ_NAME="foo"
DECLARE
  N INTEGER := 0;
BEGIN
  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='&OBJ_NAME' AND
    OBJECT_TYPE='FUNCTION';
  IF N != 0 THEN
    RAISE_APPLICATION_ERROR( -20001, '&OBJ_NAME ALREADY EXISTS!' );
  END IF;
END;
/

Çıktı olarak şunu alırız

SQL> @foo.SQL
old   4:  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='&OBJ_NAME'
  AND OBJECT_TYPE='FUNCTION';
new   4:  SELECT COUNT(OBJECT_NAME) INTO N FROM USER_OBJECTS WHERE OBJECT_NAME='FOO_LIST'
  AND OBJECT_TYPE='FUNCTION';
old   6:  RAISE_APPLICATION_ERROR( -20001, '&OBJ_NAME ALREADY EXISTS!' );
new   6:  RAISE_APPLICATION_ERROR( -20001, 'GET_FALIST ALREADY EXISTS!' );

Örnek

Şöyle yaparız

SQL> set verify off
SQL> DECLARE
  2          inputData VARCHAR2(1024);
  3  BEGIN
  4          inputData :='&&inputData' ;
  5          Dbms_Output.put_line('Value entered is:' || inputData);
  6  END;
  7  /
Enter value for inputdata: sdf

PL/SQL procedure successfully completed.

SQL>

Çıktı olarak şunu alırız

Enter value for inputdata: check
old   4:         inputData :='&&inputData' ;
new   4:         inputData :='check' ;
Value entered is:check

PL/SQL procedure successfully completed.

ALL_SOURCE - Stored Function Kaynak Kodunu Gösterir

Örnek

Şöyle yaparız

SELECT type, text FROM all_source WHERE name = 'GET_FALIST' ORDER BY type, line;

Oracle Streams Asenkron Kullanım

Eğer asenkron kullanacaksak bu iki şekilde yapabiliyoruz

1. Asynchronous Distribution HotLog

2. Asynchronous AutoLog

Bazı adımlar şöyle

-- Publisher : Create the Change Sources
BEGIN
   DBMS_CDC_PUBLISH.CREATE_HOTLOG_CHANGE_SOURCE(
       change_source_name => 'MAILING_LIST',
       description => 'test source',
       source_database => 'source_db');
END;
/

-- Publisher : Create Change Sets
BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'DAILY_MAILING_LIST',
       description => 'change set for contact info',
       change_source_name => 'MAILING_LIST',
       stop_on_ddl => 'y');
END;
/
-- Publisher: Create the Change Tables
BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner             => 'staging_streamspub',
       change_table_name => 'contacts_ct',
       change_set_name   => 'DAILY_MAILING_LIST',
       source_schema     => 'SH',
       source_table      => 'CONTACTS',
       column_type_list  => 'PHONE_NUMBER NUMBER(10), CONTACT_NAME VARCHAR2(50),
          ZIP_CODE NUMBER(5),
          EMAIL_ADDRESS VARCHAR2(15), HOME_ADDRESS VARCHAR(20)',
       capture_values    => 'both',
       rs_id             => 'y',
       row_id            => 'n',
       user_id           => 'n',
       timestamp         => 'n',
       object_id         => 'n',
       source_colmap     => 'n',
       target_colmap     => 'y',
       options_string    => 'TABLESPACE TS_DAILY_MAILING_LIST');
END;
/
--Publisher: Enable the Change Source
BEGIN
  DBMS_CDC_PUBLISH.ALTER_HOTLOG_CHANGE_SOURCE(
    change_source_name => 'DAILY_MAILING_LIST',
    enable_source      => 'Y');
END;
/

Publisher: Grant Access to Subscribers
GRANT SELECT ON staging_cdcpub.products_ct TO subscriber1;

Varrays - Variable-Size Arrays

Örnek

Şöyle yaparız. Varray null değer için "" karakteri basar.

DECLARE type type_ntt IS TABLE OF NUMBER;

l_ntt_var1 type_ntt:=type_ntt();

BEGIN
  l_ntt_var1.extend();
  l_ntt_var1(1):=857;
  l_ntt_var1.extend(3);
  l_ntt_var1.extend(3,1);

  FOR index IN 1..l_ntt_var1.count
  LOOP
    dbms_output.put_line('Index value ['||ndex||']: Element value - '||l_ntt_var1(ndex));
  END LOOP loop_ntt;
END;
/
dbms_output:
Index value [1]: Element value - 857
Index value [2]: Element value - 
Index value [3]: Element value - 
Index value [4]: Element value - 
Index value [5]: Element value - 857
Index value [6]: Element value - 857
Index value [7]: Element value - 857

Örnek

Şöyle yaparız

type T_EXPIRATIONDATES is table of varchar2(14);
EXPIRATIONDATES T_EXPIRATIONDATES;

EXPIRATIONDATES := T_EXPIRATIONDATES();
EXPIRATIONDATES.EXTEND(100);

//Iterate over cursor
for REGISTRATIONS_ROW in C_REGISTRATIONS(FUNCTIONS_ROW.FUNCTION_ID) loop
  EXPIRATIONDATES(REGISTRATIONS_ROW.FUNCTIONCODE+1) := REGISTRATIONS_ROW.EXPIRATIONDATE;
end loop;

OUT_EXPIRATIONDATES_ROW VARCHAR2(32767);

for IDX in 1..100 loop
  OUT_EXPIRATIONDATES_ROW := OUT_EXPIRATIONDATES_ROW||'|'||EXPIRATIONDATES(IDX);
end loop;

TRUNCATE TABLE

Giriş

Belirtilen tabloyu boşaltır. MySQL ile de aynı şekilde kullanılır

Örnek

TRUNCATE TABLE get_falist;

TO_NUMBER Metodu - String To Number

Giriş

Açıklaması şöyle

TO_NUMBER converts a string to a number of data type NUMERIC. 

TO_CHAR performs the reverse operation; it converts a number to a string. 

MySQL karşılığı CAST() metodu

Örnek

Şöyle yaparız

SELECT TO_NUMBER(functioncode) functioncode FROM ...

IS TABLE OF

Giriş

Stored Procedure içinde kullanılır

Örnek

Şöyle yaparız. Belirtilen tablonun aynısı olarak bir başka tablo yaratır

type T_MSISDNS is table of varchar(23);

Örnek

Şöyle yaparız. Belirtilen tablonun aynısı olarak bir başka tablo yaratır

TYPE <Typename>  IS TABLE OF <table>.ROW_ID%TYPE;