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. 

More standards checking with PL/Scope

After the earlier post about PL/Scope another use has come to mind with regard to standards checking.


A standard coding principle is to to avoid hard coding in code. Hard coding can take many forms but the one we are looking at here is the case where we define parameters or variables in PL/SQL using a static definition rather than using typing to a database columns.


For example we may have
v_surname VARCHAR2(40);  -- hard code length
instead of
v_surname person.surname%TYPE -- takes definition from the surname column in the person table


By using the type of the column we automatically pick up changes that happen to the table definition without the need to alter our code.


So where does PL/Scope help with this? Well you can pretty easily check all your code (that has been compiled with PL/Scope turned on) to find examples of where variables have been declared using a standard datatype rather than referencing a database column.


So here is a small function as an example.

FUNCTION f_agg_cost (p_event_no NUMBER)
RETURN NUMBER
AS
v_agg_cost   NUMBER;
BEGIN
SELECT SUM (bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;


RETURN (v_agg_cost);
END;

If we compile using PL/Scope we can then run this query to find where straight datatypes have been used

SELECT ai2.line,
ai2.NAME,
ai2.TYPE usage,
ai.NAME datatype
FROM all_identifiers ai
INNER JOIN
all_identifiers ai2
USING
(owner, object_name, object_type)
where owner = 'SCHEMA_OWNER' AND
object_name = 'F_AGG_COST' AND
ai.TYPE LIKE '%DATATYPE' AND
ai.usage_context_id != 0 AND
ai2.usage_id = ai.usage_context_id AND
ai2.TYPE IN ('FORMAL IN', 'FORMAL OUT', 'VARIABLE')
ORDER BY ai.line

Which results in two records
LINE NAME       USAGE     DATATYPE
---- ---------- --------- --------
1   P_EVENT_NO FORMAL IN NUMBER
4   V_AGG_COST VARIABLE NUMBER

So we can see that we get entries corresponding to the declarations for the parameter and the variable. Of course this would be easy to pick up just by looking at it but in a 10,000 line package being able to do it via a query makes finding these much easier.

So if we change the function to be this
FUNCTION f_agg_cost (p_event_no bookings.event_no%TYPE)
RETURN NUMBER
AS
v_agg_cost   bookings.cost%TYPE;
BEGIN
SELECT SUM(bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;


RETURN (v_agg_cost);
END;

And then run the query again we get no records returned.

Of course it does not always make sense to declare parameters and variables against database columns as there may not be a related column that can provide the typing details. But having this option to quickly check lots of code can help with code reviews and implementing standards.

Deleting all the child records of the current record

Sometimes we want to delete a record from a table and we know, or suspect, that there are records in child tables that refer to the current record via a foreign key.
The script below will look at all foreign keys that refer to the primary key (called PT_PK in the script) of the desired parent table (called PARENT_TABLE in the script). It then gets the table name and key column name of the child table and builds a SQL statement to delete the records from the child table that match the key value (Called 'VALUE' in the example below). The SQL is then executed using dynamic SQL. 
Finally it deletes the parent record and commits.


This could also be adjusted to use a unique key instead of the primary key if that was used for foreign keys instead.


For testing, replace the execute statement with 
dbms_output.put_line(v_sql); 
to view the SQL that will be executed and comment out the final delete and commit until you are happy with the SQL it will run.

Note that this example is for a single column key only and only goes one level down. You could build a recursive function that repeatedly tracks down all child tables using the same method or extend it to allow for multi-column keys.


Note also that the '''VALUE''' example used below assumes the column is a literal string and hence needs the triple quotes so it can be inserted as such in the SQL statement. If you need a number then then it would be specified as 'VALUE'. E.g. '9'. Or use other data types as required.

DECLARE
  CURSOR  c_fk IS
  SELECT  constraint_name,
          table_name
  FROM    all_constraints
  WHERE   constraint_type = 'R' AND
          delete_rule     = 'NO ACTION' AND
          r_constraint_name IN  ( 
            SELECT  constraint_name
            FROM    all_constraints
            WHERE   constraint_type  = 'P' AND -- primary
                    table_name       = 'PARENT_TABLE' AND
                    owner            = 'SCHEMA_OWNER') AND
          table_name != 'PARENT_TABLE';
  CURSOR c_child_col (cp_child_cons_name IN      
                          all_cons_columns.constraint_name%TYPE,
                          cp_child_tab_name  IN         
                          all_cons_columns.table_name%TYPE) IS
  SELECT  cc1.column_name      AS child_col,
          cc1.table_name       AS child_table
  FROM    all_cons_columns     cc1,
          all_cons_columns     cc2
  WHERE   cc1.constraint_name  = cp_child_cons_name AND                          
          cc1.table_name       = cp_child_tab_name AND                           
          cc2.constraint_name  = 'PT_PK' AND --parent primary key
          cc1.position         = cc2.position
  ORDER BY cc1.position;
  v_sql   VARCHAR2(1000);
BEGIN
  FOR v_fk_rec IN c_fk LOOP
    FOR v_child_col_rec IN c_child_col(
                               v_fk_rec.constraint_name,
                               v_fk_rec.table_name) LOOP
      v_sql := 'DELETE FROM '||v_child_col_rec.child_table||
                            ' WHERE '||
                            v_child_col_rec.child_col ||
                            ' = ' || 
                            '''VALUE'''; -- the primary key value


      EXECUTE IMMEDIATE v_sql;
    END LOOP;                    
  END LOOP;
  DELETE FROM PARENT_TABLE WHERE PARENT_KEY = 'VALUE';
  COMMIT;
END;