APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetch user e-mail through LDAP

ShinnizleMay 4 2010 — edited May 12 2010

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 
is
        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;
begin

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;

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

Any ideas?

Best regards,
Mathieu

This post has been answered by Shinnizle on May 12 2010

Jump to Answer

oradba101

Why aren't you using the UTL_MAIL built-in package?

Shinnizle

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,
Mathieu

Shinnizle

Answer

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;
 
 BEGIN
  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) 
  loop
   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
  LOOP
   v_attr_name := DBMS_LDAP.first_attribute (v_session, v_entry, v_ber_elmt);
   WHILE v_attr_name IS NOT NULL
   LOOP
    v_vals := DBMS_LDAP.get_values(v_session, v_entry, v_attr_name);
   IF v_vals.COUNT > 0
   THEN
    FOR i IN v_vals.FIRST .. v_vals.LAST
    LOOP
     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:

select
 attribute_value
from
 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):

where
dn = 'CN=jes,CN=Users,DC=domain,DC=localdomain'

But it wouldn't work for some reason. Anyhow, it works now.

Best regards,
Mathieu

Marked as Answer by Shinnizle · Sep 27 2020

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 9 2010

Added on May 4 2010

#apex-discussions

3 comments

2,163 views

APEX

Fetch user e-mail through LDAP

Comments

Post Details