TRUNCATE alleine und innerhalb einer Prozedur

[SonnyHH]

CREATE OR REPLACE PROCEDURE pr_zahlungsabgleich IS
BEGIN
TRUNCATE TABLE SYSBEZAHLT;
END;

Kann mir jemand erklären, warum ich eine Fehlermeldung erhalte, wenn ich den TRUNCATE Befehl innerhalb einer Prozedur benutzen möchte? Führe ich den Befehl alleine aus, funktioniert alles einwandfrei.

• Fehler(5,11): PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue.

Danke im voraus.

 

[akretschmer]

Oracle kann keine DDL-Befehle wie TRUNCATE innerhalb einer Transaktion, und eine Procedure läuft komplett als eine Transaktion. Wenn Du das willst, brauchst eine bessere DB.

 

[akretschmer]

test=# create table hh(i int);
CREATE TABLE
test=*# insert into hh select * from generate_series(1, 10) s;
INSERT 0 10
test=*# select * from hh;
 i  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 Zeilen)

test=*# create or replace function truncate_hh() returns bool as $$begin truncate hh; return true; end; $$language plpgsql;
CREATE FUNCTION
test=*# commit;
COMMIT

test=*# select * from truncate_hh();
 truncate_hh
-------------
 t
(1 Zeile)

test=*# select * from hh;
 i
---
(0 Zeilen)

test=*#

 

[SonnyHH]

Dankefür die Erklärung. Wenn man aus dem MS SQL Server Umfeld kommt und die Prozeduren dort gewohnt ist, kommt man sich bei Oracle wie der "Ochs vor'm Berg" vor...
Ich denke, das meinst Du mit einer besseren DB. ;-)

 

[akretschmer]

Ich denke, das meinst Du mit einer besseren DB. ;-)

Ähm - nein.

 

[unficyp]

lol bessere db ...so ein blödsinn...

execute immediate 'truncate table x';

 

[akretschmer]

ahh, das geht in Oracle so? Aber generell kann man kein DDL innerhalb einer Transaktion machen, oder? Also Create Table oder Alter table. Oder bin ich da falsch informiert?

 

[unficyp]

nein, ddl ist ein impliziter commit. mit autonomous transactions könnts funktionieren.
aber das war ja nicht die frage

 

[akretschmer]

nein, ddl ist ein impliziter commit.

Eben (bei Oracle, MySQL, ...), daher dachte ich das wäre auch bei TRUNCATE so. In PG geht (fast) alles in einer TX.

 

[drdimitri]

ahh, das geht in Oracle so? Aber generell kann man kein DDL innerhalb einer Transaktion machen, oder? Also Create Table oder Alter table. Oder bin ich da falsch informiert?

Du bist in Oracle immer in einer Transaktion. Es gibt kein Begin TX .. End TX
Was nicht möglich ist:
Insert into T ...
Alter table t drop column ...
rollback;

Weder der Insert noch der Alter Table sind durch das Rollback rückgängig gemacht.

Ich wüsste auch nicht, dass das bei PG anders ist.
Der Grund warum man in einer Prozedur kein statisches DDL möglich ist, ist der, dass man sich damit praktisch während der Ausführung die Prozedur invalid machen kann.
Z.B. man dropt eine Spalte /Tabelle, die aber in einem anderen statischen SQL benötigt wird, das würde die Prozedur während der Ausführung invalidieren, man sägt quasi den Ast ab auf dem man sitzt.
Da auch Truncate DDL ist (Rücksetzen der High Water Mark in der Tabelle, kein Schreiben von UNDO/REDO für diese Aktion) fällt es ebenfalls unter diese Beschränkung.

Mit Transaktionen hat das rein gar nichts zu tun.

 

[akretschmer]

Weder der Insert noch der Alter Table sind durch das Rollback rückgängig gemacht.

Ich wüsste auch nicht, dass das bei PG anders ist.

Schauen wir doch mal ...

test=# create table demo (id int, alte_spalte int);
CREATE TABLE
test=*# insert into demo values (1,1);
INSERT 0 1
test=*# commit;
COMMIT
test=# begin;
BEGIN
test=*# select * from demo;
 id | alte_spalte
----+-------------
  1 |  1
(1 Zeile)

test=*# insert into demo values (2,2);
INSERT 0 1
test=*# alter table demo drop column alte_spalte;
ALTER TABLE
test=*# alter table demo add column neue_spalte timestamp;
ALTER TABLE
test=*# insert into demo values (3, now());
INSERT 0 1
test=*# select * from demo;
 id |  neue_spalte   
----+----------------------------
  1 |
  2 |
  3 | 2016-12-22 13:39:58.577093
(3 Zeilen)

test=*# rollback;
ROLLBACK
test=# select * from demo;
 id | alte_spalte
----+-------------
  1 |  1
(1 Zeile)

test=*#

Das ist auch ganz praktisch so - wenn z.B. eine Applikation ein Update seiner Tabellen macht. Das geht halt in einer Transaktion, entweder alles oder nix.

 

[drdimitri]

Hmm ok.
Was passiert denn, wenn eine andere Session die Tabelle lesen oder per DML Daten ändern will während die Tabelle geändert wird?

 

[akretschmer]

ALTER TABLE setzt einen exclusive lock.

 

[drdimitri]

Auch für lesende Zugriffe?

 

[akretschmer]

Ja.