Skip to Main Content

SQL & PL/SQL

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!

Insert values into local table from LDAP loop query

KthunFeb 18 2021

Hi everyone,
I have a script (see below) that retrieves (3) attributes/values from an external LDAP using LOOPS and successfully reports the results with a DBMS_OUTPUT.PUT_LINE as follows:
sama1
cn1
memb1
sama2
cn2
memb2
sama3
cn3
memb3
...

I need these values to be inserted in the columns of a local user table (TEST_USERS) as follows:
SAMA CN MEMB
sama1 cn1 memb1
sama2 cn2 memb2
sama3 cn3 memb3
...

I tried different solutions without success, can you please help me in coding the script to populate the table?
Thank you for your help,
cordially
Andrea

My code:
BEGIN
-- Choose to raise exceptions.
DBMS_LDAP.USE_EXCEPTION := TRUE;
DBMS_LDAP.UTF8_CONVERSION := FALSE;
-- Connect to the LDAP server.
l_session := DBMS_LDAP.init(hostname => l_ldap_host,
portnum => l_ldap_port);

l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
dn => l_ldap_user,
passwd => l_ldap_passwd);

-- Get user attributes
l_attrs(1) := 'sAMAccountName';
l_attrs(2) := 'cn';
l_attrs(3) := 'memberOf';
l_retval := DBMS_LDAP.search_s(ld => l_session,
base => l_ldap_base,
scope => DBMS_LDAP.SCOPE_SUBTREE,
filter => 'objectclass=user',
attrs => l_attrs,
attronly => 0,
res => l_message);

IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
-- Get all the entries returned by our search.
l_entry := DBMS_LDAP.first_entry(ld => l_session,
msg => l_message);

<< entry_loop >>
WHILE l_entry IS NOT NULL LOOP
-- Get all the attributes for this entry.

 l\_attr\_name := DBMS\_LDAP.first\_attribute(ld       => l\_session,  
                                          ldapentry => l\_entry,  
                                          ber\_elem => l\_ber\_element);  

 \<\< attributes\_loop >>  
 WHILE l\_attr\_name IS NOT NULL LOOP  
   -- Get all the values for this attribute.  
   l\_vals := DBMS\_LDAP.get\_values (ld       => l\_session,  
                                   ldapentry => l\_entry,  
                                   attr     => l\_attr\_name);  
   \<\< values\_loop >>  
   FOR i IN l\_vals.FIRST .. l\_vals.FIRST LOOP -- choose only the first value  
     DBMS\_OUTPUT.PUT\_LINE(SUBSTR(l\_vals(i),1,200));  
   END LOOP values\_loop;  
   l\_attr\_name := DBMS\_LDAP.next\_attribute(ld       => l\_session,  
                                           ldapentry => l\_entry,  
                                           ber\_elem => l\_ber\_element);  
 END LOOP attibutes\_loop;  
 l\_entry := DBMS\_LDAP.next\_entry(ld => l\_session,  
                                 msg => l\_entry);  

END LOOP entry_loop;
END IF;

Comments
Post Details
Added on Feb 18 2021
5 comments
451 views