In diesem Tipp stellen wir Ihnen einige Aufgaben vor, die in APEX mit der Kommandozeile, also typischerweise mit SQL*Plus, erledigt werden können. Das sind mittlerweile mehr als man denkt. In einigen Fällen sind die Funktionsaufrufe, die APEX von Haus aus bereitstellt, allerdings etwas umständlich, so dass ein wenig Skripting hilfreich sein kann. Fangen wir am besten gleich an.

APEX Workspace erzeugen

Dies ist sicherlich eine der ersten Aufgaben, die man per Kommandozeile ausführen möchte ... und im PL/SQL Paket APEX_INSTANCE_ADMIN findet sich mit ADD_WORKSPACE auch ein entsprechender Aufruf dazu ...

PROCEDURE ADD_WORKSPACE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_WORKSPACE_ID                 NUMBER                  IN     DEFAULT
 P_WORKSPACE                    VARCHAR2                IN
 P_SOURCE_IDENTIFIER            VARCHAR2                IN     DEFAULT
 P_PRIMARY_SCHEMA               VARCHAR2                IN
 P_ADDITIONAL_SCHEMAS           VARCHAR2                IN
 P_RM_CONSUMER_GROUP            VARCHAR2                IN     DEFAULT
 P_CLOUD_GROUP_NAME             VARCHAR2                IN     DEFAULT
 P_CLOUD_SERVICE_NAME           VARCHAR2                IN     DEFAULT
 P_HOST_PREFIX                  VARCHAR2                IN     DEFAULT

Natürlich braucht man, um APEX_INSTANCE_ADMIN (und später APEX_UTIL.CREATE_USER) aufrufen zu können, administrative Privilegien - Sie brauchen mindestens die APEX_ADMINISTRATOR_ROLE. Alternativ können Sie auch mit einem DBA-User arbeiten.

Allerdings reicht das nicht aus - denn in einen damit erzeugten Workspace kann sich zunächst niemand einloggen - mit APEX_UTIL.CREATE_USER muss vorher ein ADMIN-User eingerichtet werden. Ebenso wird das Datenbankschema nicht automatisch generiert - auch dies müsste mit einem CREATE USER-Kommando manuell geschehen. Um also einen sofort nutzbaren APEX-Workspace per Skript bereitzustellen, brauchen wir ein etwas umfangreicheres SQL-Skript, welches mehrere Dinge tut:

• Ein Datenbankschema mit den nötigen Privilegien erstellen (CREATE USER, GRANT). Das Passwort ist in diesem Fall stets oracle.
• Einen APEX Workspace gleichen Namens generieren (APEX_INSTANCE_ADMIN)
• Einen Workspace-Administrator namens ADMIN, ebenfalls mit dem Passwort oracle, erstellen (APEX_UTIL)

create user &1. identified by oracle
/

grant connect, resource, create view, alter session to &1.
/

begin
   apex_util.set_security_group_id(p_security_group_id=> 10);
   apex_instance_admin.add_workspace(
     P_WORKSPACE            => upper('&1.'),
     P_PRIMARY_SCHEMA       => upper('&1.'),
     P_ADDITIONAL_SCHEMAS   => upper('&1.')
   );
end;
/

commit
/

declare
  v_wsid apex_workspaces.workspace_id%type;
begin
   select workspace_id into v_wsid
   from apex_workspaces
   where upper(workspace) = upper('&1.');

   apex_util.set_security_group_id(p_security_group_id => v_wsid);

   apex_util.create_user(
     p_user_name       => 'ADMIN',
     p_web_password    => lower('oracle'),
     P_DEVELOPER_PRIVS => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',  
     p_first_name      => 'Max',
     p_last_name       => 'Mustermann',
     p_email_address   => 'max.mustermann@mailserver.com',
     p_default_schema  => '&1.',
     p_change_password_on_first_use => 'N' 
   );
end;
/

commit
/

begin
   apex_instance_admin.enable_workspace(
     p_workspace       => '&1.'
   );
end;
/

commit
/

undefine &1

Speichern Sie das Skript als create-apx-workspace.sql ab. Aufgerufen es in SQL*Plus dann wie folgt.

SQL> @create-apx-workspace.sql MYWORKSPACE

Natürlich kann man mit diesem Skript-Code auch eine PL/SQL Stored Procedure erstellen; dann wäre sogar der Aufruf aus anderem Umgebungen heraus denkbar.

Neuen User zum APEX Workspace hinzufügen

Basierend auf obigem Skript, ist diese Aufgabe sehr einfach.

set verify off

select workspace from apex_workspaces 
where workspace_id not in (10,11)
order by 1; 

accept wsname prompt '>>> Workspace: '
accept wsuser prompt '>>> Username:  '

declare
  v_wsid apex_workspaces.workspace_id%type;
begin
   select workspace_id into v_wsid
   from apex_workspaces
   where upper(workspace) = upper('&wsname.');

   apex_util.set_security_group_id(p_security_group_id => v_wsid);

   apex_util.create_user(
     p_user_name       => '&wsuser.',
     p_web_password    => lower('oracle'),
     p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',  
     p_first_name      => 'Max',
     p_last_name       => 'Mustermann',
     p_email_address   => 'max.mustermann@mailserver.com',
     p_change_password_on_first_use => 'N' 
   );
end;
/

commit
/

Wichtig ist hier vor allem der Parameter p_developer_privs. Das obige Skript generiert einen Workspace-Administrator. Wenn ein Entwicklerkonto generiert werden soll, lässt man ADMIN weg und übergibt CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL. Ein End-User wird schließlich erzeugt, in dem SQL NULL übergeben wird.

Passwort eines APEX Workspace Users ändern

Analog dazu kann auch das Passwort eines APEX-Workspace-Users sehr einfach per Kommandozeile geändert werden. Dazu bringt APEX zwar schon den einen oder anderen Aufruf mit ...

• APEX_UTIL.CHANGE_CURRENT_USER_PW erlaubt das explizite Ändern des Passworts, aber nur für eine gegebene APEX-Sitzung und nur für den angemeldeten User. Diese Funktion ist geeignet, um bestehende APEX-Anwendungen mit einem Dialog zum Ändern des Passworts auszustatten.
• APEX_UTIL.RESET_PW erlaubt das Zurücksetzen für einen angegebenen User; allerdings bekommt dieser das neue Passwort per Mail zugeschickt. Wenn keine oder eine ungültige Mailadresse hinterlegt ist, nutzt diese Funktion nichts.

Gesucht wäre eine PL/SQL-Funktion, welche das gleiche tut, wie der Dialog der APEX-Administrationsoberfläche: Die Auswahl eines Users und das explizite Eingeben des neuen Passworts. In der Praxis braucht es gerade das sehr häufig. Die Basis zur Umsetzung ist die Funktion APEX_UTIL.EDIT_USER. Diese erlaubt das Ändern aller Attribute eines APEX Workspace-Users, darunter auch das Passwort. Allerdings müssen alle Attribute neu angegeben werden - die Funktion erkennt SQL NULL eben nicht als "keine Änderung". Der direkte Aufruf ist also wiederum etwas umständlich, also erzeugen wir eine PL/SQL-Prozedur zum einfachen Aufrufen.

create or replace procedure change_apex_user_password(
  p_workspace        in apex_workspace_apex_users.workspace_name%type,
  p_username         in apex_workspace_apex_users.user_name%type,
  p_new_password     in varchar2,
  p_change_on_use    in varchar2 default 'N'
) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_workspace_id     apex_workspace_apex_users.workspace_id%type;
begin
  begin
    select workspace_id into l_workspace_id
    from apex_workspaces where workspace = p_workspace;
  exception
    when NO_DATA_FOUND then
      raise_application_error(-20000, 'NONEXISTENT WORKSPACE', true);
  end;

  apex_util.set_security_group_id(
    p_security_group_id=>l_workspace_id
  );
  for i in (
    select
      USER_ID,
      USER_NAME,
      FIRST_NAME,
      LAST_NAME,
      START_DATE,
      END_DATE,
      DESCRIPTION,
      EMPLOYEE_ID,
      PERSON_TYPE,
      EMAIL_ADDRESS,
      DEFAULT_SCHEMA,
      ALLOW_ACCESS_TO_SCHEMAS,
      ACCOUNT_LOCKED,
      ACCOUNT_EXPIRY,
      FIRST_PASSWORD_USE_OCCURRED,
      CHANGE_PASSWORD_ON_FIRST_USE,
      apex_util.get_GROUPS_USER_BELONGS_TO(user_name) GROUP_IDS,
      apex_util.get_user_roles(user_name) ROLES
    from wwv_flow_users
    where security_group_id = l_workspace_id and user_name = p_username
  ) loop
    apex_util.edit_user(
      p_user_id                      => i.user_id,
      p_user_name                    => p_username,
      P_FIRST_NAME                   => i.first_name,
      P_LAST_NAME                    => i.last_name,
      P_WEB_PASSWORD                 => p_new_password,
      P_NEW_PASSWORD                 => p_new_password,
      P_EMAIL_ADDRESS                => i.email_address,
      P_START_DATE                   => i.start_date,
      P_END_DATE                     => i.end_date,
      P_EMPLOYEE_ID                  => i.employee_id, 
      P_ALLOW_ACCESS_TO_SCHEMAS      => i.allow_access_to_schemas,
      P_PERSON_TYPE                  => i.person_type,        
      P_DEFAULT_SCHEMA               => i.default_schema,
      P_GROUP_IDS                    => i.group_ids,
      P_DEVELOPER_ROLES              => i.roles,
      P_DESCRIPTION                  => i.description,
      P_ACCOUNT_EXPIRY               => i.account_expiry,
      P_ACCOUNT_LOCKED               => i.account_locked,
      P_FAILED_ACCESS_ATTEMPTS       => 0,
      P_CHANGE_PASSWORD_ON_FIRST_USE => p_change_on_use,
      P_FIRST_PASSWORD_USE_OCCURRED  => i.FIRST_PASSWORD_USE_OCCURRED
    );
  end loop;
  commit;
end change_apex_user_password;
/  

Diese neue Prozedur CHANGE_APEX_USER_PASSWORD hat eine sehr einfache Schnittstelle und funktioniert sogar für den zentralen APEX-ADMIN-User (User ADMIN im Workspace INTERNAL). Der Unix-Login auf dem Datenbankserver (zum Aufrufen von apxchpwd.sql) ist also nicht mehr nötig.

SQL> desc change_apex_user_password
PROCEDURE change_apex_user_password
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_WORKSPACE                    VARCHAR2(255)           IN
 P_USERNAME                     VARCHAR2(100)           IN
 P_NEW_PASSWORD                 VARCHAR2                IN
 P_CHANGE_ON_USE                VARCHAR2                IN     DEFAULT

SQL> exec change_apex_user_password('INTERNAL', 'ADMIN', 'apexiscool', 'N');

PL/SQL-Prozedur erfolgreich abgeschlossen.

Applikationen importieren

Zum Thema Export und Import von APEX-Anwendungen per Kommandozeile existieren schon einige Community-Tipps.

• Exportieren und Importieren per Kommandozeile
• APEX-Anwendungen automatisch versionieren: Ein Ansatz

Zum Exportieren per Kommandozeile braucht es den Aufruf des Java-basierten Werkzeugs APEXExport. Die enstehende SQL-Datei kann dann in SQL*Plus wie jedes andere SQL Skript eingespielt werden. Soll die Anwendung in ein anderes Workspace eingespielt werden oder eine neue Anwendungs-ID erhalten, so leistet das Paket APEX_APPLICATION_INSTALL wertvolle Dienste. Das folgende Skript ist ein Beispiel für ein "interaktives" APEX Importwerkzeug in SQL*Plus. Hierfür brauchen sie übrigens keine DBA-Privilegien; Sie müssen lediglich als der User angemeldet sein, der dem APEX-Workspace "als Schema" zugeordnet ist.

set define '^'
set verify off
set timing off

prompt >>> Existing Workspaces:

col workspace format a40
col application_name       format a40

select rownum zeile, workspace from (
select workspace from apex_workspaces
where workspace_id not in (10,11)
order by 1 asc
)
/

accept wsnum default 1 prompt          '>>> Enter line number for workspace to install in: [1] '
accept appid default 100 prompt        '>>> Enter application ID : [100]                       '
accept expfile default f100.sql prompt '>>> Enter export file: [f100.sql]                      '

declare 
  l_wsid number;
begin
  select workspace_id into l_wsid from (
    select workspace_id, workspace, rownum zeile from (
      select workspace_id, workspace from apex_workspaces
      where workspace_id not in (10,11)
      order by 2
    )
  )
  where zeile = ^wsnum.;

  apex_application_install.set_workspace_id(l_wsid);
  apex_application_install.set_schema(user);
  apex_application_install.set_application_id(^appid.);
  apex_application_install.set_application_alias('F_^appid.');
  apex_application_install.generate_offset;
end;
/

@@^expfile.

commit
/

Die Nutzung sieht etwa so aus ...

SQL> @ins
>>> Existing Workspaces:

     ZEILE WORKSPACE
---------- ----------------------------------------
         1 APEXSPATIAL
         2 CCZARSKI_DE_T
         3 DOAG2013
         4 DOAG2013PRO
         5 GEOWS
         6 HAMBURG
         : :

19 Zeilen ausgewählt.

>>> Enter line number for workspace to install in: [1] 5
>>> Enter application ID : [100]                       2002
>>> Enter export file: [f100.sql]                      f130.sql

PL/SQL-Prozedur erfolgreich abgeschlossen.

APPLICATION 130 - Geo-Workshop
Set Credentials...
Check Compatibility...
API Last Extended:20120101
Your Current Version:20120101
This import is compatible with version: 20120101
COMPATIBLE (You should be able to run this import without issues.)
Set Application ID...
...ui types
:

Natürlich kann man ein solches Skript noch weiter ausbauen. Interessant ist auch die Kombination mit dem eingangs erwähnten Skript zum Erstellen von APEX-Workspaces; denn so lässt sich ein neuer APEX-Workspace automatisch mit eigenen Beispiel- bzw. "Kochbuch"-Anwendungen ausstatten.

Attribute von APEX-Anwendungen setzen

Im Oracle SQL Developer gibt es einen Bereich Application Express, in dem sich das eine oder andere Attribut einer APEX-Anwendung ändern lässt. Interessant dabei ist, dass der SQL Developer dabei stets ein Show SQL anbietet, mit dem man sich ansehen kann, mit welchem Code die Aktion durchgeführt wird.

Das probieren wir nun anhand der Global Notification aus - denn gerade diese möchte man u.U. auch ohne Zugriff auf den APEX Application Builder ändern. Das folgende SQL*Plus Skript macht genau dies. Allerdings muss hierzu gesagt werden, dass die verwendeten Aufrufe des PL/SQL-Pakets WWV_FLOW_API nicht dokumentiert sind. Testen Sie also vorher.

set verify off
set define '&'

select workspace from apex_workspaces 
where workspace_id not in (10,11)
order by 1
/

accept wsname prompt '>>> Workspace:      '

col application_id format 9999999999
col application_name format a40

select application_id, application_name
from apex_applications
where workspace = '&wsname.'
order by 1 asc
/

accept appid prompt '>>> Application ID: '
accept msg prompt '>>> Notification:   '

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_wsid number;
begin
  select workspace_id into l_wsid from apex_workspaces where workspace = '&wsname.';
  apex_util.set_security_group_id(p_security_group_id => l_wsid);
  wwv_flow_api.set_global_notification (
    p_flow_id=> &appid.,
    P_GLOBAL_NOTIFICATION=> '&msg.'
  );
  commit;
end;
/  

Wichtige Nachrichten können nun auch ohne Entwicklerzugang zur APEX-Anwendung (ggfs. sogar automatisiert) gesetzt werden.

APEX-Anwendungen löschen

Schaut man in eine APEX Exportdatei hinein, so stellt man fest, dass das Löschen einer APEX-Anwendung sehr einfach ist. Gleich zu Beginn finden sich die Aufrufe von WWV_FLOW_API.REMOVE_FLOW und WWV_FLOW_AUDIT.REMOVE_AUDIT_TRAIL. Damit lässt sich - analog zu obigem Importier-Skript - ein "Lösch-Skript" bauen.

Wie bereits weiter oben, beim Ändern der Anwendungsattribute, wird auch hier das Paket WWV_FLOW_API verwendet, welches nicht dokumentiert ist. Sie können eine APEX-Anwendung alternativ auch mit dem dokumentierten APEX_INSTANCE_ADMIN.REMOVE_APPLICATION löschen - dann benötigen Sie aber einen DBA-User oder die Rolle APEX_ADMINISTRATOR_ROLE.

set define '^'
set verify off
set timing off

prompt >>> Existing Workspaces:
prompt 

col workspace format a40
col application_name       format a40

select rownum zeile, workspace from (
select workspace from apex_workspaces
where workspace_id not in (10,11)
order by 1 asc
)
/

accept wsnum default 1 prompt          '>>> Enter line number for workspace : [1] '


select application_id, application_name 
from apex_applications 
where workspace_id in (
  select workspace_id from (
    select workspace_id, workspace, rownum zeile from (
      select workspace_id, workspace from apex_workspaces
      where workspace_id not in (10,11)
      order by 2
    )
  )
  where zeile = ^wsnum.
)
/

accept appid default 100 prompt        '>>> Enter application ID : [100]          '

declare 
  l_wsid number;
begin
  select workspace_id into l_wsid from (
    select workspace_id, workspace, rownum zeile from (
      select workspace_id, workspace from apex_workspaces
      where workspace_id not in (10,11)
      order by 2
    )
  )
  where zeile = ^wsnum.;

  apex_util.set_security_group_id(p_security_group_id=>l_wsid);
  wwv_flow_api.remove_flow(nvl(wwv_flow_application_install.get_application_id,^appid.));
  wwv_flow_audit.remove_audit_trail(nvl(wwv_flow_application_install.get_application_id,^appid.));
end;
/

commit
/

Die Nutzung sieht etwa so aus ...

SQL> @del
>>> Existing Workspaces:

     ZEILE WORKSPACE
---------- ----------------------------------------
         1 APEXSPATIAL
         2 CCZARSKI_DE_T
         3 DOAG2013
         4 DOAG2013PRO
         5 GEOWS
         6 HAMBURG
         : :

19 Zeilen ausgewählt.

>>> Enter line number for workspace : [1] 5

APPLICATION_ID APPLICATION_NAME
-------------- ----------------------------------------
           100 Sample Application
           101 Räumliche Auswertungen
           128 Oracle Maps Plugin
           130 Geo-Workshop
           135 LRS Demo
           136 Sample Geolocation Showcase
           157 New MAPS Plugin
          2001 Geo-Workshop

10 Zeilen ausgewählt.

>>> Enter application ID : [100]          2001

PL/SQL-Prozedur erfolgreich abgeschlossen.

Transaktion mit COMMIT abgeschlossen.

SQL>

APEX-Instanzparameter verwalten

In der APEX-Administration (Workspace INTERNAL) werden neben der Workspace- und Nutzerverwaltung auch zahlreiche Parameter eingestellt. Auf der Kommandozeile dienen dazu die Aufrufe GET_PARAMETER und SET_PARAMETER im Paket APEX_INSTANCE_ADMIN. Hierfür brauchen Sie wiederum DBA-Privilegien. Die verschiedenen Parameter, die sich nutzen lassen, sind in der Dokumentation zu APEX_INSTANCE_ADMIN aufgeführt. Das folgende Beispiel ruft den aktuell eingestellten Emailserver ab und stellt diesen um.

SQL> select
  2     apex_instance_admin.get_parameter('SMTP_HOST_ADDRESS') host,
  3     apex_instance_admin.get_parameter('SMTP_HOST_PORT') port
  4* from dual

HOST                                     PORT
---------------------------------------- ----------
mailserver.meinefirma.de                 25

1 Zeile wurde ausgewählt.

SQL> exec apex_instance_admin.set_parameter('SMTP_HOST_ADDRESS', 'new-mail-server.mydomain.com');

PL/SQL-Prozedur erfolgreich abgeschlossen.

Fazit

Somit lassen sich sehr viele administrative Aufgaben auch ohne die APEX-Weboberfläche ausführen. Insbesondere das Provisionieren neuer APEX Workspaces oder Nutzerkonzen bzw. das Zurücksetzen von Passwörtern lässt sich auf diesem Wege sehr elegant in zentrale Ticket- und Provisionierungssysteme integrieren - so dass ein APEX Workspace auf dem gleichen Wege bereitgestellt werden kann wie ein Email-Konto.

Natürlich sind die Möglichkeiten damit noch lange nicht erschöpfend beschrieben: Nimmt man das APEX Data Dictionary hinzu, so lassen sich auch zahlreiche Reports und ggfs. auch Alertings aus der APEX Engine heraus generieren - die APEX-Umgebung wird damit - ebenso wie die Oracle-Datenbank selbst - ein normaler Teil der IT-Infrastruktur. Wie die Integration in ein Werkzeug aussehen kann, zeigt übrigens das APEX-Workspace Plugin für den SQL Developer, welches in der APEX Community vor einiger Zeit vorgestellt wurde. Auch dieses basiert auf den hier beschriebenen PL/SQL-Paketen.

Zurück zur Community-Seite