Handle the LDAP with the DBMS_LDAP package

This is a simple PL/SQL package that allows to insert, update and check existence of users in the LDAP.

The package Specification

CREATE OR REPLACE PACKAGE Pkg_Ldap
IS
/*
* Package to handle the LDAP From PL/SQL
* All functions use the DBMS_LDAP package
*
*/

-- User Insert --
FUNCTION Insert_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2 ;

-- User Update --
FUNCTION Modif_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2 ;

-- User Authentification --
FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
RETURN VARCHAR2 ;

-- Add to group --
FUNCTION add_in_group (p_session dbms_ldap.SESSION,
p_group VARCHAR2,
p_user VARCHAR2)
RETURN PLS_INTEGER ;


END Pkg_Ldap;
/

The package body

CREATE OR REPLACE PACKAGE BODY Pkg_Ldap
IS

-- globale variables --
GN$Errcode NUMBER ;
GC$ErrLib VARCHAR2(2000) ;

GC$ldap_host VARCHAR2(256) := 'company.my_dc.org'; -- your LDAP Host url
GC$ldap_port VARCHAR2(256) := '3060'; -- your LDAP Port
GC$ldap_user VARCHAR2(256) := 'cn=orcladmin';
GC$ldap_passwd VARCHAR2(256) := 'welcome1';
GC$ldap_base VARCHAR2(256) := 'cn=my_cn,dc=my_dc,dc=fr';
GC$Groupe VARCHAR2(200) := 'cn=authenticated_users,cn=portal_groups,cn=groups,dc=my_dc,dc=fr';

-------------------------
-- Insert a new user --
-------------------------
FUNCTION Insert_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2
IS

retval PLS_INTEGER;

my_session DBMS_LDAP.SESSION;
emp_dn VARCHAR2(256);
emp_array DBMS_LDAP.MOD_ARRAY;
emp_vals DBMS_LDAP.STRING_COLLECTION;

BEGIN
retval := -1;

-- Insert into LDAP --

DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);

DBMS_LDAP.USE_EXCEPTION := TRUE;

my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);

DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');

retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);

DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));

emp_array := DBMS_LDAP.create_mod_array(20);

-- Properties --
emp_vals(1) := login;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'uid',emp_vals);
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'cn',emp_vals);
emp_vals(1) := name;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'sn',emp_vals);
emp_vals(1) := givenname;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'givenname',emp_vals);
emp_vals(1) := pwd;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'userpassword',emp_vals);
emp_vals(1) := company;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'st',emp_vals);
emp_vals(1) := title;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'title',emp_vals);
emp_vals(1) := address;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'homepostaladdress',emp_vals);
emp_vals(1) := street;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'street',emp_vals);
emp_vals(1) := cp;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'postalcode',emp_vals);
emp_vals(1) := city;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'l',emp_vals);
emp_vals(1) := tel;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'telephonenumber',emp_vals);
emp_vals(1) := mobile;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mobile',emp_vals);
emp_vals(1) := mail;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'mail',emp_vals);

emp_vals(1) := 'top';
emp_vals(2) := 'person';
emp_vals(3) := 'organizationalPerson';
emp_vals(4) := 'inetOrgPerson';
emp_vals(5) := 'orcluser';
emp_vals(6) := 'orcluserv2';

DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD, 'objectclass',emp_vals);

emp_dn := 'cn=' || login || ', ' || GC$ldap_base;

DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') ||': [' || emp_dn || ']');

retval := DBMS_LDAP.add_s(my_session,emp_dn,emp_array);
DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ') || ': '|| TO_CHAR(retval));

DBMS_LDAP.free_mod_array(emp_array);


-- Add the user to the portal group --
retval := add_in_group (my_session, GC$Groupe, emp_dn) ;

retval := DBMS_LDAP.unbind_s(my_session);

DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' ||
TO_CHAR(retval));

DBMS_OUTPUT.PUT_LINE('Resultt -> OK');

RETURN 'OK' ;

-- Handle Exceptions
EXCEPTION

WHEN OTHERS THEN

GN$ErrCode := SQLCODE ;
GC$ErrLib := SQLERRM ;
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN ( GC$ErrLib ) ;

END Insert_User;

-----------------------------
-- Update existing user --
-----------------------------
FUNCTION Modif_User
(
login IN VARCHAR2,
pwd IN VARCHAR2,
name IN VARCHAR2,
givenname IN VARCHAR2,
company IN VARCHAR2,
title IN VARCHAR2,
fonction IN VARCHAR2,
address IN VARCHAR2,
street IN VARCHAR2,
cp IN VARCHAR2,
city IN VARCHAR2,
tel IN VARCHAR2,
mobile IN VARCHAR2,
mail IN VARCHAR2
)
RETURN VARCHAR2
IS

retval PLS_INTEGER;

my_session DBMS_LDAP.SESSION;
emp_session DBMS_LDAP.SESSION;
emp_dn VARCHAR2(256);
emp_array DBMS_LDAP.MOD_ARRAY;
emp_vals DBMS_LDAP.STRING_COLLECTION;

BEGIN
retval := -1;


emp_dn := 'cn=' || login || ', ' || GC$ldap_base ;

DBMS_OUTPUT.PUT('ADD Example: ');
DBMS_OUTPUT.PUT_LINE('Connecting to directory .. ');
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Host ',25,' ') || ': ' || GC$ldap_host);
DBMS_OUTPUT.PUT_LINE(RPAD('LDAP Port ',25,' ') || ': ' || GC$ldap_port);


-- Unabling exceptions --
DBMS_LDAP.USE_EXCEPTION := TRUE;

-- Initialization --
my_session := DBMS_LDAP.init(GC$ldap_host,GC$ldap_port);

DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ') || ': ' ||
RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)');

-- Connexion au LDAP --
retval := DBMS_LDAP.simple_bind_s(my_session, GC$ldap_user,GC$ldap_passwd);

DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval));


-- Tableau pour les modifications --
emp_array := DBMS_LDAP.create_mod_array(20);

-- Populate the varray --
IF login IS NOT NULL THEN
emp_vals(1) := login;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'uid',emp_vals);
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'cn',emp_vals);
END IF ;

IF name IS NOT NULL THEN
emp_vals(1) := name;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'sn',emp_vals);
END IF ;
IF givenname IS NOT NULL THEN
emp_vals(1) := givenname;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'givenname',emp_vals);
END IF ;
IF company IS NOT NULL THEN
emp_vals(1) := company;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'st',emp_vals);
END IF ;
IF address IS NOT NULL THEN
emp_vals(1) := address;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'homepostaladdress',emp_vals);
END IF ;
IF street IS NOT NULL THEN
emp_vals(1) := street;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'street',emp_vals);
END IF ;
IF cp IS NOT NULL THEN
emp_vals(1) := cp;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'postalcode',emp_vals);
END IF ;
IF city IS NOT NULL THEN
emp_vals(1) := city;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'l',emp_vals);
END IF ;
IF title IS NOT NULL THEN
emp_vals(1) := title;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'title',emp_vals);
END IF ;
IF tel IS NOT NULL THEN
emp_vals(1) := tel;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'telephonenumber',emp_vals);
END IF ;
IF mobile IS NOT NULL THEN
emp_vals(1) := mobile;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mobile',emp_vals);
END IF ;
IF pwd IS NOT NULL THEN
emp_vals(1) := pwd;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'userpassword',emp_vals);
END IF ;
IF mail IS NOT NULL THEN
emp_vals(1) := mail;
DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,'mail',emp_vals);
END IF ;

-- Update LDAP --
retval := DBMS_LDAP.modify_s(my_session,emp_dn,emp_array);
DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' || TO_CHAR(retval));

-- Free the varray --
DBMS_LDAP.free_mod_array(emp_array);

-- Logout from LDAP --
retval := DBMS_LDAP.unbind_s(my_session);

DBMS_OUTPUT.PUT_LINE(RPAD('unbind_res Returns ',25,' ') || ': ' || TO_CHAR(retval));

DBMS_OUTPUT.PUT_LINE('Résultat -> OK');

RETURN 'OK' ;

-- Handle Exceptions
EXCEPTION

WHEN OTHERS THEN

GN$ErrCode := SQLCODE ;
GC$ErrLib := SQLERRM ;
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(GN$ErrCode));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || GC$ErrLib);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN ( GC$ErrLib ) ;

END Modif_User;


/*
*****************************************************************
-- Function : add_in_group
-- Parameters : group name / user name (DN) / session
-- Return : 0 = Ok / others KO
*****************************************************************
*/
FUNCTION add_in_group (p_session dbms_ldap.SESSION,
p_group VARCHAR2,
p_user VARCHAR2)
RETURN PLS_INTEGER IS
my_vals dbms_ldap.string_collection;
v_array dbms_ldap.mod_array;
retval PLS_INTEGER;

BEGIN
-- Initialize the pl/sql table for the new entry
my_vals(1) := p_user;

-- Initialize the varray for the modify command
v_array := dbms_ldap.create_mod_array(num => 1);

IF v_array = NULL THEN
dbms_output.put_line('Error add_in_group: v_array not initialized.');
NULL;
END IF;
dbms_output.put_line ('v_array initialisee avec succes.');

-- Populate the varray
dbms_ldap.populate_mod_array(v_array,
dbms_ldap.mod_add,
'uniquemember',
my_vals);

dbms_output.put_line(p_user);
-- Group Modification
retval := dbms_ldap.modify_s(p_session, p_group, v_array);


-- Free the varray
dbms_ldap.free_mod_array(v_array);

RETURN retval;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('add_in_group : '|| SQLCODE||' '||SQLERRM);
RETURN -1 ;
END add_in_group;

-----------------------------
-- User Authentification --
-----------------------------
FUNCTION existe_user( p_user IN VARCHAR2, p_pwd IN VARCHAR2 )
RETURN VARCHAR2
IS
ldap_host VARCHAR2(256);
ldap_port PLS_INTEGER;
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
retval PLS_INTEGER;
my_session DBMS_LDAP.SESSION;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
sub_type PLS_INTEGER;
subscriber_id VARCHAR2(2000);
my_pset_coll DBMS_LDAP_UTL.PROPERTY_SET_COLLECTION;
my_property_names DBMS_LDAP.STRING_COLLECTION;
my_property_values DBMS_LDAP.STRING_COLLECTION;
user_handle DBMS_LDAP_UTL.HANDLE;
user_id VARCHAR2(2000);
user_type PLS_INTEGER;
user_password VARCHAR2(2000);
my_mod_pset DBMS_LDAP_UTL.MOD_PROPERTY_SET;
my_attrs DBMS_LDAP.STRING_COLLECTION;
user_dn VARCHAR2(256);
BEGIN
-- Please customize the following variables as needed
ldap_user := 'cn=orcladmin';
ldap_passwd := 'welcome1';
sub_type := DBMS_LDAP_UTL.TYPE_DEFAULT;
subscriber_id := NULL;
user_type := DBMS_LDAP_UTL.TYPE_NICKNAME;
user_id := p_user;
user_password := p_pwd;
-- Choosing exceptions to be raised by DBMS_LDAP library.
DBMS_LDAP.USE_EXCEPTION := TRUE;

DBMS_OUTPUT.PUT_LINE('Input Parameters: ');
DBMS_OUTPUT.PUT_LINE('LDAP HOST: ' || GC$ldap_host );
DBMS_OUTPUT.PUT_LINE('LDAP PORT: ' || GC$ldap_port);
DBMS_OUTPUT.PUT_LINE('LDAP BIND USER: ' || ldap_user);
DBMS_OUTPUT.PUT_LINE('USER ID : ' || user_id);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
-----------------------------------------------
-- Connect to the LDAP server
-- and obtain and ld session.
-----------------------------------------------
DBMS_OUTPUT.PUT('Connecting to ' || GC$ldap_host || ' ...');

my_session := DBMS_LDAP.init(GC$ldap_host, GC$ldap_port);

DBMS_OUTPUT.PUT_LINE(': Connected.');
-----------------------------------------------
-- Bind to the directory
-----------------------------------------------
DBMS_OUTPUT.PUT('Binding to directory as ' || ldap_user || ' ... ');
retval := DBMS_LDAP.simple_bind_s(my_session, ldap_user, ldap_passwd);
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Create Subscriber Handle
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Creating Realm Handle ... ');
retval := DBMS_LDAP_UTL.create_subscriber_handle(subscriber_handle,
sub_type,
subscriber_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
RETURN('Error: create_subscriber_handle returns : ' || TO_CHAR(retval));
END IF;
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Create User Handle
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Creating user handle for ' || user_id || ' ... ');
retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: create_user_handle returns : ' || TO_CHAR(retval));
RETURN('Error: create_user_handle returns : ' || TO_CHAR(retval));
END IF;
DBMS_OUTPUT.PUT_LINE(': Successful.');
---------------------------------------------------------------------
-- Set user handle properties
-- (link subscriber to user )
---------------------------------------------------------------------
retval := DBMS_LDAP_UTL.set_user_handle_properties(user_handle,
DBMS_LDAP_UTL.SUBSCRIBER_HANDLE,
subscriber_handle);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
RETURN('Error: set_user_handle_properties returns : ' || TO_CHAR(retval));
END IF;
---------------------------------------------------------------------
-- Authenticate User
---------------------------------------------------------------------
DBMS_OUTPUT.PUT('Authenticating user ' || user_id || ' ... ');
retval := DBMS_LDAP_UTL.authenticate_user(my_session,
user_handle,
DBMS_LDAP_UTL.AUTH_SIMPLE,
user_password,
NULL);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('Authentification error : ' || TO_CHAR(retval));
IF retval = -5 THEN
RETURN('User unknown');
ELSIF retval = -16 THEN
RETURN ('Incorrect password');

ELSIF retval = DBMS_LDAP_UTL.PARAM_ERROR THEN
RETURN (' Invalid input parameters.');

ELSIF retval = DBMS_LDAP_UTL.GENERAL_ERROR THEN
RETURN (' Authentication failed.');

ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_USER THEN
RETURN (' USER doesn''t exist.');

ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_USER_ENTRIES THEN
RETURN (' Multiple NUMBER OF USER DN entries exist IN the DIRECTORY FOR the given USER.');

ELSIF retval = DBMS_LDAP_UTL.INVALID_SUBSCRIBER_ORCL_CTX THEN
RETURN (' Invalid Subscriber Oracle Context.');

ELSIF retval = DBMS_LDAP_UTL.NO_SUCH_SUBSCRIBER THEN
RETURN (' Subscriber doesn''t exist.');

ELSIF retval = DBMS_LDAP_UTL.MULTIPLE_SUBSCRIBER_ENTRIES THEN
RETURN (' Multiple NUMBER OF subscriber DN entries exist IN the DIRECTORY FOR the given subscriber.');

ELSIF retval = DBMS_LDAP_UTL.INVALID_ROOT_ORCL_CTX THEN
RETURN (' Invalid Root Oracle Context.');

ELSIF retval = DBMS_LDAP_UTL.AUTH_PASSWD_CHANGE_WARN THEN
RETURN (' PASSWORD should be changed.');

ELSIF retval = DBMS_LDAP_UTL.ACCT_TOTALLY_LOCKED_EXCEPTION THEN
RETURN (' USER account IS locked.');

ELSIF retval = DBMS_LDAP_UTL.PWD_EXPIRED_EXCEPTION THEN
RETURN (' USER PASSWORD has expired.');

ELSIF retval = DBMS_LDAP_UTL.PWD_GRACELOGIN_WARN THEN
RETURN (' Grace login FOR USER.');

ELSE
RETURN('Authentification error : ' || TO_CHAR(retval));
END IF ;
ELSE
DBMS_OUTPUT.PUT_LINE(': Successful.');
END IF;


------------------------------------------
-- Free Mod Propertyset
------------------------------------------
DBMS_LDAP_UTL.free_mod_propertyset(my_mod_pset);
---------------------------------------------------------------------
-- Free handles
---------------------------------------------------------------------
DBMS_LDAP_UTL.free_handle(subscriber_handle);
DBMS_LDAP_UTL.free_handle(user_handle);

-- unbind from the directory
DBMS_OUTPUT.PUT('Unbinding from directory ... ');
retval := DBMS_LDAP.unbind_s(my_session);
IF retval != DBMS_LDAP_UTL.SUCCESS THEN
-- Handle Errors
DBMS_OUTPUT.PUT_LINE('unbind_s returns : ' || TO_CHAR(retval));
RETURN('unbind_s returns : ' || TO_CHAR(retval));
ELSE
DBMS_OUTPUT.PUT_LINE(': Successful.');
END IF;

RETURN( 'OK' ) ;

-- Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
RETURN SQLERRM ;
END;

END Pkg_Ldap;
/


Calling from PL/SQL

--------------------
-- Insert entry --
--------------------
Declare
LC$Return Varchar2(2000) ;
BEGIN
LC$Return := PKG_LDAP.Insert_User
(
'f.degrelle@free.fr',
'musicisthebest',
'degrelle',
'francois',
'znort',
'researcher',
'analyst',
'10 World place',
'',
'75000',
'Paris',
'0102030405',
'0606060606',
'f.degrelle@free.fr'
);

If LC$Return <> 'OK' Then
message( LC$Return ) ;
End if ;
END;

--------------------
-- Update entry --
--------------------
DECLARE
LC$Return Varchar2(2000) ;
BEGIN

LC$Return := PKG_LDAP.Modif_User
(
'f.degrelle@free.fr',
'musicisthebest',
'degrelle',
'francois',
'znort',
'researcher',
'analyst',
'10 World place',
'',
'75000',
'Paris',
'0102030405',
'0606060606',
'f.degrelle@free.fr'
);

If LC$Return <> 'OK' Then
message( LC$Return ) ;
End if ;


END;

Calling from Java

/*---------------------------------------
* Check existence of user in the LDAP
*--------------------------------------*/
public String Check_User (OracleConnection conn, String p_user, String p_pwd)
throws Exception, SQLException
{

String sReturn = "" ;

// Call the stored function
CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Existe_User (?,?)}");

call.registerOutParameter (1, Types.VARCHAR);
call.setString (2, p_user);
call.setString (3, p_pwd);
call.execute ();
sReturn = call.getString (1) ;

if( ! sReturn.equals("OK") ) System.out.println("*** Unknown user *** " + sReturn) ;

call.close();

return( sReturn ) ;

}


/*-------------------------
* Update the LDAP
*------------------------*/
public String SetUserInfo (OracleConnection conn, int iUtiId)
throws Exception, SQLException
{
String sReturn = "" ;

// Call the stored function
CallableStatement call = conn.prepareCall ("{ ? = call PKG_LDAP.Modif_User (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

call.registerOutParameter (1, Types.VARCHAR);
call.setString (2, "login");
call.setString (3, "pwd");
call.setString (4, "name");
call.setString (5, "givenname");
call.setString (6, "company");
call.setString (7, "title");
call.setString (8, "function");
call.setString (9, "address");
call.setString (10, "street");
call.setString (11, "cp");
call.setString (12, "city");
call.setString (13, "tel");
call.setString (14, "mobile");
call.setString (15, "mail");

call.execute ();
sReturn = call.getString (1) ;

if( ! sReturn.equals("OK") ) System.out.println("*** LDAP update error *** " + sReturn) ;

call.close();

return sReturn ;

}

You can download the source code here