Casting a Nested Table in a Cursor

Recently I did some work where we had a big selection of data that was returned by a function into a collection. The data needed to be manipulated in a few different ways. One of the great things in PL/SQL is you can cast the collection or nested table as normal database table.

This means you can use the processing power on SQL to do all sorts of things that would require a lot of work if you were just using standard array logic.

Below is an example of creating a simple nested table and then using SQL to sort it. This is a very simple example to show you how to use it. You can treat this table like any other table. Join it to normal database tables or even join it to another version of itself.

CREATE OR REPLACE
TYPE
pet_details AS OBJECT
(
pet_id NUMBER(3),
pet_name VARCHAR2(200),
pet_age NUMBER(3)
)
/

CREATE OR REPLACE
TYPE
list_of_pets AS TABLE OF pet_details
/

DECLARE 
     v_list_of_pets list_of_pets := list_of_pets();
    v_pet_details pet_details;
    -- pass the nested table in as a parameter to the cursor
    CURSOR c_test (cp_input_list list_of_pets) IS
    SELECT *
    FROM TABLE(cp_input_list)
    ORDER BY pet_age;

BEGIN
    -- put some sample data into a nested table
    v_pet_details := pet_details(1, 'Tom', 14);
    v_list_of_pets.EXTEND;
    v_list_of_pets(1) := v_pet_details;
    v_pet_details := pet_details(2, 'Jenny', 2);
    v_list_of_pets.EXTEND;
    v_list_of_pets(2) := v_pet_details;
    v_pet_details := pet_details(3, 'Rambo', 5);
    v_list_of_pets.EXTEND;
    v_list_of_pets(3) := v_pet_details;
    -- Now pass the nested table into the cursor and loop
    FOR v_test IN c_test(v_list_of_pets) LOOP
         dbms_output.put_line('Show me some pets ' ||
             to_char(v_test.pet_id) ||','||
             v_test.pet_name ||','||
             to_char(v_test.pet_age));
    END LOOP;
END;

Nested tables example with multiset processing

A lot of people don't use array processing in PL/SQL. I don't know why. If they were programming in some other language they would probably use them all the time. The PL/SQL implementation uses different names like nested table but essentially these are just arrays and have some really useful features.
Like the ability to do complex comparisons of the entire contents of the array with a single line of code.

Here is an example where nested tables are used to work out if a role has any tables that have not been granted select access to a particular role.

What this does is select all the tables that exists in the example_owner schema and put them in one nested table.
Then get a list of all the the tables that have been granted to the ex_tabs_role and put those in another nested table.
Then see if there is any difference between the two nested tables.
If they are not exactly the same then using MULTISET EXCEPT DISTINCT find out which values are in the first nested table (hold the full list of tables) that are not in the second nested table (holds the list of tables that have already been granted to the role).
The resulting nested table (new_tables_nt) is the list of missing tables that we want to grant to the role.

DECLARE
cst_role     CONSTANT varchar2(20) := 'EX_TABS_ROLE';
-- set up a type that will hold a list of table names
TYPE tables_nt IS TABLE OF VARCHAR2(100);
-- set up array to hold tables the role already has
role_tables_nt  tables_nt;
-- set up array to hold tables that exist for schema 
 -- owner
all_tables_nt    tables_nt;
-- set up array to hold the tables that need to be 
 -- added to the role
new_tables_nt   tables_nt := tables_nt();

BEGIN
-- select the full list of tables we want to grant
SELECT  alt.owner || '.' || alt.table_name
BULK COLLECT INTO all_tables_nt
FROM   all_tables alt
WHERE   alt.owner = 'EXAMPLE_OWNER'
AND     alt.iot_type IS NULL;

-- select the list of tables we have already granted
SELECT  dtp.table_schema || '.' || dtp.table_name
BULK COLLECT INTO role_tables_nt
FROM    all_tab_privs dtp
WHERE   grantee = cst_role;

-- We only need to keep processing if there is a 
 -- difference between the sets
-- As we are using nested tables we can just compare 
 -- if the whole table is the same
IF all_tables_nt != role_tables_nt THEN
-- with set logic find the list of tables that have 
  -- not been granted
-- This is full list of tables minus the granted list
-- then store this difference in the new_tables_nt
  -- array
new_tables_nt := all_tables_nt 
               MULTISET EXCEPT DISTINCT role_tables_nt;

-- check if the difference is that we actually need 
  -- to add tables
-- if it is a postive number it means we need to 
  -- add tables
 if new_tables_nt.COUNT > 0 THEN
-- Now actually do the grants of the new tables 
   -- to the role
FOR i IN new_tables_nt.FIRST..new_tables_nt.LAST LOOP
-- display what the differences are
dbms_output.put_line(new_tables_nt(i));
-- Now grant the differences to the role.
EXECUTE IMMEDIATE 'GRANT SELECT ON ' ||
new_tables_nt(i) || ' TO ' ||
cst_role;
END LOOP;
 END IF; -- there are values to add
END IF; -- sets are different
END;

Note that if you don't check for new_tables_nt.COUNT > 0 then when you try to excute the for loop with no details in it you will get

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 40

Because it cannot loop from first to last as new_tables_nt.FIRST and new_tables_nt.LAST will both be NULL.

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.