Calling LDAP from PL/SQL, Retrieve attribute

Below is an example of some code that can be used to get the userPassword (or any attribute) details from LDAP using PL/SQL.
It also shows an example of getting the error 'ORA-31208: DBMS_LDAP: PL/SQL - Invalid LDAP Message.' and what it means and how it can be handled.

The inital code snippet to get the password looks like this

DECLARE
v_password VARCHAR2(2000);
v_session DBMS_LDAP.SESSION;
v_retval PLS_INTEGER
v_search_result DBMS_LDAP.message;
v_first_entry DBMS_LDAP.message;
v_attr_name VARCHAR2(256);
.....

BEGIN
.....
-- some code goes here to set up LDAP connection see previous post for details
.....

-- do the search to find the person we want
v_retval := DBMS_LDAP.SEARCH_S(v_session,
'dc=people,dc=ds,dc=company,dc=com',
DBMS_LDAP.SCOPE_SUBTREE,
'cn=notauser',
'userPassword',
0,
v_search_result);
-- get the first entry that was in the search
v_first_entry := DBMS_LDAP.FIRST_ENTRY(v_session,
v_search_result);
-- get the first userPassword attribute that matches
v_attr_name := DBMS_LDAP.FIRST_ATTRIBUTE(v_session ,
v_first_entry,
v_ber_elmt);
-- get the value of the userPassword attribute
v_vals :=  DBMS_LDAP.GET_VALUES(v_session,
v_first_entry,
v_attr_name);
-- trim down the value and assign to final variable
v_password := TRIM(TO_CHAR(v_vals(0)));

This all works fine assuming the records exist in LDAP as expected.
However if we don't find the records we expect we could encounter
ORA-31208: DBMS_LDAP: PL/SQL - Invalid LDAP Message.
Because if the search doesn't return a value then we are still trying to get the values from the search.
So we need to check if there was a value returned before we then try and pull the details out.
The first time I saw Invalid LDAP Message I thought it was looking for an error message. It is actually referring to something with the type DBMS_LDAP.message.

So we simply add some conditions around the code following the search. Like shown below.

-- do the search to find the person we want
v_retval := DBMS_LDAP.SEARCH_S(v_session,
'dc=people,dc=ds,dc=company,dc=com',
DBMS_LDAP.SCOPE_SUBTREE,
'cn=notauser',
'userPassword',
0,
v_search_result);
IF v_search_result IS NOT NULL THEN
-- get the first entry that was in the search
v_first_entry := DBMS_LDAP.FIRST_ENTRY(v_session,
v_search_result);
IF v_first_entry IS NOT NULL THEN
-- get the first userPassword attribute that matches
v_attr_name := DBMS_LDAP.FIRST_ATTRIBUTE(v_session ,
v_first_entry,
v_ber_elmt);
-- get the value of the userPassword attribute
v_vals :=  DBMS_LDAP.GET_VALUES(v_session,
v_first_entry,
v_attr_name);
-- trim down the value and assign to final variable
v_password := TRIM(TO_CHAR(v_vals(0)));

ELSE -- v_first_entry IS NULL
-- do whatever you want to do when you can't get the value
END IF;

ELSE -- v_search_result IS NULL
-- do whatever you want to do when you can't get the value
END IF;

ORA-01001: invalid cursor

Recently came across some code that was causing an ORA-010001: invalid cursor error but it seemed like it should work even though it is not really required. Here is an example of the offending code.

FUNCTION test_loop
         RETURN BOOLEAN IS
         CURSOR c_au IS
                     SELECT user_id FROM all_users;
BEGIN
          FOR v_au_rec IN c_au LOOP
                     IF c_au%ISOPEN THEN
                                CLOSE c_au;
                     END IF;
                     RETURN TRUE;
          END LOOP;
          RETURN FALSE;
EXCEPTION
          WHEN OTHERS THEN
                     RAISE;
END;

The example above doesn't do anything sensible but shows the issue. Inside the cursor loop it check if the cursor is open, if it is then close it and the return from the function which will exit the cursor. While it might seem like a good idea to close the cursor if you are going to exit the loop with a return from the function it is not needed. Oracle is smart enough to close the cursor on the way out anyway so there is no need to do it explicitly.

But I thought this should still work without an error but it doesn't.

Running this gives the ORA-010001: invalid cursor error but now where I expected.

It is actually raised as part of the RETURN TRUE;

I assume it is because the cursor is already closed by the explicit CLOSE statement so when it does the implicit close as part of the RETURN FALSE from the loop it fails as the cursor is already gone.

The simple solution is to let Oracle handle the close itself and remove the code to check and close the cursor.