Erstellen dynamischer Webseiten mit PL/SQL Server Pages
Werbung
Erstellen dynamischer Webseiten
mit PL/SQL Server Pages
Autor: Klaus Rohrmoser, unabhängiger Datenbank Architekt
DOAGNews Q3_2004
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere die der Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und Tabellen, der Funksendung,
der Mikroverfilmung oder der Vervielfältigung auf anderen Wegen und der Speicherung in Datenverarbeitungsanlagen, bleiben, bei auch nur auszugsweiser Verwertung, vorbehalten. Eine Vervielfältigung
dieses Werkes oder von Teilen dieses Werkes ist auch im Einzelfall nur in den Grenzen der gesetzlichen
Bestimmungen des Urheberrechtes der Bundesrepublik Deutschland vom 9. September 1965 in der
jeweils geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2004
Einleitung
Mit Hilfe von PL/SQL Server Pages (PSPs) lassen sich mit relativ geringem Aufwand
dynamische Webseiten aus einer Datenbank erzeugen. Somit hat man ein Werkzeug an der
Hand, das Datenbank Abfragen in HTML darstellt und im Web sichtbar macht. Statt einen
Report in SQLPLUS oder als ASCII Datei auszugeben, lassen sich die Daten in bestehende
Web Seiten integrieren, sind damit in einem bestehenden Intranet oder auch im Internet
verfügbar.
Ziel dieses Artikels ist es, einen Einstieg in die Technik der PSP´s zu geben. Die ersten
Hürden beim Umgang mit PSP´s sollen genommen werden, ein Überblick über die
notwendigen Komponenten soll gegeben werden.
Hierbei werden Grundkenntnisse über HTML, PL/SQL, Webserver und Datenbank
vorausgesetzt.
Nur ein paar Worte hierzu. HTML dient zur Präsentation von Daten, in der andere Sprachen
eingebettet werden können. HTML ist in TAG´s aufgebaut, die geschachtelt werden können.
PL/SQL ist die Prozedurale Erweiterung von SQL zur Abfrage und Manipulation von Daten
einer relationalen Datenbank.
Voraussetzung ist eine Oracle RDBMS Installation einschließlich des Oracle HTTP Listeners,
wie es ab Version 8.1.7 gegeben ist. Hier wird die Version Oracle 9.2.0 eingesetzt. Der iAS
wird übrigens nicht benötigt, kann aber verwendet werden.
Definition und Abgrenzung
PL/SQL Server Pages oder PSP´s sind HTML Seiten in denen PL/SQL Code eingebettet ist.
Aus HTML Sicht wird der PL/SQL Code als Kommentar dargestellt. Diese werden dann in
die Datenbank als PL/SQL Prozedur geladen. Die PSP ist damit aus Datenbank Sicht eine
PL/SQL Prozedur, welche das PL/SQL wie gewohnt kompiliert und beim Aufruf parst sowie
das HTML entsprechend darstellt.
Hierbei kommen alle Eigenschaften von PL/SQL Prozeduren zum tragen, wie bspw.
Zugriffsteuerung über die Datenbank, Caching im Library Cache usw. Aus Performanzsicht
ist im Vergleich zu manch anderen Server Pages zu sagen, das die PSP bereits in der
Datenbank kompiliert vorliegen.
Server Pages werden zentral verwaltet und können von verschiedenen HTML fähigen Clients
aufgerufen werden. Neben den PSP´s gibt es auch weitere Server Pages wie Active Server
Pages oder Java Server Pages.
ASP´s sind ebenfalls HTML Seiten, in denen VisualBasic eingebettet ist, JSP´s hingegen
enthalten Java.
Eine PSP ist nicht mit dem Web Development Toolkit zu verwechseln. Ein PSP ist eine mit
speziellen Tags ausgestattet HTML Seite, das Web Delepment Toolkit wird zur Erzeugung
von PL/SQL verwendet. Selbstverständlich können in der „real World“ diese beiden Ansätze
vermischt werden, wie es auch im Kapitel „Beispielanwendung“ zu sehen ist.
©2004
Eine PSP hat folgendes Aussehen:
<%@ page language="PL/SQL" %>
<%@ plsql procedure="hello" %>
<HTML>
<TITLE>Say Hello</TITLE>
<BODY>
Hello. This is the PSP
</BODY>
</HTML>
Nach laden der PSP in die Datenbank wird daraus:
CREATE OR REPLACE PROCEDURE hello
AS
BEGIN NULL;
htp.prn('');
htp.prn('
<HTML>
<TITLE>Say Hello PSP</TITLE>
<BODY>
Hello. This is the PSP
</BODY>
</HTML>
');
END;
Das PL/SQL Web Development Toolkit ist eine Sammlung von Built-in Packages in der
Datenbank. Damit lassen sich die meisten HTML Befehle abdecken. Die wichtigsten
Komponenten sind HTP, HTF, OWA_UTIL, OWA_COOKIE. Das Toolkit ist im Schema
SYS zu finden und wird über die Datei $ORACLE_HOME/rdbms/admin/owaload.sql in die
Datenbank geladen.
Mit Hilfe des Toolkit kann dasselbe Ergebnis erzielt werden, dass auch mit PSP erreicht
wurde. Hierzu ruft man folgende Prozedur auf:
CREATE OR REPLACE PROCEDURE hello
is
begin
htp.title('Say Hello');
htp.print ('<h1> Hello. This is the PSP </h1>');
end;
Welche Methode man bevorzugt, liegt an der Gewichtung von HTML gegenüber PL/SQL im
Code. Eine Entscheidung sollte aufgrund der Anforderung aus der Anwendung getroffen
werden.
Architektur
PSP´s werden von Browsern abgerufen. Hierbei liegt eine 3-tier Architektur zugrunde, in dem
unter Verwendung des HyperText Transmission Protokols, ein Universal Resource Locator
URL an den Webserver geliefert wird. Dieser übersetzt die URL und bindet daran eine Datei
oder führt ein Programm aus und sendet dann das Ergebnis via http wieder zurück an den
Browser.
http ist ein zustandloses Protokoll zur Übertragung von MIME Typen. Es baut auf TCP/IP auf
und ist dort der Anwendungsschicht zuzuordnen. Sessioninformationen werden üblicherweise
in Cookies gespeichert.
©2004
Das Webserver Modul mod_plsql dient dem Webserver zur Kommunikation mit der
Datenbank. Hierzu wird ein Database Access Descriptor konfiguriert, s. „Einrichten eines
DAD´s“
Eine URL zum Abruf von PSP´s hat typischerweise folgendes Format:
http://host:port/pls/<dad_name>/<procedure>
PLS ist der Identifier des mod_plsql vor dem eigentlichen DAD Namen. Damit verbindet man
sich an die Datenbank und kann die dort gespeicherten PSPs aufrufen.
http LISTENER
HTTP
mod_plsql
DAD1
...
DAD<n>
....
Benutzer
SQLNet
Schema
PL/SQL
Web Dev. Toolkit
Datenbank
Webserver
Abb. „Architektur“
Das PSP wird als stored Procedure in der Datenbank ausgeführt und das Ergebnis wird dem
Webserver als HTML zurückgeliefert. Der Webserver leitet es dann an den Client weiter.
Einrichten eines DAD´s
Der http Listener unter Oracle ist ein erweiterter Apache Webserver, der u.a. mit dem Modul
mod_plsql erweitert wurde. Das Modul stellt die Schnittstelle zwischen Web und Datenbank
dar. Konfiguriert wird es im wesentlichen über einen Database Access Descriptor. Der DAD
teilt dem Webserver mit, wie er sich gegen die Datenbank verbinden soll.
Nach Start der Datenbank und des http_LISTENER kann der DAD eingerichtet werden.
Hierzu ist im Browser die URL des http_LISTENER einzugeben, in der Form
©2004
http://host:port
In der http Server Startseite ist auf den Link „Mod plsql Configuration Menu“ zu klicken.
Anschließend auf den Link „Einstellungen für Gateway Database Access Descriptor“ und
dann auf „Standardwert hinzufügen“ klicken.
Nun ist man im Konfigurationsmenü des DADs. Dort stehen die anzugebenen Werte mit
brauchbaren Erklärungen dazu.
Wir konfigurieren nur das nötigste, wie: Name des DADs, Connectstring in Form von
Benutzer/Passwort@tnsalias. Berechtigungsprüfung bleibt Basic, man kann hier eigene
Packages hinterlegen oder vorhandene Möglichkeiten benutzen, wie Single Sign On.
In der Homepage kann man eine Default Prozedur hinterlegen, die beim Aufruf der URL des
DAD´s im Browser angezeigt wird, wenn keine Prozedur explizit angegeben wird.
Anschließend ganz oben auf der Seite auf „anwenden“ drücken und dann „ok“, der DAD steht
nun bereit.
Testen kann man den DAD mit der URL
http://<host>/pls/<dad_Name>/<procedure>
Lässt man die Prozedur weg erhält man die Homepage, die eben bei der Konfiguration
eingegeben wurde.
Aufbau einer PSP
Wie ist nun eine PSP aufgebaut? Die Antwort ist, wie eine HTML Seite in denen der PL/SQL
Code mit speziellen Tags eingebettet wird. Für den Aufbau und Tags sind folgende
Direktiven einzuhalten:
Page: hier werden die Eigenschaften der Page definiert, wie Sprache (PL/SQL), Mime
Type (default text/html), FehlerSeite
<%@ page language=“PL/SQL“ contenttype= „html“ errorpage= „file.psp“ %>
Procedure: hier wird der Name der Prozedur wie er in der DB zu finden ist vergeben
<%@ procedure=“proc_name“ %>
Parameter: hier werden Parameter definiert, die an die Prozeduren übergeben werden
oder von der Prozedur zurückgegeben werden (IN/OUT).
<%@ plsql parameter=“p_name“ type=”PL/SQL Datentyp” default=”’Wert’” %>
Declaration: hier werden Variablen, Cursor etc. definiert
<%! V_text varchar2(20) := ‘Hallo’; %>
Print: hier wird Text ausgegeben.
<%=’der Wert von v_text ist: ’||v_text %>
Include: hier können andere HTML Seiten oder Prozeduren in die PSP eingebunden
werden, um zum Beispiel ein Stylesheet einzubinden. Die Datei wird zum Zeitpunkt
des Ladens der PSP eingebunden. Vererbung ist nur beim Ladezeitpunkt gegeben,
danach ist die Einbindung statisch.
©2004
<%@ include file=“stylesheet.html“ %>
<% procedure_name; %>
oder
Anschließend kann wie gewohnt ausführbarer PL/SQL Code (ohne BEGIN und END)
eingebettet werden, wie folgt.
<% /* BEGIN pl/sql fängt hier an */
....
enthält sinnvollerweise ein Exeception Handling
....
/* END pl/sql hört hier auf
%>
*/
Zu beachten ist, dass die Tags aus HTML Sicht als Kommentar <% comment %> dargestellt
sind. PSP interpretiert den Inhalt der Kommentare, wobei eine Variablen Deklaration immer
ein Ausrufezeichen nach dem Kommentar Tag enthält, <%! V_text varchar2(20); %>.
PL/SQL kann beliebig mit HTML gekapselt werden.
Tipps zur Programmierung
Um den Code übersichtlich und wartbar zu halten, sollte dieser in Präsentations- und
Anwendungslogik getrennt werden. HTML ist im wesentlichen Präsentationslogik, wogegen
mit Hilfe von PL/SQL Anwendungslogik erzeugt werden kann. Daher sollte vermieden
werden Anwendungslogik in HTML zu packen, auch wenn dieses mit PSP´s möglich ist.
Durch eine strikte Trennung ist auch eine Aufgabenverteilung besser möglich, Webseiten
Programmierung auf der einen, Datenbank Programmierung auf der anderen Seite.
Eine Webseiten Anwendung kann somit in 3 Teile gegliedert werden
•
•
•
statische HTML Seiten, für Gliederung und Aufbau der Webseite, z.B. index.html
PSP´s für Darstellung von dynamischen Inhalt, z.B. Datenbank Reports
PL/SQL Packages für die Anwendungslogik, z.B. Berechnungen, Eingabeprüfungen
Ein einheitliches Aussehen der Webseite lässt sich durch die Direktive „include“ erreichen
<%@ include file=“stylesheet.html“ %>
Mit Hilfe von Stylesheets können in der Webseite Farben, das Firmenlogo usw. hinterlegt
werden.
Selbstverständlich sollten auch Exception Handling, Namenskonventionen usw. verwendet
bzw. eingehalten werden.
Deployment
PSPs werden als HTML mit Hilfe von loadpsp in die Datenbank geladen. loadpsp ist
unter $ORACLE_HOME/bin zu finden.
$ loadpsp
usage: loadpsp [-replace] -user <logon> [<page1> <page2> ...]
where <logon> ::= <username>/<password>[@<connect_string>]
©2004
-replace entspricht dem CREATE OR REPLACE PROCEDURE,
-user ist der Eigentümer der Prozedur,
<logon> ist der übliche Verbindungsstring,
page1 .. page2 sind die zu ladenden Seiten.
Beispiel: loadpsp –replace –user k_user/k_pw@orca hello.psp
Zu beachten ist, dass die zu ladenden Dateien mit dem Kürzel .psp enden. Ebenso müssen
diese Dateien den Direktiven, wie im Kapitel „Aufbau eines PSPs“ beschrieben, entsprechen.
Falls beim Laden Fehler auftreten, werden diese wie gewohnt im Format ORA-nnnnn und
PLS-nnnnn dargestellt:
ORA-20006: "hello.psp": compilation failed with the following errors.
"hello.psp", line 11, pos 2: PLS-00103: Fand das Symbol "=" als eines der folgenden
erwartet wurde:
:= . ( @ % ;
Bei statischen HTML Seiten ist darauf zu achten, diese in ein Verzeichnis unter dem root
Verzeichnis des Webservers zu legen. Aufgerufen werden diese nicht über den DAD sondern
direkt durch das virtuelle Mapping des Webservers.
Beispielanwendung
Das Beispiel zeigt ein für DBA´s nützliches Werkzeug, einen Überblick über Auslastung der
Tablespaces einschließlich einer Vergleichsmöglichkeit mit historischen Daten. (Anm. der
Monitor berücksichtigt nur belegte Tablespaces, deren Segmentverwaltung nicht automatisch
ist und setzt eine UNIFORM SIZE bei den EXTENTS voraus). Das Beispiel soll
verdeutlichen, wie PSPs eingesetzt werden können.
Im Beispiel ist eine Mischung von PSP und Development Toolkit zu sehen, da mit dem
Toolkit eine Funktion TABLEPRINT zur Verfügung gestellt wird, mit der man eine Tabelle
ohne großen Aufwand erzeugen kann.
Ein Join ist mit dieser Funktion nicht möglich, daher wird auf ein Konstrukt mit Temporary
Tables zurückgegriffen. Mit deren Hilfe lassen sich komplexe Queries leicht darstellen.
Die Anwendung besteht aus einer rudimentären MenüDatei „index.html“, einem Stylesheet
PSP und einen Daten PSP. Diese stellen dir Präsentationsslogik dar.
Die Anwendungslogik wurde mit PL/SQL abgebildet und besteht aus einer Funktion zum
Datenladen und einer Prozedur zur Datenextraktion.
Auf der Datenbank wurden noch 2 Tabellen und 1 Sequenz angelegt. Die Vergabe von
Rechten ist im Beispiel nicht berücksichtigt.
©2004
Hier der Quellcode zum ausprobieren (der verwendete DAD hat die Bezeichnung PSP):
Index.html
<HTML>
<HEAD>
<META NAME="Tablespace Reporting", "Monitoring">
<TITLE> Tablespace Reporting </TITLE>
</HEAD>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<p><b><font face="Arial" size="6">
Tablespace Übersicht
<form action="http://localhost/pls/psp/tbs_overview_web" method=post>
<p><b><font face="Arial" size="4">
Eingabe Snapid: &nbsp;&nbsp;&nbsp;<input type=number name="p_snapid"
maxlength="4" size="4" style="background-color:white;"></font></b></p>
<input type=submit value="Absenden" >
</form>
</BODY>
</HTML>
01_objekte.sql
CREATE SEQUENCE tbs_overview_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;
CREATE TABLE tbs_overview_t (SnapId NUMBER(10),TablespaceName VARCHAR2(30),
NextExtent_MB NUMBER(20), UsedExtents NUMBER(20),
FreeExtents NUMBER(20), UsedGB NUMBER(20), FreeGB NUMBER(20));
CREATE global TEMPORARY TABLE tbs_overview_tmp
(TablespaceName VARCHAR2(30), NextExtent_MB NUMBER(20), UsedExtents NUMBER(20),
FreeExtents NUMBER(20), UsedGB NUMBER(20), FreeGB NUMBER(20))
ON COMMIT DELETE ROWS;
02_tbs_overview.sql
CREATE OR REPLACE PROCEDURE tbs_overview (
p_snapid IN NUMBER DEFAULT NULL)
AS
BEGIN
IF p_snapid IS NOT NULL THEN
-- curval
INSERT INTO tbs_overview_tmp (TablespaceName, NextExtent_MB, UsedExtents,
FreeExtents, UsedGB, FreeGB)
(SELECT TablespaceName, NextExtent_MB, UsedExtents, FreeExtents, UsedGB, FreeGB
FROM tbs_overview_t
WHERE snapid = (SELECT MAX(snapid) FROM tbs_overview_t)
MINUS
-- histval
SELECT TablespaceName, NextExtent_MB, UsedExtents, FreeExtents, UsedGB, FreeGB
FROM tbs_overview_t
WHERE snapid = p_snapid);
ELSE
INSERT INTO tbs_overview_tmp (TablespaceName, NextExtent_MB, UsedExtents,
FreeExtents, UsedGB, FreeGB)
(SELECT t.tablespace_name as "TablespaceName", t.next_extent/1024/1024 AS
"NextExtent_MB", ue.usedextents AS "UsedExtents",
SUM(TRUNC(f.bytes/(DECODE(t.next_extent,0,1,t.next_extent)))) AS "FreeExtents",
ROUND(ue.usedextents*t.next_extent/1024/1024/1024) AS "UsedGB",
ROUND(SUM(f.bytes/1024/1024/1024)) AS "FreeGB"
©2004
FROM dba_free_space f, dba_tablespaces t, (SELECT SUM(u.extents) AS usedextents,
u.tablespace_name AS usedtbs FROM dba_segments u
GROUP BY u.tablespace_name) ue
WHERE t.tablespace_name = f.tablespace_name (+) AND ue.usedtbs =
f.tablespace_name
GROUP BY t.tablespace_name, ue.usedextents, t.next_extent);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('No Differenz between Snapshots ');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error while generating Report '||SQLERRM);
END tbs_overview;
/
03_dataload.sql
CREATE OR REPLACE FUNCTION dataload RETURN VARCHAR2
AS
v_snapid NUMBER(38);
BEGIN
SELECT tbs_overview_seq.nextval INTO v_snapid FROM dual;
INSERT INTO tbs_overview_t (SnapId, TablespaceName, NextExtent_MB, UsedExtents,
FreeExtents, UsedGB, FreeGB)
(SELECT v_snapid AS SnapId, t.tablespace_name AS "TablespaceName",
t.next_extent/1024/1024 AS "NextExtent_MB", ue.usedextents AS "UsedExtents",
SUM(TRUNC(f.bytes/(DECODE(t.next_extent,0,1,t.next_extent)))) AS "FreeExtents",
ROUND(ue.usedextents*t.next_extent/1024/1024/1024) AS "UsedGB",
ROUND(SUM(f.bytes/1024/1024/1024)) AS "FreeGB"
FROM dba_free_space f, dba_tablespaces t, (SELECT SUM(u.extents) AS usedextents,
u.tablespace_name AS usedtbs FROM dba_segments u
GROUP BY u.tablespace_name) ue
WHERE t.tablespace_name = f.tablespace_name (+) AND ue.usedtbs =
f.tablespace_name
GROUP BY t.tablespace_name, ue.usedextents, t.next_extent);
COMMIT;
RETURN 'Data Successful loaded for Table ';
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error Loading Data for Table ';
END dataload;
/
04_stylesheet_tbs.psp
<html>
<%@ plsql procedure="stylesheet_tbs" %>
<%@ page language="PL/SQL" %>
<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
</html>
05_tbs_overview_web.psp
<%@ page language="PL/SQL" %>
<%@ plsql procedure="tbs_overview_web" %>
<%@ plsql parameter="p_snapid" type="NUMBER" %>
<% /* Mixture of PSP and the Web Development Toolkit */ %>
<% stylesheet_tbs; %>
<% tbs_overview(p_snapid); %>
<HTML>
©2004
<HEAD><TITLE>Show Tablespace Overview (Complete Dump)</TITLE></HEAD>
<BODY>
<p><b><font face="Arial" size="5"><u>Capacity Report:</u></font>
<font face="Arial" size="4">
&nbsp;&nbsp;&nbsp;&nbsp;Tablespace Status</font></b></p>
<%
declare
dummy boolean;
begin
dummy := owa_util.tableprint
(CTABLE => 'tbs_overview_tmp',
CATTRIBUTES => 'border=1,style="background-color:rgb(204,204,204);",
cellspacing="0", bordercolor="FF99FF"',
CCOLUMNS => 'TablespaceName, NextExtent_MB, UsedExtents, FreeExtents,
UsedGB, FreeGB',
CCLAUSES => 'order by FreeExtents');
end;
%>
</BODY>
</HTML>
Fazit
Mit Hilfe der PSPs kann man mit etwas PL/SQL KnowHow und einem HTML Editor schnell
und einfach dynamische Webseiten erzeugen.
Man erhält somit eine fertige Anwendung einschließlich GUI, ohne Tools wie Forms
einsetzen zu müssen.
Was noch zu ergründen wäre sind Themen wie Sicherheit (Authentifizierung, „Logouts“ bzw.
Stale Sessions), Performanz (wie schnell ist eine etwas aufwendiger gestaltete Webseite mit
PSPs im Vergleich zur vorhandenen Webseite) usw.
Als ich die PSPs „entdeckt“ habe, fande ich sie gleich sympathisch ☺ Vielleicht habe ich
diesen Eindruck auch manchem Leser vermitteln können.
Für Anregungen und Kritik stehe ich gerne zur Verfügung unter [email protected]
Klaus Rohrmoser
[email protected]
©2004
