Auswahl  

 

Oracle
PL/SQL
RDBMS 11.2:RDBMS 12.1:RDBMS 12.2:RDBMS 18.1:10.x
Dynamisches SQL, EXECUTE IMMEDIATE
12.07.18
MP
30.07.18
MP

Body

In Unix gibt es eine schöne Funktion mit Namen eval. Mit der kann man dynamische Befehle ausführen. So etwas wäre doch auch unter Oracle ganz praktisch. Es gibt ja auch schon drei dynamische Konzepte:

  • EXECUTE IMMEDIATE
  • REF Cursor
  • DBMS_SQL und DBMS_SYS_SQL

Wir lösen das Ganze mit EXECUTE IMMEDIATE und packen eine kleine Funktion darum.

CREATE OR REPLACE FUNCTION my_eval (cmd IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Damit ist die Funktion eigenständig und beeinflusst niemanden :-)
v_cmd VARCHAR2(32000);
BEGIN
v_cmd:=rtrim(cmd,';'); -- Semmikolon ggf.herausfiltern
EXECUTE IMMEDIATE v_cmd; -- Befehl ausführen
RETURN v_cmd||'; --OK'; -- Befehl erfolgreich ausgeführt
EXCEPTION WHEN OTHERS THEN
 RETURN v_cmd||' --'||sqlerrm;
END;
/

 

Anwendungsbeispiele:

REM Löschen aller Tabellen des Benutzers SCOTT die mit Tab beginnen


SELECT my_eval('drop table scott.'||table_name||';')
FROM all_tables
WHERE table_name like 'TAB%';

oder alle Tabellen im gleichen Tablespace reorgansieren


SELECT my_eval('alter table scott.'||table_name||' move;')
FROM dba_tables
WHERE owner='SCOTT'

 Alle Indizes neu kompileren, die ungültig sind:


SELECT my_eval('ALTER INDEX '||owner||'."'||index_name||'" REBUILD ONLINE;')
FROM dba_indexes WHERE status<>'VALID' and PARTITIONED='NO';

Zu Risikien und Nebenwirkungen:

Dynamisches PL/SQL hat auch diverse Nachteile:

1. Es ist langsamer als normales PL/SQL

2. Es ist anfällig für SQL Injection.  (Aber das wird in einem anderen Tipp besprochen :-) )

Weitere Tipps erhalten Sie in einem unserer schönen PL/SQL Kurse (PL/SQL I, PL/SQL II, PL/SQL Kompakt, PL/SQL Tuning, PL/SQL Packages)

Möchten Sie die gleiche Aufgabe auf viele Objekte anwenden? Oder sich Befehle selbst dynamisch zusammensetzen? Dann sind Sie bei diesem Tipp richtig.