APEX

I'm getting error report error: ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session. when trying to select from a view in APEX. The view is the region source for a Report Region.

The view contains active directory data that was built up using the DBMS_LDAP package.

PLEASE SEE BELOW FOR THE CODE USED IN THE CONSTRUCTION OF THE VIEW :-

CREATE OR REPLACE VIEW v_capa_security AS
(select attribute_name, attribute_value
from table(ldapquery(sys_context('CAPA_ACCESS','domain')||'.com',
'389',
sys_context('CAPA_ACCESS','username')||'@'||sys_context('CAPA_ACCESS','domain')||'.com',
sys_context('CAPA_ACCESS','password'),
'DC=psdev,DC=com',
'sAMAccountName='||sys_context('CAPA_ACCESS','username'),
'memberof,company,mail,sAMAccountName'))
);

(The below function retrieves data from an LDAP directory)

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);
RETURN;

END LDAPQuery;



I then use the following piece of pl/sql code to populate the context and the view :-

begin
pkg_capa_security.pr_set_context(<username>,<password>,<domain>);
end;


If I run the code thru TOAD or SQLPLUS it works fine (I'm able to select from the view without getting any errors)

Is the error due to the LDAPQuery being a Pipelined function????

Any help/suggestions welcome.

Thanks in advance