26. September 2007

LDAP-Server abfragen ... mit SQL

In vielen Unternehmen haben LDAP-Server eine zentrale Bedeutung - nahezu alle Mitarbeiter sind dort mit ihren Kontaktdaten abgelegt - So kann es vorkommen, dass man aus der Datenbank heraus auf diese Informationen zugreifen möchte. Und die Oracle-Datenbank bringt dazu auch ein Hilfsmittel mit: DBMS_LDAP. Mit diesem PL/SQL-Paket kann man auf LDAP-Directories zugreifen und dort Informationen abrufen oder manipulieren. Allerdings ist es recht aufwändig, mit diesem Paket zu programmieren ... viel hübscher wäre es, wenn man mit einem SQL SELECT auf die Informationen zugreifen könne - dies würde die Aufbereitung der Daten bspw. in einer Web-Anwendung (Application Express-Bericht) massiv vereinfachen.
Der folgende Code ermöglicht genau dies. Die LDAP-Abfrage wird durch eine Table Function durchgeführt; diese Funktionen liefern keine skalaren Werte, sondern eine ganze Tabelle zurück (mehr zu Table Functions in diesem Blog). Es gibt hier aber noch eine Besonderheit zu beachten: Normalerweise muss man die "Rückgabetabelle" der Table Function durch einen Objekttypen (CREATE TYPE) festlegen und so beschreiben. Das folgende Beispiel geht etwas anders vor: Mit Hilfe des Oracle Data Cartridge Interface (ODCI) kann man ebenfalls Table Functions bauen und hier muss man vorher keine Objekttypen erstellen.
Wenn DBMS_LDAP in der Datenbank nicht vorhanden ist, kann der DBA es mit $ORACLE_HOME/rdbms/admin/catldap.sql einspielen. Doch genug der Vorrede: hier der Code

drop type ldapquery
/


CREATE type ldapquery as object(
  ldap_host        varchar2(4000),
  ldap_port        number,
  ldap_session     raw(32),
  ldap_resultset   raw(32),

  q_search_base    varchar2(4000), 
  q_search_filter  varchar2(4000), 
  q_return_fields  varchar2(4000),
  q_timeout        number,

  row_types          anytype,
  last_entry_fetched number,

  static function query(
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number default 3
  ) return anydataset pipelined using ldapquery,

  static function ODCITableDescribe(
    record_table       out anytype,
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number
  ) return number,

  static function ODCITablePrepare (
    sctx               out ldapquery, 
    tab_func_info      in sys.ODCITabFuncInfo,
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number
  ) return number,

  static function ODCITableStart   (
    sctx               in out ldapquery, 
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number
  ) return number, 

  member function ODCITableFetch   (
    self               in out ldapquery,
    nrows              in number, 
    record_out         out anydataset
  ) return number,
  
  member function ODCITableClose   (
    self               in ldapquery 
  ) return number


)
/


create or replace package ldapquery_helper is
  function tokenize(
    p_string    in varchar2, 
    p_delim     in varchar2 default ','
  ) return dbms_ldap.String_collection;
end ldapquery_helper;
/

create or replace package body ldapquery_helper is
  function tokenize(
    p_string    in varchar2, 
    p_delim     in varchar2 default ','
  ) return dbms_ldap.String_collection is
    v_startpos  pls_integer := 1;
    v_comma_pos pls_integer;
   
    v_result_array dbms_ldap.string_collection;
    v_array_index  pls_integer := 0;
  begin 
    while v_startpos != 0 loop
      v_comma_pos := instr(p_string, p_delim, v_startpos);  
      if v_comma_pos != 0 then 
        v_result_array(v_array_index) := substr(p_string, v_startpos, v_comma_pos - v_startpos);
        v_startpos := v_comma_pos + 1;
      else 
        v_result_array(v_array_index) := substr(p_string, v_startpos);
        v_startpos := v_comma_pos;
      end if;
      v_array_index := v_array_index + 1;
    end loop;
    return v_result_array;
  end tokenize;
end ldapquery_helper;
/


CREATE or replace type BODY ldapquery as
 static function ODCITableDescribe(
    record_table       out anytype,
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number
  ) return number as
    v_record_structure anytype; 
   
    v_result_array dbms_ldap.string_collection;
  begin 
    v_result_array := ldapquery_helper.tokenize(p_return_fields);

    anytype.begincreate(dbms_types.typecode_object, v_record_structure);

    for i in v_result_array.first..v_result_array.last loop
      v_record_structure.addattr(   
        ANAME     => v_result_array(i),
        TYPECODE  => dbms_types.typecode_varchar2,
        PREC      => null,
        SCALE     => null,
        LEN       => 4000,
        CSID      => null,    
        CSFRM     => null,
        ATTR_TYPE => null
      );
    end loop;
    
    v_record_structure.endcreate();

    anytype.begincreate(dbms_types.typecode_table, record_table); 

    record_table.setinfo(nullnullnullnullnull, v_record_structure, dbms_types.typecode_object, 0); 
    record_table.endcreate(); 

    return odciconst.success;
  
  exception when others then 
    -- indicate that an error has occured somewhere.
    return odciconst.error;
  end;   

  static function ODCITablePrepare (
    sctx               out ldapquery, 
    tab_func_info      in sys.ODCITabFuncInfo, 
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number 
  ) return number is
    prec         pls_integer; 
    scale        pls_integer; 
    len          pls_integer; 
    csid         pls_integer; 
    csfrm        pls_integer; 
    record_desc  anytype; 
    aname        varchar2(30); 
    dummy        pls_integer; 
  begin 
    dummy := tab_func_info.RetType.GetAttrElemInfo(null, prec, scale, len, csid, csfrm, record_desc, aname); 
    sctx := ldapquery(p_ldap_host, p_ldap_port, nullnull, p_search_base, p_search_filter, p_return_fields, p_timeout, record_desc, 0); 
    return odciconst.success; 
  end; 

  static function ODCITableStart   (
    sctx               in out ldapquery, 
    p_ldap_host        in varchar2,
    p_ldap_port        in number,
    p_search_base      in varchar2, 
    p_search_filter    in varchar2,
    p_return_fields    in varchar2,
    p_timeout          in number 
  ) return number is
    v_ldap_session   dbms_ldap.session;
    v_ldap_resultset dbms_ldap.message;
    v_bind_status    pls_integer;
    v_search_status  pls_integer;
    v_return_attrs   dbms_ldap.string_collection;
    v_timeout        dbms_ldap.timeval;
  begin 
    v_return_attrs := ldapquery_helper.tokenize(p_return_fields);
    v_timeout.seconds := p_timeout;
    v_timeout.useconds := 0;

    
    v_ldap_session := dbms_ldap.init(p_ldap_host, p_ldap_port);
    v_bind_status := dbms_ldap.bind_s(
      ld => v_ldap_session
     ,dn => null
     ,cred => null
     ,meth => dbms_ldap.AUTH_SIMPLE
    );

    v_search_status := dbms_ldap.search_st(
      ld         => v_ldap_session,
      base       => p_search_base,
      scope      => dbms_ldap.SCOPE_SUBTREE,
      filter     => p_search_filter,
      attrs      => v_return_attrs,
      attronly   => 0,
      res        => v_ldap_resultset,
      tv         => v_timeout
    );
    sctx.ldap_session := v_ldap_session;
    sctx.ldap_resultset := v_ldap_resultset;
    return odciconst.success; 
  end; 
 

  member function ODCITableFetch   (
    self               in out ldapquery,
    nrows              in number, 
    record_out         out anydataset
  ) return number is
    v_return_attrs dbms_ldap.string_collection;
    v_att_values   dbms_ldap.string_collection;
    v_field varchar2(4000);
  
    v_no_more_rows boolean := true;
  begin 
    record_out := null;
    
    v_return_attrs := ldapquery_helper.tokenize(q_return_fields);
    begin
      anydataset.begincreate(dbms_types.typecode_object, self.row_types, record_out); 
      record_out.addinstance;
      record_out.piecewise(); 
      for i in v_return_attrs.first..v_return_attrs.last loop
         v_att_values := dbms_ldap.get_values(
           ld        => ldap_session,
           ldapentry => ldap_resultset,
           attr      => v_return_attrs(i)
         );
         if v_att_values.exists(0) then
           v_field := v_att_values(0);
         else
           v_field := null;
         end if;
         record_out.setvarchar2(v_field);
      end loop;
      record_out.endcreate;
            
      ldap_resultset := dbms_ldap.next_entry(
       ld => ldap_session,
       msg => ldap_resultset
      );
    exception
      when others then 
        v_no_more_rows := false;
    end;
    if not v_no_more_rows then 
      begin
        record_out.endcreate;
      exception when others then null;
      end;
      record_out := null;
    end if;
    return odciconst.success; 
  end; 

  
  member function ODCITableClose   (
    self               in ldapquery 
  ) return number is
    v_ldap_session   dbms_ldap.session;
    v_unbind_status  pls_integer;
  begin
    v_ldap_session := ldap_session;
    v_unbind_status := dbms_ldap.unbind_s(
      ld => v_ldap_session
    );
    return odciconst.success; 
  end;
end;
/


Ausprobieren ist dann ganz einfach ...
select * from table(
  ldapquery.query(
    'ldapserv.mydomain.com',   -- Servername oder IP-Adresse
    389,                       -- TCP/IP-POrt
    '',                        -- Die Suche erfolgt ab diesem Eintrag im LDAP-Baum
    '(cn=Czarski*)',           -- Suchabfrage
    'cn,c,title',              -- Rückgabefelder
    3                          -- Timeout für Abfrage: 3 Sekunden
  )
);

cn                   c       title
-------------------- ------- --------------------------------------
Czarski,Carsten      de      Leitende/R Systemberater/In

1 Zeile wurde ausgewählt.

21. September 2007

PL/SQL in Oracle11g: Sequences direkt ansprechen ...

Eine weitere Verbesserung in Oracle11g ist die direkte Ansprache von Sequences. Während man bislang dazu stets ein SQL benötigte ...

declare
  v_number  number;
begin
  select sequence.nextval into v_number from dual;
end;
geht das nun einfacher ...

declare
  v_number  number;
begin
  v_number := sequence.nextval;
end;

14. September 2007

Neuer PL/SQL-Datentyp: SIMPLE_INTEGER

In Oracle11g gibt es für PL/SQL einige Verbesserungen und Erweiterungen in der Sprache ... eine davon ist der neue Datentyp SIMPLE_INTEGER - hierzu ein Beispiel:

set timing on

/*
 * Der Performancevorteil von SIMPLE_INTEGER ergibt sich nur bei NATIVE 
 * kompiliertem PL/SQL Code; daher wird PLSQL_CODE_TYPE auf "NATIVE"
 * gesetzt. Im Vergleich zu Oracle9i oder Oracle10g ist dies die einzige
 * Einstellung, die für Native-Compilation erforderlich ist.
 */

alter session set plsql_code_type='NATIVE'
/

create or replace procedure count_simpleinteger is
  a simple_integer := 0;
  b simple_integer := 0;
begin
  while a < 100000 loop
   while b < 10000 loop
    b := b + 1;
   end loop;
   a := a + 1;
   b := 0;
  end loop;
end;
/
sho err


create or replace procedure count_plsinteger is
  a pls_integer := 0;
  b pls_integer := 0;
begin
  while a < 100000 loop
   while b < 10000 loop
    b := b + 1;
   end loop;
   a := a + 1;
   b := 0;
  end loop;
end;
/
sho err

prompt '** COUNTING with PLS_INTEGER ...'
    
exec count_plsinteger

prompt '** COUNTING with the new SIMPLE_INTEGER ...'

exec count_simpleinteger

... und die Ausgabe ist dann ...

:
:

'** COUNTING with PLS_INTEGER ...'

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:03.44
'** COUNTING with the new SIMPLE_INTEGER ...'

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.92
An diesem Beispiel ist gut erkennbar: Arbeitet man mit "Native Compiled" PL/SQL, dann ist SIMPLE_INTEGER massiv schneller als PLS_INTEGER. Im interpretieren Modus ergibt sich so gut wie kein Vorteil. Das liegt daran, dass SIMPLE_INTEGER sehr hardwarenah implementiert ist - ein Nebeneffekt ist, dass SIMPLE_INTEGER kein SQL NULL kennt - Variablen dieses Typs müssen daher schon bei Deklaration initialisiert werden.

4. September 2007

PL/SQL FUNKTIONEN in SQL*Plus testen ...

Heute nochmal was allgemeineres: Immer wieder kommt man in die Situation, mal schnell eine PL/SQL-Prozedur oder -Funktion aufrufen zu müssen - der SQL Developer oder TOAD ist grad' nicht zur Hand - man hat nur SQL*Plus. Wie man nun eine PL/SQL-Prozedur aufrufen kann, ist ja hinreichend bekannt ... SQL> exec MY_PROCEDURE(param1, param2) woraus SQL*Plus dann ein ... SQL> begin MY_PROCEDURE(param1, param2); end; ... macht. Doch wie geht das mit einer Funktion? Hier muss man ja noch den Rückgabewert entgegennehmen ... Ganz einfach, nur ein wenig mehr Tipparbeit ... SQL> var result varchar2(200); SQL> exec :result := MY_FUNCTION(param1, param2) SQL> print result

Beliebte Postings