Dynamische Ausführung von Befehlen

04.
Oktober
2010
Veröffentlicht von: Marco Patzwahl

Seit Jahren stellt sich mir immer wieder das gleiche Problem:
Wie kann ich eine Aktion x-Mal in der Datenbank ausführen?
z.B. Wie kann ich 100 Indizes in einen anderen Tablespace verschieben?
Bisher war meine Lösung immer:

Seit Jahren stellt sich mir immer wieder das gleiche Problem:
Wie kann ich eine Aktion x-Mal in der Datenbank ausführen?

z.B. Wie kann ich 100 Indizes in einen anderen Tablespace verschieben?
Bisher war meine Lösung immer:

SPOOL c:\temp\move_index.sql
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE indx_tbs;'
FROM dba_indexes
WHERE index_name like 'PK_%''';  /*da sind ein paar Hochkommata zu viel (evtl. den Underscore noch mit Escape-Zeichen versehen?)*/
SPOOL OFF
@c:\temp\move_index.sql

Dann bekommt man ein paar Fehler durch die Formatierung ausgegeben (kann man durch geschickte SET Kommandos ausblenden), trotzdem funktioniert es.

Was macht man jedoch, wenn man kein Laufwerk hat, wo man die Spool Datei ablegen darf/kann? Oder wenn kein SQL*Plus zur Verfügung steht? Hier hilft ein kleines simples PL/SQL Skript, dass die Funktionalität nachbaut. Ich habe es eval genannt als Hommage an das Unix Kommando eval(uate).

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

Warnung: Die Funktion führt den Befehl sofort aus! Da gibt es keine Rückfrage mehr, wie SIND SIE SICHER?? :-)
Eigentlich dürfen/sollen Funktionen solche Sachen gar nicht machen :-), aber wir sind ja Advanced User :-)

Anwendungsbeispiele:

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

SELECT my_eval('drop table scott."'||table_name||'"') /* Hinweis der Tabellenname ist in doppelte Hochkommas gesetzt !*/
FROM dba_tables
WHERE table_name like 'TAB%';

Alle Tabellen im gleichen Tablespace reorganisieren:

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

Alle Indizes neu kompilieren, die ungültig sind:

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

Wie immer gilt:
Wollen Sie mehr aus Ihrer Datenbank machen? Kommen Sie in eine unserer Opens internal link in current windowSchulungen und/oder holen Sie sich einen unserer erfahrenen Opens internal link in current windowIT-Consultants ins Haus.

PL/SQL

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.