PLSQL - dbms_sql

[ErikRo]

Ik kwam iets tegen over deze package. Het voorbeeld was helaas erg kort en best wel ingewikkeld. Eerst alles aan elkaar plakken, dan parsen en dan executen. Op Oracle-internet kwam ik alleen engelse teksten tegen die ik niet begreep.
Maar op zich zag het veelbelovend uit wat eruit begreep. Nu komt mijn vraag. Voor ik een boek koop en mij hier helemaal in verdiepen en tot de ontdekking ik deze mogelijkheden de eerste 10 jaar niet nodig heb.

Zou iemand me even in vier zinnen kunnen uitleggen, wat je nou wel en wat je nou niet met dit package kan doen (in het nederlands graag). Wat is nou zijn specififieke waarde. Het lijkt me dat variabelen bijna hetzelfde bereiken. Een link naar een nederlandse pagina mag natuurlijk ook.
ps als deze vragen hier niet thuishoren brand me dan maar af

[whoami]

Over welk pakket heb je het?
PL/SQL is voor zover ik weet een uitbreiding op SQL door Oracle. Ipv gewoon data-retrieval en manipulatie (en creatie van datastructuur), zijn er procedure taalelementen aan toegevoegd.

dbms_sql: Dat is iets dat jij waarschijnlijk zelf verzonnen hebt? Een DBMS is een Database Management System zoals daar zijn Oracle, SQL Server, DB2, ... SQL is een taal waarmee je databases kunt creeëren, data uit die databank halen en data in die databank wijzigen etc...

[ErikRo]

Ik heb het inderdaad over de uitbreiding en je verhaal klopt dan ook helemaal. Op het zelf verzinnen van de term "dmbs_sql" na dan, want zo slim ben ik nou ook weer niet.
Ik het voor de zekerdheid nog even nagezocht, maar het staat er echt. Het zou te maken hebben met "dynamic sql".

[JaQ]

whoami >> bijna goed, (bijna) alle packages die oracle meeleverd voor besturing van zijn eigen database (de rdbms dus) beginnen met dbms. Feitenlijk is het ook nog eens onwaar wat je zegt. sql is Structured Query Language. (en zoeken is niet hetzelfde als manipuleren zoals je misschien wel weet ;-) ) Verder is oracle een RDBMS, een zogenaamde Relational Database Management System. Maar goed, we zijn hier niet om elkaar af te zeiken. toch ;-)

Erikro>> dbms_sql is een package die je kan gebruiken om dynamisch een stuk pl/sql op te bouwen. Tegenwoordig is zogenaamd "native" meer in zwang. Zelf gebruik ik altijd execute immediate

Wat je doet is (net zoals bijvoorbeeld in perl) een stuk code in een string zetten en deze vervolgens richting de rdbms sturen. Een commit (of rollback) is niet meer nodig, aangezien je het hele i.d. van "sessies" en transacties ontwijkt. (vrij grof middel dus)

Voordeel: super snel (geen onnodige communicatie tussen client en server)
nadeel: geen feedback. Alle data manipulatie die je uitvoerd zal je zelf moeten controleren (bij voorkeur dus in het stuk pl/sql dat je gebruikt om die data manipulatie uit te voeren).

Je kan inderdaad bitter weinig info vinden op technet.oracle.com over dit package. Codevoorbeelden kan je ook beter opzoeken op metalink.oracle.com Ik weet alleen niet of je daar in komt zonder support nummer. (maar die heb ik wel :-) )

Let even goed op. Het is met dbms_sql NIET mogelijk om ddl te doen (dus geen tabellen aanmaken / veranderen). Grappig genoeg is het wel mogelijk indexes aan te maken. (vraag me niet waarom. Er zijn vast DBA's die je dat wel uit kunnen leggen). Verder is het erg grappig om dit soort trucjes in triggers te gebruiken, maar dan moet je wel drie dubbel en dwars controleren dat je geen foutjes maakt (anders kan je binnen no time je db om zeep helpen).

klein voorbeeldje:

declare
lv_code varchar2(200);
begin
lv_code := 'insert into bla (bla, bla_2, bla_3) values (1,2,3);';
execute immediate (lv_code);
end;
/

[ErikRo]

DrFrankenstoner >>> bedankt veel duidelijker had je uitleg vast niet gekund.

Mag ik je nog één vraag stellen?Jij weet er zo te zien erg veel van.
Dit was een voorbeeld met update. Is een "select * from xx" ook mogelijk?

Ik dacht altijd dat onmogelijk een SQL statement tussen BEGIN en END; te laten uitvoeren.
Als het via deze weg wel kan zou dat heel mooi zijn. Dan kan ik misschien eindelijk de inhoud van een array selecteren/uitvragen en naar het scherm sturen als een tabel ipv dbms.output.

Maar ik hou voor de zekerheid alvast rekening met een hoop ingewikkeld programmeerwerk mbv dbms_output en concat

[ronaldmathies]

[oeptie]

negeer dit bericht maar kijk naar de volgende.

[ Voor 0% gewijzigd door ronaldmathies op 10-08-2002 16:32 . Reden: berichtje teveel ]

[ronaldmathies]

Een sql statement kan prima tussen begin en end. Zowel in een anounymous block als in een procedure/function/package.

Alleen maak je dan of gebruik van cursors als het om een resultaat gaat waarbij meer dan één regel als resultaat komt of je maakt gebruik van de into functie.

En bij Oracle 8 kan je ook een temporaraly table gebruiken om de resultaten in te stoppen.

Dit voorbeeld is corrupt gemaakt, dit omdat ik deze code voor een commerciele doeleind gemaakt heb.

code:

PACKAGE BODY ### IS

-- De PL/SQL tabel.
type ###_SCHEMASTYPE
 IS TABLE OF SCHEMAS_###
 INDEX BY BINARY_INTEGER;


-- Creeer een instance van de PL/SQL tabel.
###_SCHEMAS ###_SCHEMASTYPE;
PROCEDURE SP_CONTROLE_### (P_### IN ###.GRB_CODE%TYPE,
                                         P_NUM_FOUND OUT NUMBER) IS
V_CURSOR_HANDLE INTEGER;  -- De cursor handler.
V_AANTAL_REGELS INTEGER;  -- Aantal regels wat er per query is gevonden.
V_IGNORE INTEGER;         -- Een loze variabele.
V_TELLER INTEGER;         -- Totaal aantal tabellen die de opgegeven #### rekening bevatten.
V_INDEX NUMBER;           -- De index voor de PL/SQL tabel.
CURSOR C_CONSTRAINTS IS
-- De query haalt alle constraints op die wijzen naar de tabel ####.
SELECT * FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE R_CONSTRAINT_NAME = (
        SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = '####' and CONSTRAINT_TYPE = 'P'))
  ORDER BY OWNER, CONSTRAINT_NAME;
BEGIN
 -- Schoon de PL/SQL tabel.
 ###_SCHEMAS.DELETE;
 V_CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
 FOR R_CONSTRAINTS IN C_CONSTRAINTS LOOP
    V_AANTAL_REGELS := 0;
    -- Doe een query naar de database om te kijken of de tabel in een bepaald schema de opgegeven ### bevat.
    DBMS_SQL.PARSE(V_CURSOR_HANDLE, 'SELECT ' || R_CONSTRAINTS.COLUMN_NAME || ' FROM ' || R_CONSTRAINTS.OWNER || '.' || R_CONSTRAINTS.TABLE_NAME ||
                                     ' WHERE ' || R_CONSTRAINTS.COLUMN_NAME || ' = :###',DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE (V_CURSOR_HANDLE, ':####', P_###);
    V_IGNORE := DBMS_SQL.EXECUTE(V_CURSOR_HANDLE);
    -- Fetch de regels. (Haal de regels niet op alleen het aantal gevonden regels.
    V_AANTAL_REGELS := DBMS_SQL.FETCH_ROWS (V_CURSOR_HANDLE);
    IF V_AANTAL_REGELS > 0 THEN
        -- Bewaar de laatste schema en tabel die in het zoeken is gebruikt.
        V_INDEX:=###_SCHEMAS.COUNT+1;
        ###_SCHEMAS(V_INDEX).OWNER := R_CONSTRAINTS.OWNER;
        ###_SCHEMAS(V_INDEX).TABLE_NAME := R_CONSTRAINTS.TABLE_NAME;
    END IF;
 END LOOP;
 DBMS_SQL.CLOSE_CURSOR (V_CURSOR_HANDLE);
 P_NUM_FOUND := V_INDEX;
 EXCEPTION
 WHEN OTHERS
 THEN
    Raise_application_error( -20000, 'Fout bij het controleren van de grootboekrekeningen ### ' || Sqlerrm(Sqlcode));
END SP_CONTROLE_###;

PROCEDURE GET_GRB_SCHEMA
    (P_INDEX IN NUMBER,
    PA_SCHEMA_### OUT SCHEMAS_###)
IS
    LV_SCHEMAS_###  SCHEMAS_###;
   BEGIN
    PA_SCHEMA_### :=###_SCHEMAS(P_INDEX);
    RETURN;
END GET_###_SCHEMA;
END;

[JaQ]

ErikRo>> Volgens mij is het vrij onhandig om de resultaten naar scherm te gaan schrijven. Je moet dan goed opletten met je buffers (want je zal dan toch dbms_output moeten gebruiken. Een tijdelijke tabel (zoals ronaldmathies voorsteld) is handiger. Naar scherm schrijven is dus een beetje een "debug" truc voor dit dynamische plsql. Als je je "normale" sql dymanisch wilt maken, denk ik dat je beter met & kan werken.

Als je dan een script maakt (en dat dus aanroept vanuit een applicatie, of de sql prompt) werkt dat als volgt:

--------------------
prompt [1] tabelnaam [2] kolomnaam

define p_table = '&1'
define p_column = '&2'

select &2 from &1
/

--------------------

Op deze manier kan je dus ook allerlij "dynamische" where-statements maken.
Een parameter tabel kan je dan dus helpen

voorbeeld
---------------
column waarde new value p_arameter noprint

select waarde
from parameters
/

select bla
from zoektabel
where waar = '&p_arameter'
/

---------------------

uiteraard zijn de ' alleen verplicht bij varchar's

Zo zie je dat je niet altijd pl/sql nodig hebt om iets dynamisch te maken. Je moet dus een afweging maken of je echt iets intelligents maakt, of enkel een wat slimmere query wilt hebben. Als je gaat zoeken op bind-variables op technet.oracle.com kan je hier veel meer info over vinden. (dat werkt dan met :bind_var iets dat geheugentechnisch iets beter uitpakt dan &)

Zoals je ziet dat er meer wegen zijn die naar rome leiden, maar eenvoud siert heb ik wel ontdekt in mijn werk. Vaak zie je allerlij ingewikkelde queries die m.b.v. moeilijke procedures en functies een parameter zetten of een query bouwen, terwijl een stukje "normaal" sql, uitgebreid met & al wonderen kan doen.

Success. (ow jah, en ik weet ook niet alles.... dus correct me if i'm wrong anyone...)

[whoami]

DrFrankenstoner schreef op 10 augustus 2002 @ 14:45:
sql is Structured Query Language. (en zoeken is niet hetzelfde als manipuleren zoals je misschien wel weet ;-) )

Dat weet ik.
Ik had toch ook niet gezegd dat zoeken hetzelfde is als manipuleren.
Ik zei dat SQL een taal is waarmee je datastructuren kunt creeëren en wijzigen (tabellen maken, tabellen wijzigen, indexen leggen, ...), waarmee je data in de databank kunt manipuleren (data inserten, data wijzigen, data verwijderen, de INSERT, UPDATE, DELETE statements dus (De DML aspecten van de taal dus)), en waarmee je data kunt opvragen (SELECT (DRL))

Verder is oracle een RDBMS, een zogenaamde Relational Database Management System.

De afkortingen en waarvoor ze staan ken ik wel.

Maar goed, we zijn hier niet om elkaar af te zeiken. toch ;-)

Goed dat je het weet.