Tipy a poznámky k vývoji

Tipy a poznámky k vývoji - Oracle

Oracle 19, PL/SQL i samotné SQL
Mrtvé linky prosím hlašte - ty o kterých vím a jde to nahrazuji pomocí Wayback Machine :)

GUID

16B náhodných raw dat (32 znaků)

SELECT SYS_GUID() AS guid FROM dual; -- např. 01EA3F6DD60D45EAA7AEAC3AC0B93347
 
DECLARE
  lGUID VARCHAR2(32) := SYS_GUID();
  ...

pro vytvoření UUID je třeba výstup naformátovat:
- 36 znaků ve skupinách oddělených pomlčkami: 8-4-4-4-12
- viz https://cs.wikipedia.org/wiki/Univerzálně_unikátní_identifikátor

Encode / decode do API formátů

URL encode/decode (kódování do URL, viz https://cs.wikipedia.org/wiki/URL_kódování)

utl_url.escape(url => 'nějaký text', url_charset => 'UTF-8'); -- n%C4%9Bjak%C3%BD%20text
utl_url.unescape('n%C4%9Bjak%C3%BD%20text', 'UTF-8');         -- nějaký text

text - base64 (kódování parametrů volání API, viz https://cs.wikipedia.org/wiki/Base64)

DECLARE
  lText         VARCHAR2(255);
  lBase64Coded  VARCHAR2(255);
BEGIN
  lBase64Coded := utl_encode.text_encode(buf => 'nějaký text', encoding => utl_encode.base64);
  dbms_output.put_line(lBase64Coded); -- buxqYWv9IHRleHQ=
 
  lText := utl_encode.text_decode(buf => lBase64Coded, encoding => utl_encode.base64);
  dbms_output.put_line(lText); -- nějaký text
END;

Rekurzivní CTE

pokud potřebujeme pracovat s ostatními řádky dotazu, máme tyto možnosti:
- rekurzivní CTE (Common Table Expression - WITH)
  - SQL standard (mělo by fungovat všude)
  - hodí se především na procházení / generování stromových struktur
- agregační funkce (viz over…)
- CONNECT BY
  - jde o Oracle vlastnost, má jednodušší syntaxi než CTE
- pipelined funkce
  - většinou 'nejlepší' řešení - je nejjednodušší a proti rek. CTE je zjevně mnohem rychlejší. Má ale i zásadní záludnosti (viz např. zahlušení výjimek)

příklad rekurzivního CTE (výpis řady 15 čísel):

-- rekurzivní CTE musí obsahovat seznam sloupců
WITH cisla(cislo) AS (
  -- počáteční krok
  SELECT 1 AS cislo FROM dual
  UNION ALL -- vždy musí být all
  -- rekurzivní krok
  SELECT c.cislo + 1 FROM cisla c WHERE c.cislo < 15
)
SELECT * FROM cisla;

Obejití omezení funkce listagg()

funkce listagg() spojuje data vybraného sloupce do varchar2 textu. Velikost výsledku je omezena na 4000 znaků
tipy na řešení (Wayback Machine), techniky agregace stringu
pomalejší řešení pomocí xmlagg:
```
SELECT RTRIM(XMLAGG(XMLELEMENT(e, t.ke_spojeni, ', ').EXTRACT('//text()') ORDER BY t.sloupec_trideni).GetClobVal(), ', ')
FROM tabulka t
```
- XMLAGG(xml_element ORDER BY podle_čeho_třídit)
- XMLELEMENT(název_elementu, hodnoty_elementu) vytvoří XMLTYPE sloučeninu hodnoty na řádku (t.ke_spojeni) a ', '
- .extract('//text()') - extrahuje obsah elementu (výsledek je stále XMLTYPE)
- .GetClobVal() - převede extrahovaný text z XML do clobu
- RTRIM() - ořízne zprava o poslední ', '

Přehled grantů

pro získání přehledu grantů nad vybraným objektem (tabulka, package) lze využít pohled sys.dba_tab_privs
hodnoty ve sloupcích jsou VELKÝMI ZNAKY (uppercase)
- GRANTEE = komu je právo uděleno
- GRANTOR = kdo právo uděloval
- TABLE_NAME = samotný název objektu (klidně i package), neobsahuje název schematu
- PRIVILEGE = oprávnění (SELECT, INSERT, EXECUTE, není zde ALL :)

např.:

SELECT * FROM dba_tab_privs WHERE table_name = 'API_PRIKAZY_LOG';

Detaily konkrétního omezení

pokud na nás někde vyskočí porušení omezení a nevíme o co jde / u které je tabulky, pak se detaily dozvíme takhle:
```
SELECT * FROM all_constraints ac WHERE ac.owner = 'TEST' AND ac.constraint_name = 'TABULKA_CHECK';
```
najdeme zde název tabulky (table_name), typ omezení (constraint_type) i definici (search_condition) a popis omezení (search_condition_vc)
typy omezení:
- C - check
- P - primární klíč
- U - unikátní klíč
- R - reference (cizí klíč)
- V - check nad view
- O - read only nad view

Dummy - proměnná bez hintů

pokud chcete někde volat funkci, ale její výsledek vás nezajímá, deklarujete proměnnou kterou ale dále nevyužijete. A compiler vás pokárá HINTem…
náprava: pojmenovat proměnnou dummy

Sekvence v selectu (ORA-08002, not yet defined in this session)

pokud chcete získat hodnotu sekvence.CURRVAL v SQL dotazu, může Oracle vyhodit chybu ORA-08002: sequence MySequence.CURRVAL is not yet defined in this session
- je to proto, že před použitím CURRVAL se v aktuální session musí alespoň jednou použít NEXTVAL
- jde to ale i jinak:
```
-- aktuální hodnota sekvence TEST.TABULKA_SQ
SELECT last_number-1 AS curr_val FROM DBA_SEQUENCES WHERE sequence_name = 'TABULKA_SQ';
```

Hash

pokud chci získat hash z CLOB sloupce, není dobré používat ora_hash - je omezený na 4000 zn.

lze použít dbms_crypto.Hash:

-- vstupem je CLOB a typ hashe (3 = SHA256)
-- výstupem je RAW, ten lze zkonvertovat např. do varcharu v hexa tvaru
SELECT s.src_data, RAWTOHEX(dbms_crypto.Hash(src => s.src_data, typ => 3)) FROM test.sankce s;

dbms_crypto.Hash může mít problémy se znaky s codebase nad 127 (řešení je potřeba ověřit)

Insert a update najednou (merge)

chceme založit záznam, pokud existuje tak jen aktualizovat
možnosti:
- count a podle něj insert/update (problém s více uživateli)
- exception (prý pomalé - exception engine…)
- procka s autonomní transakcí
- merge

tabulka k příkladu:

CREATE TABLE test.tmp_tab_merge (
  id NUMBER,
  text VARCHAR2(255),
  CONSTRAINT pk_tmp_tab_merge PRIMARY KEY (id) USING INDEX
);
 
INSERT INTO test.tmp_tab_merge
  SELECT 1 AS id, 'ahoj' AS text FROM dual
  UNION ALL
  SELECT 2 AS id, 'nazdar' AS text FROM dual
  UNION ALL
  SELECT 3 AS id, 'čau' AS text FROM dual;
 
COMMIT;

příklad 1 (pokud ještě neexistuje id12, založíme ho jako "novy" jinak ho nastavíme na "aktualni"):

merge INTO test.tmp_tab_merge l USING dual ON (l.id = 12)
WHEN matched THEN
  UPDATE SET l.text = 'aktualni'
WHEN NOT matched THEN
  INSERT (id, text) VALUES (12, 'novy');

→

příklad 2 (nastaví do id1 "hoja" a přidá id15 s "aloha"):

merge INTO test.tmp_tab_merge l
  USING (
    SELECT 1 AS id, 'hoja' AS text FROM dual
    UNION ALL
    SELECT 15 AS id, 'aloha' AS text FROM dual
  ) n ON (l.id = n.id)
WHEN matched THEN
  UPDATE SET l.text = n.text
WHEN NOT matched THEN
  INSERT (id, text) VALUES (n.id, n.text);

Porovnání null hodnot

pokud máme porovnat dvě hodnoty, ve kterých se mohou vyskytovat NULL hodnoty, pak máme tyto možnosti:
- správně:
  - case when (z.a is null and z.b is null) or (z.a = z.b) then 0 else 1 end as rozdil
  - + je to jasný, řeší se správně null hodnoty
  - - zápis dlouhý jak…
- hezky:
  - case when nvl(z.a, -99999) = nvl(z.b, -99999) then 0 else 1 end as rozdil
  - + kratší zápis
  - - musí se vymyslet náhrada pro NULL tak, aby se stopro nevyskytovala v oboru hodnot které porovnáváme (to může být u uživ. vstupů oříšek)
- správně a hezky:
  - decode(z.a, z.b, 0, 1) as rozdil
  - + krátký zápis, funguje s null hodnotami, nemusíš vymýšlet náhradu
  - - není to SQL standard (Oracle only)

test:

SELECT z.a, z.b, DECODE(z.a, z.b, 0, 1) AS rozdil
  FROM (
          SELECT 1 AS A, 1 AS B FROM dual
          UNION ALL
          SELECT 1 AS A, 2 AS B FROM dual
          UNION ALL
          SELECT 2 AS A, 1 AS B FROM dual
          UNION ALL
          SELECT 2 AS A, NULL AS B FROM dual
          UNION ALL
          SELECT NULL AS A, 2 AS B FROM dual
          UNION ALL
          SELECT NULL AS A, NULL AS B FROM dual
       ) z;

Automatické id / dosazení výchozího času

automaticky navyšované ID děláme většinou přes trigger
jde to ale rovnou v definici tabulky pomocí default on null … (stejně tak jde používat např. sysdate pro automatické plnění sloupce s datem)

mám vytvořenou nějakou sekvenci (test.import_sq)

pak jde udělat:

CREATE TABLE test.soubory (
  import_id INTEGER DEFAULT ON NULL test.import_sq.NEXTVAL PRIMARY KEY,
  datum_cas DATE DEFAULT ON NULL SYSDATE,
  nazev_souboru VARCHAR2(255),
  obsah clob  
);

insert…

INSERT INTO test.soubory(nazev_souboru, obsah)
  VALUES('test.txt', 'tohle je pokus s automatickým ID a časem zápisu');
 
COMMIT;

…pak dopadne takhle:

Jaké procesy Oracle využívají nejvíc CPU

prvních 10 nejhladovějších:

SELECT ROWNUM AS RANK, a.* 
  FROM (SELECT v.sid, sess.USERNAME, sess.Serial#, program, v.VALUE / (100 * 60) CPUMins
           FROM v$statname s, v$sesstat v, v$session sess
          WHERE s.name = 'CPU used by this session'
            AND sess.sid = v.sid
            AND v.statistic# = s.statistic#
            AND v.VALUE > 0
          ORDER BY v.VALUE DESC) a
 WHERE ROWNUM < 11;

spuštěné dotazy (nejen) podle četnosti spuštění a doby zpracování:

SELECT sql_id,
       executions,
       ROUND(cpu_time / 1000000, 2) AS cpu_sec,
       ROUND(cpu_time / executions / 1000000, 4) AS cpu_per_exec,
       sql_text
FROM   v$sql
WHERE  cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

Zamčené objekty

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, S.USERNAME, S.MACHINE, S.PORT, S.LOGON_TIME, SQ.SQL_FULLTEXT
  FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
   AND S.PADDR = P.ADDR
   AND S.SQL_ADDRESS = SQ.ADDRESS;

Odstranění komentářů regexepem (ungreedy volba)

pokud mám text ahoj /*nazdar*/ čau /*aloha*/ hoja, pak odstranění zakomentovaných částí obvykle najde první výskyt /* a pak poslední */. Tím přijdeme o text mezi komentáři (čau)
- hledali bychom to regulárem /\*(.*)\*/
  - /\* = /*
  - (.*) = skupina (závorky) jakýchkoli znaků (tečka) v množství 0 a víc (hvězdička)
  - \*/ = */
co s tím?
- použít ungreedy hledání (greedy = hladové a znamená to, že cílem je co největší výsledek; ungreedy = opak - hledáme minimální výsledek)
- Oracle tohle naštěstí umí, zapisuje se *? a znamená množství 0 a víc tak, aby byl výsledek co nejmenší
- tohle odstranění komentářů tak funguje správně:
```
DECLARE
  lText VARCHAR2(255) := 'ahoj /*nazdar*/ čau /*aloha*/ hoja';
BEGIN
  lText := REGEXP_REPLACE(lText, '/\*(.*?)\*/', '', 1, 0, 'n'); -- odstranění /**/
  lText := REGEXP_REPLACE(lText, '--(.*)'); -- odstranění --
  dbms_output.put_line(lText); -- výsledek: ahoj  čau  hoja
END;
```

Spuštění procedury ze skriptu

@echo off
sqlplus -s -l USER/pass@yourdb @yoursql.sql>your_log.LOG

je fajn na to mít extra uživatele, protože zde musí být čitelné heslo
všechno co se volá musí být vždy v souboru *.sql
- ten vypadá např. takhle:
```
EXEC test.test_jobs_start_pk.nastartuj_procesy;
EXIT;
```
- ten EXIT tam musí být, jinak skript nedoběhne!

Historie tabulek (Oracle Flashback)

pokud jste provedli omylem změnu v datech a chcete ji rychle vrátit, pak je tohle možná cesta. A přes systémové tabulky takhle lze vrátit i droplé plsql entity…
omezení:
- sloupec TABLE_OWNER je prázdný
  - pokud chci hledat tabulku test.tabulka, pak tento sloupec ignoruji a použiju pouze jméno tabulky (bez schematu)
- ukládají se jen změny a jen několik posledních hodin
  - tj. nečekat historii 2 dny zpátky
  - pokud zde tabulka není, pak se asi posledních několik hodin neměnila

postup:

nejprve vyhledám časovou značku nebo SCN (system change number) změny tabulky (tabulku chci k tomuto okamžiku)

-- zobrazení dostupných záznamů k tabulce TEST.TABULKA
SELECT t.*
  FROM sys.flashback_transaction_query t
 WHERE t.table_name = 'TABULKA'
 ORDER BY t.commit_timestamp DESC;

pak si zobrazím tabulku v daném čase

-- podle SCN
SELECT r.* FROM test.tabulka AS OF scn 4527718937 r;
 
-- podle času
SELECT * FROM test.tabulka AS OF TIMESTAMP TO_TIMESTAMP('30.5.2023 11:17:42', 'DD.MM.YYYY HH24:MI:SS') r;

další informace (např. konverze scn ↔ timestamp, jak vrátit droplý trigger, atp.):
viz Flashback Query Example (Wayback Machine)

ORA-27369 - job of type executable failed with exit code

základní popis ORA-27369 (Wayback Machine)
někdy Oracle neuvádí kód ale jen text chyby. Ten se ale netýká databáze a jde jen o standardizovaný překlad návratového kódu
- viz super článek o chybě

Přehled trace logů a jejich umístění

SELECT s.sid, s.serial#, s.audsid, s.username, s.osuser, s.client_identifier, s.sql_trace, s.action, p.spid, p.tracefile
  FROM v$session s, v$process p
 WHERE s.paddr = p.addr
   AND s.username IS NOT NULL;

Rychlá kontrola zda kolekce obsahuje prvek (member of)

např. pro itool.string_pole_typ, itool.num_pole_typ a další table of typy…
použití jak v SQL tak PL/SQL

př.:

SELECT CASE
         WHEN 123 MEMBER OF itool.num_pole_typ(1, 2, 3, 123) THEN
          'ano'
         ELSE
          'ne'
       END AS je_clenem
  FROM dual;

ve sloupci "JE_CLENEM" bude 'ano'

DECLARE 
  TYPE t_tab IS TABLE OF VARCHAR2(255);
  lTab t_tab := t_tab();
BEGIN
  lTab.extend;
  lTab(lTab.LAST) := 'ahoj';
 
  IF 'ahoj' member OF lTab
  THEN
    :je_v_tab := 'ano';
  ELSE
    :je_v_tab := 'ne';
  END IF;
END;

v proměnné "je_v_tab" bude 'ano'

Kdo drží globální temporary table?

pokud tabulku někdo používá, nelze ji smazat

takhle se dozvíme, kdo ji drží:

-- dotaz na sessions držící vybranou globální temp tabulku GTT_OWNER.GTT_NAME
SELECT *
  FROM gv$lock
 WHERE id1 = (
        SELECT object_id
          FROM dba_objects
         WHERE owner = UPPER('GTT_OWNER')
           AND object_name = UPPER('GTT_NAME')
       );

Implicitní konverze

pokud to jde - vyhněte se jí

problémy:
- v SQL její použití způsobí, že se nepoužije index
- při skládání větších textů (CLOB) dojde k chybě ORA-06502 (číselná nebo hodnotová chyba)

ORA-06502: Číselná nebo hodnotová chyba

pokud skládáme do CLOBu text a číslo nebo datum, dojde při překročení VARCHAR2 limitu k této chybě
jde o záludnou chybu - projevuje se jen při větším množství textu a z kódu to není vůbec viditelné
řešení: používejte to_char() atp.
v diskuzích jsem nenašel žádné stoprocentně přesvědčivé vysvětlení. Dokonce ani není jasné, zda jde o chybu nebo vlastnost
- https://community.oracle.com/tech/developers/discussion/3913931/working-with-clob
- https://community.oracle.com/tech/developers/discussion/3915593/clob-data-type-is-not-accepting-data-more-than-33328-bytes

Toto funguje:

DECLARE
  x clob;
BEGIN
  FOR i IN 1 .. 8000
  LOOP
    x := x || i;
  END LOOP;
  dbms_output.put_line( LENGTH( x ) );
END;

Toto již nefunguje:

DECLARE
  x clob;
BEGIN
  FOR i IN 1 .. 9000 -- vede na více než 32767 znaků
  LOOP
    x := x || i;
  END LOOP;
  dbms_output.put_line( LENGTH( x ) );
END;

A toto opět funguje:

DECLARE
  x clob;
BEGIN
  FOR i IN 1 .. 9000
  LOOP
    x := x || TO_CHAR(i); -- explicitní konverze je ok
  END LOOP;
  dbms_output.put_line( LENGTH( x ) );
END;

Diskuze

Celé jméno:

E-mail:

Vložte svůj komentář. Používání wiki syntaxe povoleno:

Vyplňte, prosím, všechna písmena v poli, abyste dokázali, že nejste robot.

Ponechte prosím toto pole prázdné:

Posílat upozornění na nové komentáře