Archiv

Archive for the ‘Oracle 11g’ Category

Oracle LISTAGG-Funktion läuft auf Fehler ORA-01489

Die Ursache für den Fehler

SELECT ‚<ul><li>‘ || LISTAGG(text, ‚</li><li>‘) WITHIN GROUP (ORDER BY datum desc) || ‚</li></ul>‘
FROM   test1;

=> ORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang

bei Verwendung der LISTAGG-Funktion liegt darin, dass Oracle intern den Datentyp VARCHAR2 verwendet und die Länge dieses Datentyps über den init-ora Parameter „MAX_STRING_SIZE“ festgelegt wird. Der Default für diesen Parameter ist „STANDARD“, was bedeutet, dass die Länge des Datentyps VARCHAR2 auf 4000 Zeichen limitiert ist (dieses Limit kennen wir ja schon sehr lange).

Wie kommen wir nun aus dieser Nummer raus?

Lösung

Im Prinzip gibt es aus meiner Sicht 3 mehr oder weniger sinnvolle Lösungen:

  1. Ersetzen der LISTAGG-Funktion durch die XMLAGG-Funktion.
  2. Umsetzen des init-ora Parameters MAX_STRING_SIZE.
  3. Schreiben einer eigenen Aggregat-Funktion.

Leider haben alle 3 Lösungen auch Nachteile, auf die ich kurz eingehen möchte.

zu 1. Ersetzen der LISTAGG-Funktion

Wir können anstelle der LISTAGG-Funktion die Funktion XMLAGG verwenden, wobei hier zu beachten ist, dass ein XML-Format zugrunde gelegt wird und wir dieses noch umwandeln müssen.

SELECT ‚<ul>‘ — nur für spezielle HTML-Formatierung „unordered list“
|| DBMS_XMLGEN.CONVERT
(XMLAGG
(XmlElement(e, ‚<li>’||text||'</li>‘, “).EXTRACT(‚//text()‘)
ORDER BY id desc
).getClobVal() — Clob-Value des XML-Aggregats holen
, 1) — 1 = unescape
|| ‚</ul>‘ — nur für spezielle HTML-Formatierung „unordered list“
FROM   test1;

Prinzipiell sollte das problemlos funktionieren. Leider sind wir nach der Inbetriebnahme dieser Lösung doch wieder auf ähnliche Probleme gestoßen, wie bei der LISTAGG-Funktion (gleicher Fehler, nur andere Situation). Was feststeht ist, dass unsere Fehler aus der Methode „.getClobVal()“ resultierten, allerdings bekomme ich die Fehler mit den Testdaten nicht mehr reproduziert. Es scheint irgendwie mit Umlauten zu tun zu haben (z.B. Stringlänge eines Feldes ist 4000-Zeichen mit CHAR-Semantik und nun sind mehrere Umlaute enthalten, aber auch das bekomme ich nur noch sporadisch reproduziert – also leider nicht aussagefähig).

zu 2. Umsetzen des init-ora Parameters MAX_STRING_SIZE

Diese Lösung wäre eigentlich die perfekte Lösung. Durch Umsetzen dieses Parameters auf „EXTENDED“ wir die maximale Länge für VARCHAR2-Felder auf 32k erhöht und in der Regel sollte das für solche Funktionen ausreichen (wie man das macht, kann man z.B. hier nachlesen Burleson Consulting – max_string_size tips.

Aber Vorsicht! Es gibt einige Risiken, über die man sich vorher Gedanken machen sollte. Wir haben diesen Schritt erst mal nicht durchgeführt, weil wir sofort auf Fehler im SQLDeveloper gelaufen sind (unsere Version des SQLDevelopers kann keine VARCHAR2-Felder mit Inhalt > 4000 verarbeiten) und wir nun befürchten, dass evtl. diverse Schnittstellen, die auf unsere Datenbank zugreifen, auf ähnliche Probleme stoßen könnten.

zu 3. Schreiben einer eigenen Aggregat-Funktion

Diese Lösung haben wir derzeit in Betrieb und funktioniert einwandfrei und ohne Risiken.

Wir haben eine eigene Aggregat-Funktion geschrieben, wie es im Internet auch mehrfach zu finden ist (z.B. hier) und verwenden diesen in unseren Select-Statements.

Neuer Object-Type: T_CLOB_AGGREGATOR

CREATE OR REPLACE TYPE t_clob_aggregator
AS OBJECT
(
aggregate_clob  CLOB,

STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT t_clob_aggregator )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate( self IN OUT t_clob_aggregator, value IN CLOB )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate( self IN t_clob_aggregator, returnValue OUT CLOB, flags IN number )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge( self IN OUT t_clob_aggregator, ctx2 IN t_clob_aggregator )
RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_clob_aggregator
IS

STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT t_clob_aggregator )
RETURN NUMBER
IS
temp_clob CLOB;
BEGIN

DBMS_LOB.CREATETEMPORARY( temp_clob, TRUE, DBMS_LOB.CALL );

sctx := t_clob_aggregator( aggregate_clob=>temp_clob );

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate( self IN OUT t_clob_aggregator, value IN CLOB )
RETURN NUMBER
IS
BEGIN
IF DBMS_LOB.GETLENGTH( self.aggregate_clob ) > 0 THEN
DBMS_LOB.APPEND( self.aggregate_clob, ‚#PLCHLD_END#’||’#PLCHLD_START#‘ );
END IF;

DBMS_LOB.APPEND( self.aggregate_clob, value );

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate( self IN t_clob_aggregator, returnValue OUT CLOB, flags IN number)
RETURN NUMBER
IS
BEGIN

returnValue := ‚#PLCHLD_START#’||self.aggregate_clob||’#PLCHLD_END#‘;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateMerge( self IN OUT t_clob_aggregator, ctx2 IN t_clob_aggregator )
RETURN NUMBER
IS
BEGIN

DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );

RETURN ODCIConst.Success;

END;
END;
/

Neue Aggregat-Funktion: CLOBAGG

CREATE OR REPLACE FUNCTION clobagg( input CLOB )
RETURN CLOB
PARALLEL_ENABLE AGGREGATE
USING t_clob_aggregator;
/

Verwendung der neuen Methoden und Funktionen:

SELECT REPLACE(REPLACE(‚<ul>’||clobagg(text)||'</ul>‘, ‚#PLCHLD_START#‘, ‚<li>‘), ‚#PLCHLD_END#‘, ‚</li>‘)
FROM (
SELECT text
FROM   test1
— Die Sortierung muss im inneren Statement erfolgen, damit es funktioniert!
ORDER BY id DESC
);

Wichtiger Hinweis!

Wir verwenden im Object-Type 2 Placeholder-Values, damit wir die Formatierungen später verändern können, wie wir es brauchen. Man könnte das auch beim Select-Statement direkt angeben, wobei man hier unbedingt die TO_CLOB-Konvertierung machen muss, da ansonsten wieder ein VARCHAR2(4000) zugrunde gelegt wird, was unseren Eingangsfehler verursachen würde:

SELECT ‚<ul>’||clobagg(TO_CLOB(‚<li>‘)||text||TO_CLOB(‚</li>‘))||'</ul>‘
FROM (
SELECT text
FROM   test1
— Die Sortierung muss im inneren Statement erfolgen, damit es funktioniert!
ORDER BY id DESC
);

 

Kategorien:Oracle 11g, Oracle 12c Schlagwörter: , ,

301 Redirect mit Oracle Apex?

Warum der 301-Redirect?

Wird eine Web-Anwendung im Internet veröffentlicht tauchen ganz neue Aspekte auf, die im Intranet keine Rolle spielen. Ein zentraler Aspekt ist der Umgang der Anwendung mit Google & Co.

Ist zum Beispiel eine veraltete URL nach einer Zeit nicht mehr in der Anwendung verfügbar, muss man davon ausgehen, dass Suchmaschinen die Seite trotzdem noch indiziert haben. Dies muss noch nicht einmal eine komplette Page sein, sondern kann auch nur ein Produkt sein, das nicht mehr im Stock des Onlineshops vorhanden ist. Dabei hilft es auch nicht die sitemap.xml zu aktualisieren. Das Sperren der URL über die robots.txt ist zwar im Prinzip möglich, allerdings auch sehr schwerfällig.

In solchen Fällen rät Google & Co dem Webmaster, einen 301-Redirect für die URL (moved permenantly) einzurichten. Dies geschieht zum Beispiel über eine mod_rewrite-Regel im Apache. Im Falle einer Oracle Apex-Anwendung ist dies allerdings auch zu unflexibel. Besser ist ein 301 Redirect aus der Anwendung heraus.

Kein 301 Redirect mit owa_util

Im Prinzip sollte ein 301 Redirect über owa_util möglich sein, dies scheitert aber an der aktuellen Implementierung:

Verschiedene Quellen in Web schlagen folgendes Coding vor

owa_util.status_line(301, null,FALSE);
owa_util.redirect_url('https://apextipps.wordpress.com', TRUE);

Erzeugt man einen entsprechenden Page-Prozess als „before header“-Prozess, zeigt sich beim Aufruf der Seite , dass trotzdem ein 302 Redirect (Moved Temporarily) zum Client gesendet wird. Ein schönes Tool, um das Redirect-Verhalten zu kontrollieren ist z.B. das Firfox-Plugin Tampa Data

Dieses Verhalten zeigt sich sowohl in der Kombination Oracle XE + Apex 3.2.1 als auch mit Oracle 11g + Apex 4

Die Alternative: 410er Get mit Meta-Redirect

Als Alternative zum 301-Redirect gibt es folgenden Workaround, der in beiden Oracle/Apex – Kombinationen funktioniert hat und den gleichen Effekt für Google & Co hat:

  1. Die URL, die aus dem Google-Index gelöscht werden soll, schickt den Status-Code 410 (Gone) zurück. Eine 410er-Seite muss zum Glück keinen bestimmten Aufbau haben. Das erleichtert die Programmierung in Apex
  2. Um trotzdem einen Redirect zum Client zu senden, falls die URL noch nicht aus dem Index gelöscht wurde wird ein Meta-Redirect verwendet.

Folgendes Coding kann für den Page-Prozess (wieder Before-Header) dafür verwendet werden:

-- 410 instead of 200 indicates that the page should be dropped
-- from any crawler index:
owa_util.status_line(410, 'Gone',FALSE);
-- send a meta-redirect in case a client still ends up on the page:
htp.p('Refresh: 0; url=https://apextipps.wordpress.com');
owa_util.http_header_close;


Kleiner Schönheitsfehler: Der Meta-Redirect hat immer eine Latenzzeit von 1 Sek. Der Client bekommt also kurz die 410er Seite angezeigt.

Kategorien:APEX 3, APEX 4, Oracle 11g, Oracle XE Schlagwörter: