štvrtok, 28 november 2013 08:15 Written by 2066 times
Rate this item
(2 votes)

PL/SQL - Výpis DDL príkazu objektu v schéme

Mnohí programátori v PL/SQL a SQL uz prišli k stavu, ked nemali niekedy k dispozícii grafického klienta, ktorý programátorom rýchlo, efektívne a hlavne pohodlne zobrazil všetky informácie o objekte (napr. tabuľke, balíčku/package, atď) na obrazovku. Samozrejme bez takéhoto klienta si máloktorý programátor v dnešnej dobe vie prácu redstaviť, ale predsa vždy sa môže stať,
že aplikácia nie je v danom čase k dispozícii alebo jednoducho to programátor len chce vedieť, aby si rozšíril obzor pri práci s databázami.

Poďme na to. Mnohí hlavne začiatočníci by si mali naštudovať pri programovaní v PL/SQL aj množstvo užitočných balíkov (packages), ktorými sa dajú robiť užitočné veci ako napríklad, prečítať a generovať/ celé komplexné DDL príkazy jednotlivých objektov, vygenerovať "explain plan" pre SQL príkazy, spustiť tuningovanie SQL príkazov, pracovať so súbormi, s FTP, SMTP protokolmi atď, atď.

Dnes som si vybral možno si poviete jednoduchú vec. Áno, je to jednoduchá vec, stačí napísať SQL príkaz:

SELECT DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE','MENO_TABULKY','MENO_SCHEMY'), 4000, 1) FROM DUAL;

Na obrazovke máme vypísaný kompletný DDL príkaz na vytvorenie tabuľky 'MENO_TABULKY' nachádzajúcej sa v schéme 'MENO_SCHEMY'. Analogicky po vyplnení parametrov napr. pre index sa v schéme zobrazí DDL kód pre vytvorenie indexu v danej schéme.  Problém avšak nastane, keď požadujeme vygenerovanie DDL príkazu, ktorý ma viac ako 4000B. Ako vieme, v DML - "SELECT" príkaze hodnota stĺpca pre typ VARCHAR2 nesmie presiahnúť hodnotu 4000B alebo znakov (podľa nastavenia semantiky). V tomto prípade si poradíme napríklad vypísaním takéhoto DDL príkazu rovno na výstup pekne po riadkoch. Vypýtame si DDL kód z metadát schémy a budeme čítať a vypisovať jednotlivé riadky DDL príkazu (Predpokladáme, že riadok nepresiahne dĺžku 1000 znakov,  kôli funkcii DBMS_OUTPUT.PUT_LINE. Ak by presahoval, je potrebné doprogramovať ďalší cyklus na ešte podrobnejší výpis riadkov.) do výstupu.  
Predpokladám, že programátor už má základé znalosti programovania v PL/SQL. Príklad som testoval na databáze Oracle 11g R2 bežiacej na 64b WIN 2008.  

Pripojíme sa na databázu a jednoducho spustítme tento anonymný blok:

SET SERVEROUTPUT ON -- v prostredí klienta Toad
DECLARE
    CRLF CONSTANT VARCHAR2(5) := CHR(10);
    H NUMBER := NULL; 
    TH NUMBER;
    DDL_LIST sys.ku$_ddls;
    DDL CLOB;
    A INTEGER;
    B INTEGER;
    J INTEGER;    
BEGIN
    -- specify the object type: TABLEs
    H := DBMS_METADATA.OPEN('TABLE');
        
    -- specify schema MENO_SCHEMY
    DBMS_METADATA.SET_FILTER(h,'SCHEMA','MENO_SCHEMY');
    
    -- specify the table named: MENO_TABULKY 
    DBMS_METADATA.SET_FILTER(h,'NAME','MENO_TABULKY');    
        
    -- request that the metadata be transformed into creation DDL
    TH := DBMS_METADATA.ADD_TRANSFORM(H,'DDL');
    
    -- Fetch the object.
    LOOP
        --DDL_LIST := DBMS_METADATA.FETCH_CLOB(h);
        -- get the DDL list
        DDL_LIST := DBMS_METADATA.FETCH_DDL(H);
        -- finish the loop if any DDL commands were listed
        EXIT WHEN DDL_LIST IS NULL;
        -- loop the DDL
        FOR I IN DDL_LIST.FIRST..DDL_LIST.LAST 
        LOOP
            DDL := DDL_LIST(I).ddlText;            
            --DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||' - '||DDL);
            IF DBMS_LOB.GETLENGTH(DDL) > 0 THEN
                J := 0;
                LOOP
                    IF J = 0 THEN
                        A := 1;
                        B := DBMS_LOB.INSTR (DDL, CRLF, 1, 1); 
                    ELSE    
                        A := DBMS_LOB.INSTR (DDL, CRLF, 1, J);
                        EXIT WHEN A = 0;
                        B := DBMS_LOB.INSTR (DDL, CRLF, 1, J + 1);
                    END IF;
                    IF B = 0 THEN
                        B := DBMS_LOB.GETLENGTH(DDL);
                    END IF;
                    -- write DDL line to the output
                    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(DDL, B - A + 1, A));
                    J := J + 1; 
                END LOOP;
            END IF;
        END LOOP;        
    END LOOP;
    -- release resources
    DBMS_METADATA.CLOSE(H);
EXCEPTION 
    WHEN OTHERS THEN
        IF H IS NOT NULL THEN
            DBMS_METADATA.CLOSE(H);
        END IF;         
END;

Hotovo, na obrazovke máme vypísaný kód DDL príkazu požadovaného objektu, v mojom prípade tabuľky "MENO_TABULKY" v schéme "MENO_SCHEMY". Využil som balík DBMS_METADATA na získanie metadát, ďalej balík DBMS_LOB pre prácu s CLOB objektami (reťazcami, ktoré môžu presiahnuť dĺžku 32000 znakov) a balíku DBMS_OUTPUT a to funkciu na výpis dát do bufferu PUT_LINE. Verím, že tento kúsok PL/SQL kódu bude nápomocný všetkým.

S pozdravom AB

 

Last modified on štvrtok, 28 november 2013 08:35
Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 20 rokov.



  • prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal):  2 roky
  • na VŠ - jazyky Turbo pascal a Turbo C: 1rok
  • programovanie databázového ekonomického širokoškálneho software v Delphi a C++ Borland (databázy Paradox): 8 rokov v Codex s.r.o. Nitra
  • programovanie webových aplikácií na databázach MySQL a MS SQL server: 1 roky na živnosť (firmy: Hermes Nitra, Schindler v Bratislave)
  • pracoval ako DBA v Homecredit Brno a vo VÚB Bratislava ako vyvojár + optimalizácie v SQL a PL/SQL na Oracle 11g (firma Accenture)
  • programoval programy pre súkromné firmy pre HW na skenovanie povrchov nádrží pre skladovanie tekutých palív (pristroj Leica)
  • momentálne pracuje ako DBA pod Oracle 11g a ako vyvojár vnútropodnikových databázových aplikacií v PHP, SQL, C++, C# a interface v PL/SQL: 8 rokov
  • ďalej programuje s HTML, CSS, Ajax, Javascript, VBA, MS visual C++ a C# malé podporné aplikácie
  • certifikát SQL expert for Oracle 11g   
  • spolupracuje a aktívne učí pre počítačové firmy: Lapis, IVIT v Nitre