Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:oracle_virtual_column

Oracle - Berechnete Tabellenspalten verwenden - Virtual Columns mit der GENERATED ALWAYS Spalten Definition - Unterschied von USER_TAB_COLS und USER_TAB_COLUMNS

Getestet mit 19c

Schon länger besteht die Möglichkeit eine Logische/berechnete/virtuelle Tabellenspalte mit dem Schlüsselwort „GENERATED ALWAYS“ anzulegen.

Aber für was ist das gut?

Solche Spalten können das Reporting vereinfachen, z.B. sind in der Preistabelle alle Preise netto hinterlegt, ein einer virtuellen Spalte ist der Wert inkl. Umsatzsteuer hinterlegt, ändert sich diese muss eine virtuelle Spalte angepasst werden und schon werden alle Werte korrekt dargestellt.

Beispiel:

CREATE TABLE materials (
   -- PK Spalte
  id INTEGER GENERATED ALWAYS AS IDENTITY  (START WITH 1 INCREMENT BY 1 )
  -- Daten
, name varchar2(256)
, Netto_preis NUMBER(9,2)
  -- berechnete Spalte
,  brutto_preis  NUMBER(9,2) GENERATED ALWAYS AS ( Netto_preis  * 1.19 ) VIRTUAL
)
;
 
DESC materials
 
 Name                                                                    NULL?    TYPE
 ----------------------------------------------------------------------- -------- --------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                             VARCHAR2(256 CHAR)
 NETTO_PREIS                                                                      NUMBER(9,2)
 BRUTTO_PREISE                                                                    NUMBER(9,2)
 
-- test daten
INSERT INTO materials(name ,Netto_preis ) VALUES  ('Tankdeckel',100);
INSERT INTO materials(name ,Netto_preis ) VALUES  ('Tank',1000);
INSERT INTO materials(name ,Netto_preis ) VALUES  ('Ölwanne',200);
INSERT INTO materials(name ,Netto_preis ) VALUES  ('Zündkerze',100);
commit;

Abfragen:

SELECT name ,Netto_preis ,brutto_preis  FROM materials ;
name      Netto_preis   brutto_preis  
-------- ---------     ------
...
Tankdeckel	100	119
..

Anpassen:

ALTER TABLE materials  MODIFY (  brutto_preis  NUMBER(9,2) GENERATED ALWAYS AS ( Netto_preis  * 1.10 ) VIRTUAL );
 
TABLE altered.
 
SELECT name ,Netto_preis ,brutto_preis  FROM materials ;
name      Netto_preis   brutto_preis  
-------- ---------     ------
...
Tankdeckel	100	110

Funktion hinterlegen

! Muss DETERMINISTIC sein !

Natürlich kann auch eine Funktion zur Berechnung übergeben werden, nun gibt es nur noch eine Stelle um den Wert anzupassen.

Um einen „ORA-30553: The function is not deterministic“ auf das Pragma DETERMINISTIC achten!

Anlegen:

CREATE OR REPLACE FUNCTION getBrutto(p_input NUMBER)
RETURN NUMBER
DETERMINISTIC
 
IS
BEGIN
RETURN p_input  *  1.19;
END getBrutto;

Verwenden:

ALTER TABLE materials  MODIFY (  brutto_preis  NUMBER(9,2) GENERATED ALWAYS AS (getBrutto ( Netto_preis)) VIRTUAL );
TABLE altered.
 
SELECT name ,Netto_preis ,brutto_preis  FROM materials ;
name      Netto_preis   brutto_preis  
-------- ---------     ------
 
Tankdeckel	100	119

Securtiy Überlegungen

Die Frage ist jetzt in welchen Scope wird die Funktion aufgerufen, als Eigentümer oder als Aufrufender?

Anlegen:

CREATE OR REPLACE FUNCTION whoami(p_input varchar2)
RETURN varchar2
DETERMINISTIC
IS
BEGIN
RETURN USER;
END whoami;
ALTER TABLE materials  ADD(  my_username varchar2(32767) GENERATED ALWAYS AS ( whoami('A') ) VIRTUAL );

als User GPI:

SELECT  my_username FROM gpi.materials;
MY_USERNAME
-----------
GPI

als User SYS:

SELECT  my_username FROM gpi.materials;
MY_USERNAME
-----------
SYS

Das heißt das intern die Funktion mit „Caller Rights“ läuft, sehr gut, damit können wir den nächsten Test durchführen ob wir nun diese höheren Rechte ausnützen können!

Kann das klappen?

Funktion mit Osterei:

CREATE OR REPLACE FUNCTION whoami(p_input varchar2)
RETURN varchar2
AUTHID CURRENT_USER
DETERMINISTIC
IS
pragma autonomous_transaction;
v_return varchar2(2000);
BEGIN
 
SELECT USER INTO v_return  FROM dual;
 
BEGIN
  EXECUTE IMMEDIATE 'grant execute on sys.dbms_sys_sql to gpi';
  v_return :='GPI is now DBA';
exception
 WHEN others THEN 
   v_return :=SQLERRM; 
END;
 
RETURN v_return ;
 
END whoami;

⇒ Funktioniert nicht ⇒ ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

D.h. so einfach klappt das nicht, wir brauchen einen Aufruf innerhalb der Funktion der nicht als DDL erkannt wird! Wie zum Beispiel ein Rest Call oder so etwas ähnlich ansich harmloses.

oder gar nur ein einfaches „pragma autonomous_transaction“?

Nun erhält man als sys ein „ORA-00942: table or view does not exist“ das deutet daraufhin das es doch mitAUTHID value is DEFINER läuft.

Und dann müssen wir den DBA nur noch zum Select überreden …..

als GPI User:

GPI@GPI-saturn>SELECT  my_username FROM gpi.materials;
 
MY_USERNAME
------------------------------------------------------------------------
ORA-00942: TABLE OR VIEW does NOT exist

als SYS:

MY_USERNAME
------------------------------------------------------------------------
ORA-00942: TABLE OR VIEW does NOT exist

hmm….

DML

Einfügen funktioniert natürlich nicht 8-):

--
INSERT INTO materials(name ,Netto_preis,brutto_preis   ) VALUES  ('Tankdeckel',100,200)
                                        *
ERROR at line 1:
ORA-54013: INSERT operation disallowed ON virtual COLUMNS
--

Virtuelle Spalten erkennen - Unterschied USER_TAB_COLS und USER_TAB_COLUMNS

Im ersten Schritt habe ich mit die Tabelle in der View USER_TAB_COLUMNS und konnte keine Unterschied erkennen ob eine Spalte nun virtuell oder nicht ist.

Die Information steht in der View USER_TAB_COLS in der Spalte „virtual_column“ [Y|N], die bis auf wenige Spalte identisch zu USER_TAB_COLUMNS ist.

Auch etwas verwirrend ….


Quellen

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
"Autor: Gunther Pipperr"
dba/oracle_virtual_column.txt · Zuletzt geändert: 2022/09/23 14:44 von gpipperr