Plsql

Aus XmasWiki
Wechseln zu: Navigation, Suche

Datenbanksprache von Oracle.
Wird unter anderem in Oracle Portal oder APEX benutzt.

Inhaltsverzeichnis

Allgemeine Infos

SQL Plus

c:\sqlplus /nolog
SQL>conn sys/pwd@tnsnameseintrag as sysdba

Datenbankinformationen abfragen

SELECT name, description, value FROM v$parameter;
SELECT value FROM v$parameter WHERE name = 'db_name';
SELECT name FROM v$database;
SELECT * FROM global_name;

Gute Infoseiten

Kill Session

Syntax:

ALTER SYSTEM KILL SESSION 'SID, SERIAL#' IMMEDIATE;

Beispiel:

ALTER SYSTEM KILL SESSION '95, 10457' IMMEDIATE;

Mögliche Abfrage aller Sessions:

select x.SID
     ,x.serial#
     ,x.USERNAME
     ,x.OSUSER
     ,x.LOCKWAIT
     ,x.PROGRAM
     ,x.client_identifier
     ,x.module
     ,x.action
     ,o.object_name
from v$session x
join dba_objects o on o.object_id = x.ROW_WAIT_OBJ#
where x.LOCKWAIT is not null
order by x.client_identifier

oder

select * from v$locked_object l join dba_objects o on o.object_id = l.OBJECT_ID

Links

Quotation / Trenner

q'# = '#' => = '

Tablespace Informationen abfragen

select *  from database_properties
where property_name like '%TABLESPACE%';

select * from v$datafile;

http://vsbabu.org/oracle/sect03.html
http://www.psoug.org/reference/tablespaces.html

Tablespace bearbeiten

http://de.wikibooks.org/wiki/Oracle:_Tablespace_verwalten

User

  • Abfragen aller User in der Datenbank:

select distinct owner from all_tables

User anlegen

  • Zuerst den Tablespace für den Benutzer anlegen

CREATE TABLESPACE test_user
DATAFILE 'C:\oraclexe\oradata\XE\test_user.dbf' SIZE 10M
AUTOEXTEND ON NEXT 200K  MAXSIZE 200M;

oder unter linux

CREATE TABLESPACE test_user
DATAFILE '/u01/app/oracle/oradata/db11gutf8/test_user.dbf' SIZE 10M
AUTOEXTEND ON NEXT 200K  MAXSIZE 200M;


  • Danach den Benutzer selbst anlegen. Es empfiehlt sich bei User und Tablespace die gleiche Bezeichnung zu wählen damit das ganze transparent bleibt. Ein eigener Tablespace lohnt sich bei größeren Datenmengen bzw. wenn das ganze einfach pro Tablespace per Datapump gesichert werden soll.

CREATE USER test_user IDENTIFIED BY passwort
DEFAULT TABLESPACE test_user
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON test_user;

  • Zum Schluss noch Rechte geben zum Verbinden und zur Erstellung der Objekte geben

grant connect, create session, create table, create procedure, create trigger, create sequence, create view to test_user;

Debug Rechte für die Entwicklung vergeben

grant debug connect session to user;

Löschen von Objekten

löschen vom User

DROP user <user> CASCADE;

löschen vom Tablespace mittels:

DROP TABLESPACE test_user INCLUDING CONTENTS AND DATAFILES;

Import & Export mit expdp und impdp (Datapump)

Mit den Datapumpfunktionen expdp und imdp kann man eine komplette Datenbank, einzelne Schemata oder Daten unabhängig von den Objekten exportieren und importieren. Bevor man einen Export durchführt muss man einmal ein directory anlegen und danach kann man dann den Export über dieses Directory durchführen. http://www.morganslibrary.org/reference/datapump.html Hier die Anweisungen

sqlplus /nolog
SQL> conn sys as sysdba
SQL> create directory expdp_dir as '/u01/backup/exports';
SQL> exit
expdp system/<password> DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og

Danach kann man einen Import auch mit einzelnen Schemata gezielt durchführen, wobei man daran denken sollte, dass das expdp_dir dann dort auch vorhanden sein muss.

impdp system/<password> DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,o

weitere Parameter:

  • keine Berechtigungen exportieren => EXCLUDE=GRANT
  • Schemata remappen => schema_remap=OLD:NEW

SELECT, UPDATE, DELETE, INSERT und MERGE

<source lang="php"> SELECT name FROM test WHERE vorname = 'Harry' ORDER BY nachname; </source>

UPDATE test SET vorname='Harry', nachname='Huber' WHERE id = 3;

DELETE FROM test WHERE id = 3;

INSERT INTO test (vorname, nachname) VALUES ('Harry', 'Müller') RETURNING id INTO l_id;

MERGE INTO annsal a

 USING emp e
 ON (a.empno = e.empno)
 WHEN MATCHED THEN 
   UPDATE SET a.peryear = (e.sal *12 + NVL(e.comm,0))
 WHEN NOT MATCHED THEN 
   INSERT (a.empno, a.peryear) VALUES (e.empno, e.sal *12 + NVL(e.comm,0))

MERGE INTO arzt arzt

         USING (SELECT l_arzt.lanr lanr, l_arzt.titel titel, l_arzt.vorname vorname, l_arzt.nachname nachname
                , l_arzt.kv_id kv_id, l_arzt.kostentraeger_id kostentraeger_id
                FROM dual) imp
         ON (arzt.lanr = imp.lanr AND arzt.kostentraeger_id = imp.kostentraeger_id)
         WHEN MATCHED THEN 
           UPDATE SET arzt.titel = imp.titel, arzt.vorname = imp.vorname, arzt.nachname = imp.nachname
         WHEN NOT MATCHED THEN
           INSERT (lanr, titel, vorname, nachname, kassenaerzt_ver_id, kostentraeger_id) 
           VALUES (imp.lanr, imp.titel, imp.vorname, imp.nachname, imp.kv_id, imp.kostentraeger_id);

Cursor

Cursor mittels eine Anfrage in einer Varchar2 Variable

TYPE     t_fehler IS REF CURSOR;
   c_fehler t_fehler;
   l_return VARCHAR2(200);
   l_sql    VARCHAR2(4000);
   l_fehler VARCHAR2(30);
 BEGIN
   l_sql := 'SELECT SPALTE FROM ' || p_fehlertabelle || ' WHERE import_id = ' || p_import_id;
   
   OPEN c_fehler FOR l_sql;
   LOOP
     FETCH c_fehler INTO l_fehler;
     EXIT WHEN c_fehler%NOTFOUND;
     IF l_return IS NULL
     THEN
       l_return := l_fehler;
     ELSE
       l_return := l_return || ', ' || l_fehler;
     END IF;
   END LOOP;

Collections

TYPE type_name IS TABLE OF element_type [NOT NULL];


Beispiel (ein name besteht aus 50 Buchstaben):

TYPE name IS TABLE OF varchar2(50);


http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-10.htm
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm
http://it.toolbox.com/blogs/oracle-guide/oracle-collections-a-definition-in-plain-english-part-1-5924
Array in PLSQL http://www.datenbank-plsql.de/tables.htm

Interessante Tabellen/Views

  • dba_errors / all_errors => Fehlerhafte Anfragen
  • v$sqlarea => alle Anfragen

Interessante Funktionen

Carriage return line feed / Umbruch

utl_tcp.crlf

Result Cache

Durch den Result Cache wird bei gleichen Input Parametern, der Wert gecached. So wird die Funktion nur beim ersten Aufruf ausgeführt, danach wird das gleiche Ergebnis sofort zurückgegeben.

CREATE FUNCTION get_default (
  p_param in varchar2
) RETURN VARCHAR2 RESULT_CACHE;

oder mit RELIES_ON (auf welchen Tabellen basiert der Cache)

CREATE FUNCTION get_default (
  p_param in varchar2
) RETURN VARCHAR2 RESULT_CACHE RELIES_ON (test_tab);

Ausgabe von Kalendertagen

Hier eine Möglichkeit alle Tage innerhalb eines Quartals auszugeben. Dabei wurden die Wochenende rausgelassen und die Nummer des Tages im Quartal wurde vorran gestellt.

select rownum tag, to_date (:P3_QUARTALSBEGINN, 'dd.mm.yyyy') + level - 1 datum                    
from dual
where to_char(to_date(:P3_QUARTALSBEGINN, 'dd.mm.yyyy') + level - 1,'d') not in (6,7)
connect by level <= (to_char(last_day(to_date(:P3_QUARTALSBEGINN, 'dd.mm.yyyy')),'dd') +
                    to_char(last_day(add_months(to_date(:P3_QUARTALSBEGINN, 'dd.mm.yyyy'),1)),'dd') +
                    to_char(last_day(add_months(to_date(:P3_QUARTALSBEGINN, 'dd.mm.yyyy'),2)),'dd')
                   )

Quartalsbeginn ermitteln

select  to_date('01.' || (3 * x.q - 2) || '.' || to_char(sysdate,'yyyy'),'dd.mm.yyyy') tag1
from (select to_char(sysdate,'Q') q from dual) x

Boolean in Integer umwandeln

diutil.bool_to_int(boolean);

Zeile und Quelle einer Fehlermeldung

  • $$plsql_unit => Package
  • $$plsql_line => Zeile

IN / NOT IN

Mit dieser Funktion kann man innerhalb einer Abfrage oder bei plsql eine Menge vergleichen auf ihre enthaltenen Elemente. Wichtig dabei ist, dass bei der Subquery keine Elemente NULL sein dürfen da sonst immer false zurückgegeben wird. Daher sollte eine Subquery so aussehen: NOT IN (SELECT name FROM personen WHERE name IS NOT NULL). Der NULL Fall muss ggf. extra behandelt werden bzw NULL muss mit IS verglichen werden. (3-wertige Logik)

CHR

Funktion für Sonderzeichen: CHR

  • Zeilenumbruch: chr(10) / chr(13)
  • Leerzeichen: CHR(39)

Reguläre Ausdrücke / regular expression

Hierzu existieren die Funktionen regexp_like, regexp_instr und regexp_replace.
Beispiele für reguläre Ausdrücke:
4-stellige Eingabe: ^\[\[:alnum:\]\]{4}$
siehe http://www.psoug.org/reference/regexp.html hilfreicher link http://www.oracle.com/technetwork/de/community/301666.html

Rank

mit rank() kann man innerhalb einer Anfrage die Rangfolge ausgeben lassen.

SELECT
RANK() OVER (ORDER BY SAL) AS RANK_NUM
,ename
FROM emp;

to_date / to_char

Ermöglicht die Umrechnung von Datum zu String/Varchar und wieder zurück. Durch Anpassung des Formats wird bessere Lesbarkeit oder Berechenbarkeit ermöglicht. Neben Kürzel die Programmiersprachen übergreifend verwendet werden (DD = Tag 2-stellig, MM = Monat 2-Stellig, YYYY = Jahr 4-stellig, HH24 = Stunden, MI = Minuten, SS = Sekunden) gibt es noch spezielle Formatkürzel wie: FM (vorlaufende Leerzeichen und Nullen entfernen).

to_date('01.01.2008', 'DD.MM.YYYY');
to_char(SYSDATE, 'DD.MM.YYYY');
to_char(SYSDATE, 'FMMM');

Lag / Lead

Ermöglicht Vorgänger oder Nachfolger eines bestimmten Attributs auszugeben. Hier wird zum Beispiel in den Bereichen ausgegeben wer jeweils die Person die vorher oder nachher eingestellt war.

SELECT 
       deptno
,      ename
,      hiredate
,      LAG(ename,1,NULL) OVER
         (PARTITION BY deptno
          ORDER BY hiredate ASC NULLS LAST) AS previous_employee_ename
,      LEAD(ename,1,NULL) OVER
          (PARTITION BY deptno
           ORDER BY hiredate ASC NULLS LAST) AS next_employee_ename
FROM   emp
ORDER  BY

  DEPTNO ENAME      HIREDATE    PREVIOUS_EMPLOYEE_ENAME        NEXT_EMPLOYEE_ENAME
---------- ---------- ----------- ------------------------------ ---------------------
       10 CLARK      09-JUN-1981                                KING
       10 KING       17-NOV-1981 CLARK                          MILLER
       10 MILLER     23-JAN-1982 KING
       20 SMITH      17-DEC-1980                                JONES
       20 JONES      02-APR-1981 SMITH                          FORD
       20 FORD       03-DEC-1981 JONES                          SCOTT
       20 SCOTT      09-DEC-1982 FORD                           ADAMS
       20 ADAMS      12-JAN-1983 SCOTT

wm_concat

Für die Zusammenfassung mehrerer Zeilen zu einem Wert kann die Funktion wm_concat sehr praktisch sein: <code>

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

Es wird über das Attribut ein Group by definiert, über welches die Daten in der zweiten Spalte zusammengefasst werden.

Fachvokabular und theoretische Ansätze

Phonetische Suche - Oracle Text

Da es öfters von Interesse ist ähnliche Worte in einer Datenbank abzufragen ist die phonetische Suche ein mächtiges Hilfsmittel (http://de.wikipedia.org/wiki/Phonetische_Suche). In plsql sieht es so aus:
... where contains(name, '!meinesuche') > 0 Dafür muss man aber zunächst einen Index auf die Spalte name legen damit man mit Oracle Text suchen kann. Dazu findet man hier mehr:
Oracle Text Dokumentation http://download-uk.oracle.com/docs/cd/B19306_01/text.102/b14217/ind.htm
Beispiel einer Suche für APEX http://www.oracle.com/global/de/community/tipps/adressen_text/index.html
Allgemeines Beispiel http://www.blogler.de/sec/100010/blogcfm/1/2007/02/Linguistische-Phonetische-Suche-mit-Oracle-Text.cfm

Um nun innerhalb dieser Ergebnismenge sortieren zu können kann man die "Score" Funktion benutzen.
select name,SCORE(0) from meintable where contains(name, '!meinesuche',0) > 0 order by score(0)
Dies kann man hier in kürze nachlesen: http://www.blogler.de/sec/100010/blogcfm/1/2008/02/ORA29908-missing-primary-invocation-for-ancillary-operator.cfm

Weiter kann man bei der Verwendung von contains das gesuchte Wort in folgenden Schreibweisen benutzen:

  1. contains(name, 'meinesuche') -> sucht exakt nach dem Wort meine Suche im Feld name
  2. contains(name, '?meinesuche') -> lässt kleinere Abweichungen zu (zum Beispiel statt einem "i" ein "y").
  3. contains(name, '!meinesuche') -> lässt größere Abweichungen zu (zum Beispiel statt einem "ei" ein "ay").

Bitte beachten, dass diese Informationen unvollständig und nicht 100%ig richtig sein können.

Beispiel

SELECT
first_name,
last_name
from address
where contains(last_name, '!Meier' ,0) > 0
order by decode(last_name, 'Meier', 'aaa',last_name)

Execute Immediate

Mit Execute Immediate kann man Anfragen die innerhalb von plsql zusammgengebaut werden und welche in Variablen gespeichert werden direkt an die Datenbank abschicken.
l_query := 'SELECT ...';
EXECUTE IMMEDIATE Command RETURNING INTO l_result;

Update über ROWID

Mittels ROWID kann man direkt Zeilen updaten ohne Id's zu verwenden. Dadurch sollte das Update performanter sein, falls man direkt mit SELECT .. UPDATE arbeitet.

Explain Plan

Der Explain Plan zeigt an wie die SQL-Anfrage intern von Oracle abgearbeitet wird. Mit verschiedenen Anpassungen kann man durch eine Analyse und Anpassung des Explain Plans Abfragen optimieren und schneller machen. Gerade bei sehr großen Tabellen sollte man durch geschicktes Benutzen von Indizes oder Partitionierung Optimierung betreiben.

Bulk Collect / Bulk Binds

Sorgt bei Anfragen, für die Bearbeitung der Anfrage größtenteils durch die SQL Engine und übersetzt nicht zur pl/sql Engine. Ist ca. 100mal schneller bei größeren Datenmengen.
Ein Beispiel für bulk collect. select trx_id, trx_qty, trx_cost * trx_qty bulk collect

 into   l_pndidr, l_pndqty, l_pndval
from mtl_pending_trx;


Während bulk collect sachen abfragt und eine temporäre Tabelle erstellt wird bulk bind dazu benutzt große Datenmengen "upzudaten" oder einzutragen ohne das der Wechsel zwischen pl/sql und sql engine stattfindet.

Partioning

Beim Partioning kann man in Anfragen nach "Gruppen" sortieren und dies für Berechnungen nutzen.

Materialized View - MV

Ein MV stellt eine Möglichkeit dar, Daten repliziert abzubilden. Man kann also gerad bei komplexen Datenstrukturen diese in einer MV in einer einzigen Tabelle darstellen und so die Anfrage wesentlich schneller machen. Der Inhalt einer MV wird dabei je nach Anforderung sofort oder zu einem bestimmten Zeitpunkt aktualisiert. Hier ein einfacher MV

create materialized view NAME_MV
refresh force on demand
as
select * from (select * from schema.table@dblink);

Falls man den Inhalt eines Views von Hand aktualisieren will benöätigt man folgenden Aufruf:

begin
DBMS_SNAPSHOT.REFRESH( 'name_mv','c');
end;

Dabei steht das c für complete. Es gibt auch die Möglichkeit f für Fast zu verwenden. Will man alle MV's in einem Schema aktualisieren kann man DBMS_SNAPSHOT.REFRESH_ALL verwenden. Eine automatische Aktualisierung könnte auch so aussehen:

CREATE MATERIALIZED VIEW name_mv
REFRESH FAST START WITH SYSDATE NEXT trunc(SYSDATE) + 1
AS
select ....

Hier würde der MV immer um Mitternacht aktualisiert und das täglich.

Erweiterte Features

Java in der Datenbank

http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b12021/dbms_java.htm

declare

 v_grantee constant varchar2(30) := 'RSS_DOAG';

begin

dbms_java.grant_permission(

   grantee =>           v_grantee, 
   permission_type =>   'SYS:java.io.FilePermission', 

-- permission_name => '<<ALL FILES>>',

   permission_name =>   '/bin/mkdir',
   permission_action => 'execute' 
 );

dbms_java.grant_permission(

   grantee =>           v_grantee,
   permission_type =>   'SYS:java.io.FilePermission',
   permission_name =>   '/oracle/oracle/product/10.2.0/Apex/Apache/Apache/htdocs/rss_doag/*',
   permission_action => 'write'
 );

dbms_java.grant_permission(

   grantee =>           v_grantee,
   permission_type =>   'SYS:java.lang.RuntimePermission',
   permission_name =>   'readFileDescriptor',
   permission_action => null
 );
 dbms_java.grant_permission(
   grantee =>           v_grantee,
   permission_type =>   'SYS:java.lang.RuntimePermission',
   permission_name =>   'writeFileDescriptor',
   permission_action => null
 );

end;

Datenbankinformationen

Charset

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#i669888

SELECT * FROM nls_database_parameters 
WHERE parameter in ('NLS_CHARACTERSET','NLS_ISO_CURRENCY','NLS_LANGUAGE');

  • NLS_LANGUAGE = AMERICAN / GERMAN
  • NLS_ISO_CURRENCY = AMERICA / GERMANY
  • NLS_CHARACTERSET = AL32UTF8 / WE8MSWIN1252

Charset für die Dateiverarbeitung mittels utl_raw

utl_raw.convert(r => variable  , to_charset => 'GERMAN_GERMANY.AL32UTF8', from_charset => 'GERMAN_GERMANY.WE8MSWIN1252')

Bei Konvertierung von Varchar2/String reicht die convert Funktion.

convert(src => 'öööö', destcset => ('WE8ISO8859P1'));

Datenbankversion

select * from v$version;

Autonome Transaktionen

Im Procedure/Function Header folgendes:

procedure / function bla()
is
  pragma autonomous_transaction;
begin
  <do something>
  commit;
exception
  when others then
    <do something other>
    commit;
end;

und nachher muss ein COMMIT; ausgeführt werden! Dabei daran denken, dass auch im Exception Handling ein Commit durchgeführt wird.

Clob/Blob

CLOB ausgeben

declare 
 l_clob clob;
 l_offset number := 1;
begin
 l_clob := get_my_clob;
                                            
 sys.htp.htbuf_len := 63;
 loop
   exit when l_offset > dbms_lob.getlength(l_clob);
   
   sys.htp.prn(dbms_lob.substr(lob_loc => l_clob
                              ,amount  => 255
                              ,offset  => l_offset
                              )
              );
              
   l_offset := l_offset + 255;
 end loop;
 
exception
 when others then
   htp.prn('CLOB konnte nicht ausgegeben werden.');
end;

CLOB => BLOB

APEX Package wwv_flow_utilities.

v_blob := wwv_flow_utilities.clob_to_blob(p_clob    => v_clob
                                         ,p_charset => 'WE8MSWIN1252'
                                         );

BLOB => CLOB

DECLARE
 temp_blob   BLOB;
 temp_clob   CLOB;
 dest_offset NUMBER  := 1;
 src_offset  NUMBER  := 1;
 amount      INTEGER := dbms_lob.lobmaxsize;
 blob_csid   NUMBER  := dbms_lob.default_csid;
 lang_ctx    INTEGER := dbms_lob.default_lang_ctx;
 warning     INTEGER;
BEGIN
 DBMS_LOB.CREATETEMPORARY(lob_loc=>temp_blob, cache=>TRUE, dur=>dbms_lob.SESSION);
 DBMS_LOB.CREATETEMPORARY(lob_loc=>temp_clob, cache=>TRUE, dur=>dbms_lob.SESSION);

 DBMS_LOB.CONVERTTOCLOB(temp_clob,temp_blob,amount,dest_offset,src_offset,blob_csid,lang_ctx,warning);
END;

http://www.psoug.org/reference/utl_file.html

Unit Tests in plsql

Dazu gibt es folgenden Link http://utplsql.sourceforge.net/
Ähnlich wie auch in anderen Programmiersprachen definiert man Testfälle und überprüft das Ergebnis dieser Testfälle. Stimmt das Ergebnis mit dem zu erwartendem Ergebnis überein wird eine positive Meldung erzeugt. Solche Unit Tests sind immer dann praktisch, wenn man Tests automatisieren will und während Änderungen am Code vorgenommen werden um zu gewährleisten, dass sich keine Fehler wieder einschleichen.

UTL_FILE

Um darauf zuzugreifen muss man in der Express Edition die Berechtigung für den jeweiligen Benutzer erstellen:
grant execute on sys.utl_file to my_user;
utl_file.fopen(location => path,

                   filename => filename,
                   open_mode => read_write_append,
                   max_linesize => 32767);

owa_util

http://www.acs.ilstu.edu/docs/oracle/server.101/b12303/concept.htm

owa_util.get_cgi_env(param_name in varchar2) return varchar2;

  • HTTP_AUTHORIZATION
  • DAD_NAME
  • DOC_ACCESS_PATH
  • HTTP_ACCEPT
  • HTTP_ACCEPT_CHARSET
  • HTTP_ACCEPT_LANGUAGE
  • HTTP_COOKIE
  • HTTP_HOST
  • HTTP_PRAGMA
  • HTTP_REFERER
  • HTTP_USER_AGENT
  • PATH_ALIAS
  • PATH_INFO
  • HTTP_ORACLE_ECID
  • DOCUMENT_TABLE
  • REMOTE_ADDR
  • REMOTE_HOST
  • REMOTE_USER
  • REQUEST_CHARSET (refer to Section 1.9.2.1, "REQUEST_CHARSET CGI Environment Variable")
  • REQUEST_IANA_CHARSET (refer to Section 1.9.2.2, "REQUEST_IANA_CHARSET CGI Environment Variable")
  • REQUEST_METHOD
  • REQUEST_PROTOCOL
  • SCRIPT_NAME
  • SCRIPT_PREFIX
  • SERVER_NAME
  • SERVER_PORT
  • SERVER_PROTOCOL

Client IPAdresse

  • owa_util.get_cgi_env('REMOTE_ADDR')
  • sys_context('userenv', 'ip_address')

LDAP Anfragen

v_bind_status := dbms_ldap.simple_bind_s(ld => v_ldap_session
                                        , dn => p_username
                                        , passwd => p_password
                                        );

  • Funktion query, ODCITableDescribe, ODCITablePrepare, ODCITableStart mit p_username und p_password erweitern.
  • Anfrage dann mit username = Nachname, Vorname und entsprechendem LDAP Passwort.


Senden von Mails über pl/SQL

UTL_MAIL The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package, but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts:

   CONN sys/password AS SYSDBA
   @$ORACLE_HOME/rdbms/admin/utlmail.sql
   @$ORACLE_HOME/rdbms/admin/prvtmail.plb


In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server:

   CONN sys/password AS SYSDBA
   ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
   SHUTDOWN IMMEDIATE
   STARTUP


With the configuration complete we can now send a mail using:

   BEGIN
     UTL_MAIL.send(sender     => 'me@domain.com',
                   recipients => 'person1@domain.com,person2@domain.com',
                   cc         => 'person3@domain.com',
                   bcc        => 'myboss@domain.com',
                   subject    => 'UTL_MAIL Test',
                   message    => 'If you get this message it worked!');
   END;
   /


The package also supports sending mails with RAW and VARCHAR2 attachments.

http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php
http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10802/u_mail.htm#996728
http://www.scribd.com/doc/266008/Sending-email-from-an-Oracle-10g-Server

Abfragen von Webservices

Abfrage

  • XML Struktur erzeugen die ca. so aussieht:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:entrust.com:ig:authenticationV5:wsdl">
 <soapenv:Header/>
  <soapenv:Body>
   <urn:getAnonymousChallenge>
   </urn:getAnonymousChallenge>
  </soapenv:Body>
 </soapenv:Envelope>

Dabei sollte man auf den Namespace achten (Einträge hinter xmlns). Bei xmlns:soagenv steht theoretisch immer das gleiche und bei xmlns:urn steht was spezifisches je nach Webservice. Die Webservice XML Struktur beinhaltet immer ein Evelope und darin ist ein Header und Body Element. Innerhalb des Bodys wird dann die Methode angegeben die aufgerufen wird. Hier getAnonymousChallenge. Werden weitere Attribute benötigt für diese Methode sind diese dann dort nochmal enthalten.

  • Aus meiner Sicht empfiehlt es sich die komplette XML-Struktur dann in eine DEBUG_WEBSERVICE TAbelle zu schreiben und bei jeder Anfrage zu wissen was geschickt wurde.
  • Für SSL Kommunikation muss ein Walletpfad hinzugefügt werden. Dieser wird in der Datenbank definiert und es ist wichtig den Pfad zu wissen. Mittels der Methode

utl_http.set_wallet( path => '/usr/oracle/wallet');

wird dies gesetzt.

  • Dann wird der Request (v_http_request utl_http.req) erstellt

v_http_request := utl_http.begin_request( url          => 'https://addresse.de:8443/IdentityGuardAuthService/services/AuthenticationServiceV5'
                                        , method       => 'POST'
                                        , http_version => 'HTTP/1.1'
                                        );

  • Weitere Parameter werden hinzugefügt

   utl_http.set_header( r     => v_http_request
                      , name  => 'Content-Type'
                      , value => 'text/xml'
                      );
   utl_http.set_header( r     => v_http_request
                      , name  => 'Content-Length'
                      , value => length(v_envelope)
                      );
   utl_http.set_header( r     => v_http_request
                      , name  => 'SOAPAction'
                      , value => p_action
                      );
   utl_http.write_text( r    => v_http_request
                      , data => v_envelope
                      );

  • Danach wird mittels des Requests die Antwort/Response (v_http_response utl_http.resp;) geholt:

v_http_response := utl_http.get_response(r => v_http_request);

  • Danach sollte aus dem XML Konstrukt der Antwort, die entsprechenden Parameter ausgewertet werden.
  • Es empfiehlt sich eine Mehtode check_fault zu erstellen, die vor der Extraktion der Antwort, diese auf Fehler überprüft und ggf. den Fehler dann extrahiert.

Oracle 9i

http://www.oracle-base.com/articles/9i/ConsumingWebServices9i.php

10G

API: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_dbws.htm
Info: http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html
Beispiel: http://www.oracle-base.com/articles/10g/utl_dbws10g.php
Installationsanleitung: http://forums.oracle.com/forums/thread.jspa?threadID=633268&tstart=15
Webservice Testtool: http://www.soapui.org

User Berechtigung auf das Package geben:
GRANT execute ON utl_dbws TO test;

Abfragen von XML

select count(*) 
from webservice_hilfstabelle h
, table(XMLSequence(xmltype.createxml(
         replace(h.content,
                 ' xmlns="urn:entrust.com:ig:authenticationV5:wsdl"')
                ).extract('//getAllowedAuthenticationTypesReturn/genericAuth/item')))
where h.id = 57

  • Einträge in Kindknoten abfragen (pro Eintrag eine Zeile)

select extractvalue(value(v),'/item/Row') zeile
, extractvalue(value(v),'/item/Column' ) spalte
from table(XMLSequence(v_xml.extract('//getGenericChallengeReturn/GridChallenge/challenge/item'))) v

  • Oder die Abfrage des Inhalts des ersten Knotens

select extractvalue(value(v),'/item/Row') zeile
, extractvalue(value(v),'/item/Column' ) spalte
from table(XMLSequence(v_xml.extract('//getGenericChallengeReturn/GridChallenge/challenge/child::item[position()=1]'))) v

Erzeugen von XML

Beispiel: http://forums.oracle.com/forums/thread.jspa?threadID=411843
Beispiel: http://forums.oracle.com/forums/thread.jspa?messageID=1346980
Beispiel: http://forums.oracle.com/forums/thread.jspa?threadID=642264&tstart=0
Oracle Forum: http://forums.oracle.com/forums/forum.jspa?forumID=157&start=0
API: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb10pls.htm#sthref1337
XML Type http://www.ordix.de/ORDIXNews/1_2007/Datenbanken/xmltype.html Pretty Print http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#sthref469

Erzeugen mit SQL

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612
Hier Ein Beispiel wie man ein RSS Feed Struktur aus der EMP Tabelle erzeugen kann:

SELECT  XMLRoot(xmlelement("rss", XMLAttributes('2.0' as "version"), 
         xmlelement("channel",
           xmlelement("title", ),
           xmlelement("link", ),
           xmlelement("description",),
           xmlelement("language", ),
           xmlelement("copyright", ),
           xmlelement("category",),
           xmlagg(
             xmlelement("item", 
               xmlelement("title", dname), 
               xmlelement("description", loc), 
               xmlelement("link", 'http://www.mt-ag.com?id=' || deptno),
               xmlelement("pubdate", to_char(SYSDATE, 'DD.MM.YYYY'))
             )
           )
         )
       ), VERSION '1.0') rss FROM DEPT

Erzeugen mit DBMS_XMLDOM