31. Juli 2013

Oracle12c: Unsichtbare Spalten: Wirklich unsichtbar?

Oracle12c Invisible Columns: Really invisible?
Heute geht es um die in Oracle12c neuen Invisible Columns. Das ist zwar ein ganz einfaches Feature, aber man kann auch ein wenig was dazu erzählen ...
create table tab_invcolumns (
  id          number(10),
  name        varchar2(200),
  kennzeichen varchar2(20) invisible 
)
/

alter table tab_invcolumns modify (kennzeichen visible)
/

alter table tab_invcolumns modify (kennzeichen invisible)
/
Bei einem CREATE TABLE kann nun also zu einer Spalte das Attribut INVISIBLE vergeben werden; mit einem ALTER TABLE ... MODIFY kann es nachträglich geändert werden. Der Effekt ist, dass die Spalte bei einem describe Kommando nicht mehr angezeigt werden.
SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
In einem SQL*Plus der Version 12c kann man jedoch mit set colinvisible ON|OFF die Anzeige der unsichtbaren Spalten in einem describe ein- oder ausschalten ...
SQL> set colinvisible on

SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
 KENNZEICHEN (INVISIBLE)                            VARCHAR2(20)
Wenn man in die Data Dictionary View USER_TAB_COLS hineinguckt, findet sich dort die Spalte HIDDEN_COLUMN, welche Auskunft über die Sichtbarkeit einer Spalte gibt.
SQL> select column_name, data_type, hidden_column 
  2  from user_tab_cols where table_name='TAB_INVCOLUMNS' ;

COLUMN_NAME                    DATA_TYPE                      HID
------------------------------ ------------------------------ ---
ID                             NUMBER                         NO
NAME                           VARCHAR2                       NO
KENNZEICHEN                    VARCHAR2                       YES
Für Anwendungen und Entwickler werden unsichtbare Spalten dann hochinteressant, wenn man das Datenmodell erweitern möchte und ...
  • ... in der Anwendung SQL-Abfragen mit SELECT * FROM ... verwendet wurden, oder ...
  • ... SQL INSERT-Anweisungen ohne Spaltenliste (INSERT INTO {TABLE} VALUES (...)) programmiert wurden.
Wenn die Spaltenliste in einer SQL INSERT Anweisung fehlt (was eigentlich kein schöner Stil ist, aber dennoch immer wieder vorkommt), müssen normalerweise alle Tabellenspalten "versorgt" werden. Ab Oracle12c kann man sagen: müssen alle sichtbaren Spalten versorgt werden. Das folgende SQL INSERT funktioniert also, obwohl für die Spalte KENNZEICHEN kein Default festgelegt wurde.
SQL> insert into tab_invcolumns values (1, 'CZARSKI');

1 Zeile wurde erstellt.
Das geht sogar noch weiter. Wird keine Spaltenliste, jedoch ein Wert für die unsichtbare Spalte vergeben, so gibt es eine Fehlermeldung. Das folgende INSERT schlägt fehl, obwohl die Tabelle drei Spalten hat - und drei Werte angegeben wurden. Die Tabelle hat eben nur zwei sichtbare Spalten.
SQL> insert into tab_invcolumns values (1, 'CZARSKI', null);
             *
FEHLER in Zeile 1:
ORA-00913: Zu viele Werte
Analog dazu zeigt ein SELECT * nur die sichtbaren Spalten an - unsichtbare Spalten werden unterdrückt. Und das ist besonders interessant, wenn eine Anwendung ein SELECT * macht, eine bestimmte Spaltenliste erwartet und die Tabelle nun erweitert werden soll.
SQL> select * from tab_invcolumns

        ID NAME
---------- ------------------------------
         1 CZARSKI
Eines sei nochmals gesagt: Unsichtbare Spalten dienen keinesfalls dazu, Inhalte zu schützen - denn sie lassen sich normal nutzen, wenn man sie explizit anspricht. Auf keinen Fall ist eine unsichtbare Spalte als Zugriffsschutz zu verstehen: Dazu gibt es andere Möglichkeiten.
SQL> insert into tab_invcolumns (id, name, kennzeichen) values (2, 'MUELLER', 'NORMAL');

1 Zeile wurde erstellt.

SQL> select id, name, kennzeichen from tab_invcolumns;

        ID NAME                           KENNZEICHEN
---------- ------------------------------ --------------------
         1 CZARSKI                        ?
         2 MUELLER                        NORMAL

2 Zeilen ausgewählt.
Für die Experten sei erwähnt, dass die unsichtbaren Spalten eigentlich gar kein neues Feature sind - strenggenommen kennt die Oracle-Datenbank sie bereits mindestens seit der Version 8. Das kann man einfach selbst nachvollziehen, indem man auf einer älteren Datenbank eine Tabelle mit einer XMLTYPE-Spalte oder einer Objekttyp-Spalte anlegt. Neu in Oracle12c ist, dass man das Spaltenattribut INVISIBLE nun selbst verwenden kann - und zwar für beliebige Tabellenspalten. Das folgende Skript zeigt eine "Invisible Columns Experience" in Oracle10g auf Basis eines Objekttypen - in Oracle9 und Oracle8 sollte es analog funktionieren ...
Verbunden mit:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create type person_t as object(
  2    name varchar2(200),
  3    vorname varchar2(200),
  4    gebdatum date
  5  )
  6  /

Typ wurde erstellt.

SQL> create table tab_personen(
  2    id number(10),
  3    person person_t
  4  )
  5  /

Tabelle wurde erstellt.

SQL> select column_name, data_type, hidden_column from user_tab_cols 
  2  where table_name='TAB_PERSONEN'

COLUMN_NAME                    DATA_TYPE       HID
------------------------------ --------------- ---
ID                             NUMBER          NO
PERSON                         PERSON_T        NO
SYS_NC00003$                   VARCHAR2        YES
SYS_NC00004$                   VARCHAR2        YES
SYS_NC00005$                   DATE            YES

SQL> select id, SYS_NC00003$, SYS_NC00004$, SYS_NC00005$ from tab_personen

        ID SYS_NC00003$    SYS_NC00004$    SYS_NC00005$
---------- --------------- --------------- -------------------------
         1 Mustermann      Max             31.07.1984 00:00:00
This blog posting is about one of the new features in Oracle12c: Invisible Columns. This is a very simple one (but definitively worth writing a blog posting about) ...
create table tab_invcolumns (
  id          number(10),
  name        varchar2(200),
  kennzeichen varchar2(20) invisible 
)
/

alter table tab_invcolumns modify (kennzeichen visible)
/

alter table tab_invcolumns modify (kennzeichen invisible)
/
So - in a CREATE TABLE statement we can use the INVISIBLE attribute for a column. We can furthermore switch a columns' visibility with ALTER TABLE ... MODIFY. An invisible column will not be contained in a describe result.
SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
In SQL*Plus Version 12c you can use the set colinvisible switch. Setting COLINVISIBLE to ON lets SQL*Plus include the invisible columns into the result of describe.
SQL> set colinvisible on

SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
 KENNZEICHEN (INVISIBLE)                            VARCHAR2(20)
The data dictionary view USER_TAB_COLS contains the HIDDEN_COLUMN attribute. This column tells us, whether a table column is visible (NO) or invisible (YES).
SQL> select column_name, data_type, hidden_column 
  2  from user_tab_cols where table_name='TAB_INVCOLUMNS' ;

COLUMN_NAME                    DATA_TYPE                      HID
------------------------------ ------------------------------ ---
ID                             NUMBER                         NO
NAME                           VARCHAR2                       NO
KENNZEICHEN                    VARCHAR2                       YES
Invisible columns are very interestin for application developers when it's about extending the applications data model (ALTER TABLE ADD COLUMN) and the application contains ...
  • ... SELECT * FROM ... queries or ...
  • ... SQL INSERT statement without a column list (INSERT INTO {TABLE} VALUES (...)).
In a SQL INSERT statement without a column list, the VALUES clause must contain a value for each table colunn. Beginning with Oracle12c we must say: "for each visible table column". So the following SQL statement executes successfully, although we did not provide a column list and the VALUES clause contains only two values.
SQL> insert into tab_invcolumns values (1, 'CZARSKI');

1 row created.
If we now add a value for the invisible column, but still don't provide a column list, the statement will fail. The table has three columns - and we provided three values. But the table only has two visible columns: So the INSERT statement indeed contains "too many values".
SQL> insert into tab_invcolumns values (1, 'CZARSKI', null);
             *
ERROR in line 1:
ORA-00913: Too many values
According to this, a SELECT * query will return only visible columns - invisible ones will be suppressed. That is very important for applications which (a) execute these queries agains the database, (b) expecting a particular set of columns and (c) we are now about the extend the table ...
SQL> select * from tab_invcolumns

        ID NAME
---------- ------------------------------
         1 CZARSKI
I'd like to emphasize: Invisible columns are no security feature. The INVISIBLE attribute is absolutely inappropriate to implement access control for a table column, since we can use invisible columns like the visible ones - the only difference is that invisible columns must be used explicitly. For access control, we have other functions and features.
SQL> insert into tab_invcolumns (id, name, kennzeichen) values (2, 'MUELLER', 'NORMAL');

1 row created.

SQL> select id, name, kennzeichen from tab_invcolumns;

        ID NAME                           KENNZEICHEN
---------- ------------------------------ --------------------
         1 CZARSKI                        ?
         2 MUELLER                        NORMAL

2 rows selected.
Honestly, invisible columns are not really a "new" feature - the Oracle database uses invisible columns at least since Oracle8 (don't know about Oracle7). The following script (runs in Oracle8 and higher) creates a table with an object type. For the attibutes of this object type, Oracle creates invisible table columns - since Oracle8. The real "new" thing in Oracle12c is that we now have the "official" attribute INVISIBLE, which we can use for any table column.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create type person_t as object(
  2    name      varchar2(200),
  3    firstname varchar2(200),
  4    birthdate date
  5  )
  6  /

Type created.

SQL> create table tab_persons(
  2    id number(10),
  3    person person_t
  4  )
  5  /

Table created.

SQL> select column_name, data_type, hidden_column from user_tab_cols 
  2  where table_name='TAB_PERSONS'

COLUMN_NAME                    DATA_TYPE       HID
------------------------------ --------------- ---
ID                             NUMBER          NO
PERSON                         PERSON_T        NO
SYS_NC00003$                   VARCHAR2        YES
SYS_NC00004$                   VARCHAR2        YES
SYS_NC00005$                   DATE            YES

SQL> select id, SYS_NC00003$, SYS_NC00004$, SYS_NC00005$ from tab_personen

        ID SYS_NC00003$    SYS_NC00004$    SYS_NC00005$
---------- --------------- --------------- -------------------------
         1 Doe             John            31.07.1984 00:00:00

19. Juli 2013

Workshop "APEX-Anwendungen für mobile Endgeräte" am 1. August in Potsdam

Anwendungen für Mobile Endgeräte sind derzeit in aller Munde - nahezu überall taucht die Anforderung "Unterstützung von Smartphones oder Tablets" auf. Wie die meisten wissen, werden mobile Endgeräte mit der aktuellen APEX Version 4.2 out-of-the-box unterstützt. Und mobile Anwendungen werden in typischer APEX-Manier schnell und einfach erstellt. Kurzentschlossene im Raum Berlin/Brandenburg können das am 1. August 2013 im "APEX Mobile Hands On Workshop" 'LIVE' erleben. Dort erfahren Sie ...
  • Wie man eine APEX-Anwendung, basierend auf Tabellen erstellt
  • APEX Komponenten wie Formulare, Berichte, Diagramme oder Kalender einbindet
  • Wie man auf das GPS in einem Smartphone zugreift, die Koordinaten in der Datenbank speichern und damit arbeiten kann
  • Wie man auf die Kamera zugreift, die Bilder speichert und einfache Bildoperationen durchführen kann
  • Und vieles mehr ...
Darüber hinaus ist natürlich auch Zeit für den Austausch und zur Diskussion vorgesehen. Mehr Informationen zu Agenda, Terminen und Workshop-Voraussetzungen sind auf der Event-Webseite. Die Teilnahme an der Veranstaltung ist kostenlos - und noch sind Plätze frei - am besten gleich anmelden.
Übrigens: die nächsten Workshops finden am 3. September in Frankfurt, am 1. Oktober in Stuttgart und am 15. Oktober in Düsseldorf tatt.
This blog posting is about an event in german language and therefore in german only.

16. Juli 2013

Ein Oracle12c "New Feature": Freien Plattenplatz auf dem DB Server bestimmen

"Image processing in the database": Making ORDIMAGE handy and practical
In diesem Blog-Posting möchte ich etwas vorstellen, was sicherlich in keiner "Oracle12c New Feature" Zusammenstellung vorkommen wird: Oracle12c erlaubt es, eine PL/SQL Funktion einzurichten, die den freien Plattenplatz auf dem Datenbankserver ermittelt. Der eine oder andere DBA kann sowas vielleicht gebrauchen. Die Grundlage dafür ist (wie bei mir so oft) Java in der Datenbank. Oracle12c bringt ein Java6 mit - und in dieser Version gibt es in der Klasse java.io.File die Methode getFreeSpace(). Und die nutzen wir dann auch direkt mal ...
create or replace and compile java source named JAVA_GET_FREESPACE as
import java.io.*;

public class FreeSpaceHelper {
  public static long getFreeSpace(String pPath) throws Exception {
    return new File(pPath).getFreeSpace();
  }
}
/
sho err

create or replace function get_free_disk_space (p_path in varchar2) return number
is language java name 'FreeSpaceHelper.getFreeSpace(java.lang.String) return long';
/
Dann können wir uns ja mal die Inhalte von DBA_DATA_FILES ansehen - und zu jedem File den noch freien Platz auf dem Device dazustellen ...
select 
  file_name, 
  bytes file_size,
  get_free_disk_space(file_name) free_space
from dba_data_files
/
Ein wenig formatiert, sieht der Output so aus ...
FILE_NAME                                          FILE_SIZE       FREE_SPACE
-------------------------------------------------- --------------- -------------
/opt/oracle/oradata/orcl/pdb1/system01.dbf         270MB           22GB
/opt/oracle/oradata/orcl/pdb1/sysaux01.dbf         680MB           22GB
/opt/oracle/oradata/orcl/pdb1/pdb1_users01.dbf     5MB             22GB
Eine generelle Übersicht über die neuen Funktionen in Oracle12c findet Ihr, wie schon im letzten Blog Posting geschrieben, auf den Seiten der deutschsprachigen APEX und PL/SQL Community und der deutschsprachigen DBA Community.
Today I'd like to introduce something which I'd hardly expect to be contained in a "Oracle12c New Features" collection. In Oracle12c you can create a function which determines the free space on a database servers' disk. I could imagine that this might be useful ...
The function will rely (as so often in my blog postings) on Java in the Database. Oracle12c is equipped with a Java6 VM - and this Java version contains the method getFreeSpace() within java.io.File - and this is very easy to use ...
create or replace and compile java source named JAVA_GET_FREESPACE as
import java.io.*;

public class FreeSpaceHelper {
  public static long getFreeSpace(String pPath) throws Exception {
    return new File(pPath).getFreeSpace();
  }
}
/
sho err

create or replace function get_free_disk_space (p_path in varchar2) return number
is language java name 'FreeSpaceHelper.getFreeSpace(java.lang.String) return long';
/
We can use this function, together with DBA_DATA_FILES, in order to see, for each data file, the free space on the device.
select 
  file_name, 
  bytes file_size,
  get_free_disk_space(file_name) free_space
from dba_data_files
/
The output, a bit formatted ...
FILE_NAME                                          FILE_SIZE       FREE_SPACE
-------------------------------------------------- --------------- -------------
/opt/oracle/oradata/orcl/pdb1/system01.dbf         270MB           22GB
/opt/oracle/oradata/orcl/pdb1/sysaux01.dbf         680MB           22GB
/opt/oracle/oradata/orcl/pdb1/pdb1_users01.dbf     5MB             22GB

15. Juli 2013

Oracle12c - New Features für Entwickler und Administratoren ...

Seit kurzem ist ja das neue Datenbankrelease Oracle12c verfügbar - und auf den zahlreichen englischen Blogs wird ja bereits ein Feature nach dem anderen vorgestellt. Für deutschsprachige Leser möchte ich nochmals auf die Seiten der deutschsprachigen APEX und PL/SQL Community und der deutschsprachigen DBA Community hinweisen. Dort werden die neuen Funktionen kurz in deutscher Sprache vorgestellt. Wer noch mehr wissen möchte, finden im Oracle12c New Features Guide eine vollständige Auflistung der neuen Funktionen.
In den folgenden Blog Postings werde ich dann das eine oder andere New Feature etwas im Detail betrachten - vor allem die, die ansonsten eher zur Seite fallen (ja: Oracle12c bietet noch mehr als Multitenant und 32K VARCHAR2) ...
This blog postings contains links to pages about "Oracle12c new Features" in German Language - therefore in German only.

Beliebte Postings