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.

Setting a sequence to a specific number

Sometimes there is a need to set, or reset, a sequence number to a specific number. There are a number of ways to do this but I think the following is the easiest.

First find what the current value is

SELECT ex_sequence_number.CURRVAL FROM dual

12759

Say we want to set the value to 50,000 then you need work out the different between 50,000 and 12,759 which = 37241.
So then you change the increment so the next time you get a value it changes by that amount.

ALTER SEQUENCE ex_sequence_number INCREMENT BY 37241;

Then get the next value
SELECT ex_sequence_number.NEXTVAL FROM dual;

50000

Then you MUST set the value back to the original increment.

ALTER SEQUENCE ex_sequence_number INCREMENT BY 1;

So now the sequence is at 50,000 and ready to increment again by 1.

Calling LDAP from PL/SQL

It seems to be a more frequent requirement to interact with LDAP directories from PL/SQL. Either populating records or reading them.

Here is a short test script to debug basic LDAP connection problems from Oracle PL/SQL. I have included some of the common error messages with a brief description below to help work out what is going wrong.

DECLARE
    v_retval pls_integer;
    v_session dbms_ldap.session;
BEGIN

    dbms_ldap.use_exception := TRUE;
    BEGIN
        v_session := dbms_ldap.init(
                  machine.example.com', -- LDAP hostname
                  389 ); -- ldap port
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(
            Error with dbms_ldap.init' ||
            SQLERRM ||'|'||
            SQLCODE);
    END;
    BEGIN
    -- bind with the dn of the user and password
        v_retval := dbms_ldap.simple_bind_s(
                v_session,

                cn=scott,dc=people,dc=example,dc=com',
                'tiger' ); -- password
        dbms_output.put_line('Connected ' || v_retval);
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line(

                    'Error with simple_bind_s ' ||
                    'SQLERRM: ' || SQLERRM ||' | '||
                    'SQLCODE: ' || SQLCODE ||' | '||
                    'Return Value:' || v_retval);
    END;
    v_retval := dbms_ldap.unbind_s( v_session );
END;
This script can help discover a number of errors. Here are some of the most common.

ORA-24247: network access denied by access control list (ACL)
The call to dbms_ldap.init failed because the database access control list does not allow access to that destination.

So you can check what ACL exists for the database.
If you have access to the dba views you can use

SELECT host, lower_port, upper_port, acl
FROM dba_network_acls;

Or as the user trying to connect to LDAP run

SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges;
You may need to get the DBA to create a new ACL for the access to the LDAP directory. They can do this running as sys.
begin
    dbms_network_acl_admin.assign_acl (
        acl => 'acl.xml', -- the filename of the acl
        host => 'machine.example.com', -- LDAP server.
        lower_port => 389, -- LDAP port
        upper_port => 389); -- same LDAP port
end;
/
commit;
ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.
This will occur if you try to access a session that does not exist. From this test script if the dbms_ldap.init failed then there is no session when it tries to do the dbms_ldap.simple_bind_s. This error will occur when v_session does not have a valid value.

ORA-31202: DBMS_LDAP: LDAP client/server error: DSA is unwilling to perform. unauthenticated bind (DN with no password) disallowed
When user is passed but the password is blank and LDAP does not allow unauthenticated bind

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials
Either the user DN or the password is not correct

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid DN syntax. invalid DN
The string used for the user DN is not a valid format.

Of course there are lots of other things to do with LDAP but this will give a few hints to get started and connected.

Nesting multiple levels with xmlelement and xmlagg

I did some work recently prototyping creating XML directly from a select statement. I came across many examples using xmlserialize, xmlelement and xmlagg.

But most of these examples were pretty simple and didn’t go very far into creating a nested structure.

I found examples that went to two levels and used xmlagg and a group by from the query like this.

SELECT  xmlserialize(document

    xmlelement("root",

      xmlagg(

        xmlelement("Location",

          xmlelement("LocationID", l.location_id),

          xmlelement("Street", l.street_address),

          xmlelement("Departments",

            xmlagg(

              xmlelement("Department",

                xmlelement("DepartmentID" , d.department_id),

                xmlelement("DepartmentName", d.department_name)

              )

            order by d.department_id

            )

          )

        )

      )

    ) version '1.0' 

  ) as "xml"

FROM  departments d 

  JOIN locations l ON (d.location_id = l.location_id)

WHERE  l.location_id in (1400, 1500, 1700)

GROUP BY l.location_id, l.street_address

Which works fine but I couldn’t see how to extend this method past two levels. When I tried to go one level deeper I got the error

ORA-00935: group function is nested too deeply

So I had to find another way to do this. Then I found another example which nested the SQL in the select statement.

So the equivalent of the above SQL is done like this.

SELECT  xmlserialize(document

    xmlelement("root",

      xmlagg(

        xmlelement("Location",

          xmlelement("LocationID", l.location_id),

          xmlelement("Street", l.street_address),

          xmlelement("Departments",

            (SELECT  

              xmlagg(

                xmlelement("Department",

                  xmlelement("DepartmentID" , d.department_id),

                  xmlelement("DepartmentName", d.department_name)

                )

              )

            FROM  departments d

            WHERE  d.location_id = l.location_id)

          )

        )

      )

    ) version '1.0' 

  ) as "xml"

FROM  locations l

WHERE  l.location_id in (1400, 1500, 1700)

FROM    locations l

WHERE l.location_id in (1400, 1500, 1700)

With the style like this we can then extend this to however many levels we need.

For example extending the above example to a third level looks like this.

SELECT  xmlserialize(document

    xmlelement("root",

      xmlagg(

        xmlelement("Location",

          xmlelement("LocationID", l.location_id),

          xmlelement("Street", l.street_address),

          xmlelement("Departments",

            (SELECT  

              xmlagg(

                xmlelement("Department",

                  xmlelement("DepartmentID" , d.department_id),

                  xmlelement("DepartmentName", d.department_name),

                  xmlelement("Employees",

                    (SELECT  

                      xmlagg(

                        xmlelement("Employee",

                          xmlelement("EmployeeID" , e.employee_id),

                          xmlelement("EmployeeFirstName", e.first_name),

                          xmlelement("EmployeeLastName", e.last_name)

                        )

                      )

                    FROM  employees e

                    WHERE  e.department_id = d.department_id)

                  )

                )

              )

            FROM  departments d

            WHERE  d.location_id = l.location_id)

          )

        )

      )

    ) version '1.0' 

  ) as "xml"

FROM  locations l

WHERE  l.location_id in (1400, 1500, 1700)

CONNECT BY PRIOR ORDER SIBLINGS BY not ordering correctly

Came across a situation recently where a developer was using a connect by prior order by siblings and the order was all over the place.
It was a fairly lengthy query and I spent a fair amount of time looking at it before I realised there was a DISTINCT clause at the start which was not actually needed.
The DISTINCT completely wrecks the order.

Here is a simple example
SELECT LEVEL,
 a.employee_id,
 a.first_name,
 a.last_name,
 a.manager_id
FROM employees a
START WITH a.employee_id = 100
CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id
ORDER SIBLINGS BY a.last_name

Now if we add DISTINCT
SELECT DISTINCT
 LEVEL,
 a.employee_id,
 a.first_name,
 a.last_name,
 a.manager_id
FROM employees a
START WITH a.employee_id = 100
CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id
ORDER SIBLINGS BY a.last_name

The connect by prior seesm to be ignored and the order is based purely on the order siblings by a.last_name

I'm not sure if this is a bug. But it is something to be aware of if you think you need to put a distinct in when using a connect by prior.

Sorting with select * from

Recently I had a question from someone who was using a local function which took a delimited string as input and split it into values in a table that was defined as a type like

TYPE V_STRING_T IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER

The function used the PIPELINED attribute so the function could be used in an SQL select statement. The function declaration looked like

FUNCTION STR_TO_TAB_PIPELINED(

                        p_string        IN VARCHAR2,

                        p_delimiter     IN VARCHAR2)

RETURN string_array_t PIPELINED;

So an example SQL showing how it might be used is.

SELECT  * 

FROM TABLE(str_to_tab_pipelined('cat,dog,horse',','))

which was all good until there was requirement to have a set of numbers passed in and then sort them as if they were numbers not strings. So basically to add an 'order by to_number'

We can't use a table alias and you can't do something like

SELECT  * 

FROM TABLE(str_to_tab_pipelined('1,5,8,11,4,67,3',','))

ORDER BY to_number(1)

Looking at this I noticed some IDE tools called the column in the query 'column_value'. So I took a chance and tried

SELECT  * 

FROM TABLE(str_to_tab_pipelined('1,5,8,11,4,67,3',','))

ORDER BY to_number(column_value)

And it worked. I did some research and saw the pseudo column 'column_value' is used in XML processing but didn't find mention of using it in this sort of context.