Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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:

<code sql>

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;
</code>

Abfragen:
<code sql>
select name ,Netto_preis ,brutto_preis  from materials ;
name      Netto_preis   brutto_preis  
-------- ---------     ------
...
Tankdeckel	100	119
..
</code>

Anpassen:
<code sql>

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

</code>

=== 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:
<code sql>
create or replace function getBrutto(p_input number)
return number
DETERMINISTIC

is
begin
return p_input  *  1.19;
end getBrutto;
</code>

Verwenden:
<code sql>

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

</code>


=== Securtiy Überlegungen===

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


Anlegen:
<code sql>
create or replace function whoami(p_input varchar2)
return varchar2
DETERMINISTIC
is
begin
return user;
end whoami;
</code>


<code sql>
alter table materials  add(  my_username varchar2(32767) GENERATED ALWAYS AS ( whoami('A') ) VIRTUAL );
</code>

als User GPI:
<code sql>
select  my_username from gpi.materials;
MY_USERNAME
-----------
GPI
</code>


als User SYS:
<code sql>
select  my_username from gpi.materials;
MY_USERNAME
-----------
SYS

</code>


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:
<code sql>
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;
</code>


=> 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:
<code sql>
GPI@GPI-saturn>select  my_username from gpi.materials;

MY_USERNAME
------------------------------------------------------------------------
ORA-00942: table or view does not exist
</code>

als SYS:
<code sql>
MY_USERNAME
------------------------------------------------------------------------
ORA-00942: table or view does not exist

</code>

hmm....

===DML===

Einfügen funktioniert natürlich nicht 8-):
<code sql>
--
insert into materials(name ,Netto_preis,brutto_preis   ) values  ('Tankdeckel',100,200)
                                        *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
--

</code>


----
==== 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 ====


Web:
  * https://mikesmithers.wordpress.com/2015/06/23/whats-in-a-name-user_tab_cols-and-user_tab_columns-are-different/