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 :)

• 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

• 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;

• 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;

• 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í ', '

• 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';

• 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

• 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

• 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';

• 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)

• 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);

  

• 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;

  

• 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:

• 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;

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;

• 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;

@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!

• 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:
  1. 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;

     

  2. 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)

• 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ě

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;

• 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'

• 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')
         );

• 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)

• 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;