This discussion is archived
3 Replies Latest reply: May 12, 2010 11:53 AM by Shinnizle RSS

Fetch user e-mail through LDAP

Shinnizle Journeyer
Currently Being Moderated
I'd like to send an e-mail through APEX whenever a request is denied. I want to send it to the user(s) who made the request through the system. This user was authenticated via LDAP (Active Directory) in another application when he sent that request. Therefore, his e-mail address can be found within his AD profile he authenticates with. All the requests are listed in a report in another application (which uses the same LDAP Authentication Scheme) where they can be reviewed, then either denied or accepted. When the deny button is clicked, it updates the request's status in the database and sends an e-mail to an hardcoded e-mail address. I'd like to send it to the user who made the request.

Is it possible to use the current LDAP session (since I'm logged in the app via LDAP, I take for granted that there must be a valid LDAP session in use), or do I need to connect again to the server? If it's the latter, then how do I do that? I can't just hardcode my own credentials in the function, that would be nuts.

I thought I could use a script like this and call it in a page process, but I know I'm missing something.
create or replace function Get_Mail(p_user in varchar2)
return varchar2 
        l_attrs         dbms_ldap.string_collection;
        l_message       dbms_ldap.MESSAGE;
        l_entry         dbms_ldap.MESSAGE;
        l_vals          dbms_ldap.string_collection;
        l_user     varchar2(256);
        l_user2      varchar2(256);
        l_mail          varchar2(256);
        l_ldap_server     varchar2(256)         := '****';
        l_domain     varchar2(256)         := '****';
        l_ldap_port     number              := 389;
        l_retval     pls_integer;
        l_session     dbms_ldap.session;
        l_username      varchar2(256)         := NULL;
        l_password      varchar2(256)         := NULL;

dbms_ldap.use_exception := TRUE;

l_user2       := p_user||'@'||l_domain;

l_user       := l_username||'@'||l_domain;
l_session := dbms_ldap.init (l_ldap_server, l_ldap_port);
l_retval  := dbms_ldap.simple_bind_s (l_session, l_user, l_password);

l_attrs(1) := 'email';
l_retval   := dbms_ldap.search_s (ld => l_session, base => '****', scope => dbms_ldap.scope_subtree, 
filter =>'&(userPrincipalName='|| l_user2 || ')(objectClass=user)', attrs => l_attrs, attronly => 0, res => l_message);

l_entry := dbms_ldap.first_entry (ld => l_session, msg => l_message);
l_vals  := dbms_ldap.get_values (ld => l_session, ldapentry => l_entry, attr => l_attrs(1));

l_mail := l_vals(1);
return l_mail;

  when others then
    dbms_output.put_line (' Erreur #' || TO_CHAR (SQLCODE));
    dbms_output.put_line (' Message: ' || SQLERRM);
    l_mail := NULL;
    return l_mail;
end Get_Mail;
Any ideas?

Best regards,
  • 1. Re: Fetch user e-mail through LDAP
    oradba101 Newbie
    Currently Being Moderated
    Why aren't you using the UTL_MAIL built-in package?
  • 2. Re: Fetch user e-mail through LDAP
    Shinnizle Journeyer
    Currently Being Moderated
    I wasn't aware of that package. I use apex_mail.send. But sending the email is not the problem, it is fetching the email address in LDAP.

    Best regards,
  • 3. Re: Fetch user e-mail through LDAP
    Shinnizle Journeyer
    Currently Being Moderated
    I've found the solution, thanks to John Edward Scott's and Scott Spendolini's book "Pro Oracle Application Express". So, for those interested:

    I've created two types:
    create or replace type
    ty_ldap_query as object(
    dn varchar2(200),
    attribute_name varchar2(100),
    attribute_value varchar2(100));
    create or replace type tbl_ty_ldap_query
    as table of ty_ldap_query;
    Then I created the LDAPQuery routine:
    create or replace function LDAPQuery(
     p_host in varchar2,
     p_port in varchar2,
     p_user in varchar2,
     p_password in varchar2,
     p_dn_base in varchar2,
     p_filter in varchar2,
     p_attributes in varchar2)
     return tbl_ty_ldap_query PIPELINED is
     v_result tbl_ty_ldap_query := tbl_ty_ldap_query(ty_ldap_query(NULL, NULL, NULL));
     retval PLS_INTEGER;
     v_session DBMS_LDAP.SESSION;
     v_attrs DBMS_LDAP.string_collection;
     v_message DBMS_LDAP.MESSAGE;
     v_entry DBMS_LDAP.MESSAGE;
     v_dn VARCHAR2 (256);
     v_attr_name VARCHAR2 (256);
     v_ber_elmt DBMS_LDAP.ber_element;
     v_vals DBMS_LDAP.string_collection;
     b_first BOOLEAN := TRUE;
     v_dn_identifier VARCHAR2(200);
     v_attributes apex_application_global.vc_arr2;
      retval := -1;
      DBMS_LDAP.use_exception := TRUE;
      v_session := DBMS_LDAP.init (p_host, p_port);
      retval := DBMS_LDAP.simple_bind_s (v_session, p_user, p_password);
      v_attributes := apex_util.STRING_TO_TABLE(p_attributes, ',');
      for i in (v_attributes.first)..(v_attributes.last) 
       v_attrs(i) := v_attributes(i);
      end loop;
      retval := DBMS_LDAP.search_s (v_session, p_dn_base, DBMS_LDAP.scope_subtree, p_Filter, v_attrs, 0, v_message);
      retval := DBMS_LDAP.count_entries (v_session, v_message);
      v_entry := DBMS_LDAP.first_entry (v_session, v_message);
      WHILE v_entry IS NOT NULL
       v_attr_name := DBMS_LDAP.first_attribute (v_session, v_entry, v_ber_elmt);
       WHILE v_attr_name IS NOT NULL
        v_vals := DBMS_LDAP.get_values(v_session, v_entry, v_attr_name);
       IF v_vals.COUNT > 0
        FOR i IN v_vals.FIRST .. v_vals.LAST
         v_dn_identifier := dbms_ldap.GET_DN(v_session, v_entry); 
         pipe row (ty_ldap_query(v_dn_identifier, v_attr_name, v_vals(i)));
        END LOOP;
       END IF;
       v_attr_name := DBMS_LDAP.next_attribute (v_session, v_entry, v_ber_elmt);
      END LOOP;
      v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
     END LOOP;
     retval := DBMS_LDAP.unbind_s(v_session);
    END LDAPQuery;
    And I queried it that way:
     table(LDAPQuery('<server>', '<port>', '<user@domain>', '<password>', '<search base>', '&(!(logonCount=0)(objectClass=User)(sAMAccountName=<sAMAccountName>))', 'mail'))
    In the book it said I could do this (instead of using LDAP filters):
    dn = 'CN=jes,CN=Users,DC=domain,DC=localdomain'
    But it wouldn't work for some reason. Anyhow, it works now.

    Best regards,


  • Correct Answers - 10 points
  • Helpful Answers - 5 points