Oracle | DBMS

Oracle DBMS_LDAP Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This is the primary interface used by a variety of clients to make LDAP requests for Identity Management.

AUTHID CURRENT_USER

Constants and Defaulted Variables

Name	Data Type	Value
General Constants
VERSION	VARCHAR2(256)	2
INTERFACE_VERSION	VARCHAR2(256)	2
Error Constants
SUCCESS	NUMBER	0
OPERATIONS_ERROR	NUMBER	1
PROTOCOL_ERROR	NUMBER	2
TIMELIMIT_EXCEEDED	NUMBER	3
SIZELIMIT_EXCEEDED	NUMBER	4
COMPARE_FALSE	NUMBER	5
COMPARE_TRUE	NUMBER	6
STRONG_AUTH_NOT_SUPPORTED	NUMBER	7
STRONG_AUTH_REQUIRED	NUMBER	8
PARTIAL_RESULTS	NUMBER	9
REFERRAL	NUMBER	10
ADMINLIMIT_EXCEEDED	NUMBER	11
UNAVAILABLE_CRITIC	NUMBER	12
NO_SUCH_ATTRIBUTE	NUMBER	16
UNDEFINED_TYPE	NUMBER	17
INAPPROPRIATE_MATCHING	NUMBER	18
CONSTRAINT_VIOLATION	NUMBER	19
TYPE_OR_VALUE_EXISTS	NUMBER	20
INVALID_SYNTAX	NUMBER	21
NO_SUCH_OBJECT	NUMBER	32
ALIAS_PROBLEM	NUMBER	33
INVALID_DN_SYNTAX	NUMBER	34
IS_LEAF	NUMBER	35
ALIAS_DEFER_PROBLEM	NUMBER	36
INAPPROPRIATE_AUTH	NUMBER	48
INVALID_CREDENTIALS	NUMBER	49
INSUFFICIENT_ACCESS	NUMBER	50
BUSY	NUMBER	51
UNAVAILABLE	NUMBER	52
UNWILLING_TO_PERFORM	NUMBER	53
LOOP_DETECT	NUMBER	54
NAMING_VIOLATION	NUMBER	64
OBJECT_CLASS_VIOLATION	NUMBER	65
NOT_ALLOWED_ON_NONLEAF	NUMBER	66
NOT_ALLOWED_ON_RDN	NUMBER	67
ALREADY_EXISTS	NUMBER	68
NO_OBJECT_CLASS_MODS	NUMBER	69
RESULTS_TOO_LARGE	NUMBER	70
OTHER	NUMBER	80
SERVER_DOWN	NUMBER	81
LOCAL_ERROR	NUMBER	82
ENCODING_ERROR	NUMBER	83
DECODING_ERROR	NUMBER	84
TIMEOUT	NUMBER	85
AUTH_UNKNOWN	NUMBER	86
FILTER_ERROR	NUMBER	87
USER_CANCELLED	NUMBER	88
PARAM_ERROR	NUMBER	89
NO_MEMORY	NUMBER	90
Port Constants
PORT	NUMBER	389
SSL_PORT	NUMBER	636
Options
OPT_DESC	NUMBER	1
OPT_DEREF	NUMBER	2
OPT_SIZELIMIT	NUMBER	3
OPT_TIMELIMIT	NUMBER	4
OPT_THREAD_FN_PTRS	NUMBER	5
OPT_REBIND_FN	NUMBER	6
OPT_REBIND_ARG	NUMBER	7
OPT_REFERRALS	NUMBER	8
OPT_RESTART	NUMBER	9
OPT_SSL	NUMBER	10
OPT_IO_FN_PTRS	NUMBER	11
OPT_CACHE_FN_PTRS	NUMBER	13
OPT_CACHE_STRATEGY	NUMBER	14
OPT_CACHE_ENABLE	NUMBER	15
OPT_REFERRAL_HOP_LIMIT	NUMBER	16
OPT_PROTOCOL_VERSION	NUMBER	17
OPT_SERVER_CONTROLS	NUMBER	18
OPT_CLIENT_CONTROLS	NUMBER	19
OPT_PREFERRED_LANGUAGE	NUMBER	20
OPT_ERROR_NUMBER	NUMBER	49
OPT_ERROR_STRING	NUMBER	50
On/Off Options
OPT_OFF	NUMBER	0
OPT_ON	NUMBER	1
SSL Authentication Modes
GSLC_SSL_NO_AUTH	NUMBER	1
GSLC_SSL_ONEWAY_AUTH	NUMBER	32
GSLC_SSL_TWOWAY_AUTH	NUMBER	64
Search Scopes
SCOPE_BASE	NUMBER	0
SCOPE_ONELEVEL	NUMBER	1
SCOPE_SUBTREE	NUMBER	2
For Modifications
MOD_ADD	NUMBER	0
MOD_DELETE	NUMBER	1
MOD_REPLACE	NUMBER	2
MOD_BVALUES	NUMBER	128
Authentication Methods
AUTH_NONE	NUMBER	0
AUTH_SIMPLE -- context specific + primitive	NUMBER	128
AUTH_SASL -- v3 SASL	NUMBER	163
LDAP Server Connection Structures
CONNST_NEEDSOCKET	NUMBER	1
CONNST_CONNECTING	NUMBER	2
CONNST_CONNECTED	NUMBER	3
Outstanding Request Tracking
REQST_INPROGRESS	NUMBER	1
REQST_CHASINGREFS	NUMBER	2
REQST_NOTCONNECTED	NUMBER	3
REQST_WRITING	NUMBER	4
LDAP Connection Structures
DEREF_NEVER	NUMBER	0
DEREF_SEARCHING	NUMBER	1
DEREF_FINDING	NUMBER	2
DEREF_ALWAYS	NUMBER	3
LDAP URL Handling
URL_ERR_NOTLDAP -- URL doesn't begin with "ldap	NUMBER	1
URL_ERR_NODN -- URL has no DN (required)	NUMBER	2
URL_ERR_BADSCOPE -- URL scope string is invalid	NUMBER	3
URL_ERR_MEM -- can't allocate memory space	NUMBER	4
Session Info Types
TYPE_ADD_INFO	NUMBER	1
LDAP Error Codes
SUCCESS	NUMBER	0
OPERATIONS_ERROR	NUMBER	1
PROTOCOL_ERROR	NUMBER	2
TIMELIMIT_EXCEEDED	NUMBER	3
SIZELIMIT_EXCEEDED	NUMBER	4
COMPARE_FALSE	NUMBER	5
COMPARE_TRUE	NUMBER	6
STRONG_AUTH_NOT_SUPPORTED	NUMBER	7
STRONG_AUTH_REQUIRED	NUMBER	8
PARTIAL_RESULTS	NUMBER	9
REFERRAL	NUMBER	10
ADMINLIMIT_EXCEEDED	NUMBER	11
UNAVAILABLE_CRITIC	NUMBER	12
NO_SUCH_ATTRIBUTE	NUMBER	16
UNDEFINED_TYPE	NUMBER	17
INAPPROPRIATE_MATCHING	NUMBER	18
CONSTRAINT_VIOLATION	NUMBER	19
TYPE_OR_VALUE_EXISTS	NUMBER	20
INVALID_SYNTAX	NUMBER	21
NO_SUCH_OBJECT	NUMBER	32
ALIAS_PROBLEM	NUMBER	33
INVALID_DN_SYNTAX	NUMBER	34
IS_LEAF	NUMBER	35
ALIAS_DEREF_PROBLEM	NUMBER	36
INAPPROPRIATE_AUTH	NUMBER	48
INVALID_CREDENTIALS	NUMBER	49
INSUFFICIENT_ACCESS	NUMBER	50
BUSY	NUMBER	51
UNAVAILABLE	NUMBER	52
UNWILLING_TO_PERFORM	NUMBER	53
LOOP_DETECT	NUMBER	54
NAMING_VIOLATION	NUMBER	64
OBJECT_CLASS_VIOLATION	NUMBER	65
NOT_ALLOWED_ON_NONLEAF	NUMBER	66
NOT_ALLOWED_ON_RDN	NUMBER	67
ALREADY_EXISTS	NUMBER	68
NO_OBJECT_CLASS_MODS	NUMBER	69
RESULTS_TOO_LARGE	NUMBER	70
OTHER	NUMBER	80
SERVER_DOWN	NUMBER	81
LOCAL_ERROR	NUMBER	82
ENCODING_ERROR	NUMBER	83
DECODING_ERROR	NUMBER	84
TIMEOUT	NUMBER	85
AUTH_UNKNOWN	NUMBER	86
FILTER_ERROR	NUMBER	87
USER_CANCELLED	NUMBER	88
PARAM_ERROR	NUMBER	89
NO_MEMORY	NUMBER	90
LDAP Return Error Codes
INVALID_LDAP_SESSION	NUMBER	1024
INVALID_LDAP_AUTH_METHOD	NUMBER	1025
INVALID_LDAP_SEARCH_SCOPE	NUMBER	1026
INVALID_LDAP_TIME_VALUE	NUMBER	1027
INVALID_LDAP_MESSAGE	NUMBER	1027
INVALID_LDAP_ENTRY_DN	NUMBER	1028
INVALID_LDAPMOD	NUMBER	1029
INVALID_LDAP_DN	NUMBER	1030
INVALID_LDAP_NEWRDN	NUMBER	1031
INVALID_LDAP_NEWPARENT	NUMBER	1032
INVALID_LDAP_DELETEOLDRDN	NUMBER	1033
INVALID_SSLWRL	NUMBER	1034
INVALID_SSLWALLETPASSWD	NUMBER	1035
INVALID_SSLAUTH	NUMBER	1036
Variables (Not Constants)
USE_EXCEPTION	BOOLEAN	TRUE
UTF8_CONVERSION	BOOLEAN	TRUE

Data Types -- this data structure is used to hold a list of berval values TYPE berval_collection IS TABLE OF RAW(32767) INDEX BY BINARY_INTEGER; -- Holds a pointer to the BER Element used for decoding an incoming message SUBTYPE ber_element IS RAW(32); -- Used to hold binary value SUBTYPE berval IS RAW(32000); -- this data structure is used to hold a list of binary values TYPE binval_collection IS TABLE OF RAW(32767) INDEX BY BINARY_INTEGER; -- this data structure is used to hold a list of berval values TYPE blob_collection IS TABLE OF BLOB INDEX BY BINARY_INTEGER; -- Used to pass LDAP control to the api TYPE ldapcontrol IS RECORD ( ldctl_oid VARCHAR2(256), ldctl_value BERVAL, ldctl_iscritical VARCHAR2(1)); -- Holds a pointer to an LDAP message SUBTYPE message IS RAW(32); -- Holds a pointer to an LDAP mod array SUBTYPE mod_array IS RAW(32); -- Holds a pointer to an LDAP session SUBTYPE session IS RAW(32) -- Used to hold a list of values TYPE string_collection IS TABLE of VARCHAR2(32767) INDEX BY BINARY_INTEGER; -- Used to pass time limit information to the LDAP api TYPE timeval IS RECORD ( seconds PLS_INTEGER, useconds PLS_INTEGER);

Dependencies

DBMS_LDAP_API_FFI	DBMS_SYS_ERROR	PLITBLM
DBMS_LDAP_UTL

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-31201	DBMS_LDAP: generic error: Invalid input pointer
ORA-31202	general_error
ORA-31203	initialization failed
ORA-31204	invalid session
ORA-31205	Invalid LDAP Auth method
ORA-31206	Invalid LDAP search scope
ORA-31207	Invalid LDAP search time value
ORA-31208	Invalid LDAP Message
ORA-31209	LDAP count_entry error
ORA-31210	LDAP get_dn error
ORA-31211	Invalid LDAP entry dn
ORA-31212	Invalid LDAP mod_array
ORA-31213	Invalid LDAP mod option
ORA-31214	Invalid LDAP mod type
ORA-31215	Invalid LDAP mod value
ORA-31216	Invalid LDAP rdn
ORA-31217	Invalid LDAP newparent
ORA-31218	Invalid LDAP deleteoldrdn
ORA-31219	Invalid LDAP notypes
ORA-31220	Invalid LDAP SSL wallet location
ORA-31221	Invalid LDAP SSL wallet passwd
ORA-31222	Invalid LDAP SSL authentication mode
ORA-31398	Not supporting MTS mode

First Available Not known

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbmsldap.sql

Subprograms

ADD_S	FIRST_ATTRIBUTE	NEXT_ENTRY
BER_FREE	FIRST_ENTRY	NLS_CONVERT_FROM_UTF8
BIND_S	FREE_MOD_ARRAY	NLS_CONVERT_TO_UTF8
CHECK_INTERFACE_VERSION	GET_DN	NLS_GET_DBCHARSET_NAME
COMPARE_S	GET_SESSION_INFO	OPEN_SSL
COUNT_ENTRIES	GET_TRACE_LEVEL	POPULATE_MOD_ARRAY
COUNT_VALUES	GET_VALUES	RENAME_S
COUNT_VALUES_BLOB	GET_VALUES_BLOB	SEARCH_S
COUNT_VALUES_LEN	GET_VALUES_LEN	SEARCH_ST
CREATE_MOD_ARRAY	INIT	SET_TRACE_LEVEL
DELETE	MODIFY_S	SIMPLE_BIND_S
DELETE_S	MODRDN2_S	UNBIND_S
ERR2STRING	MSGFREE	VALUE_FREE_BLOB
EXPLODE_DN	NEXT_ATTRIBUTE	Demos

ADD_S

Adds a new entry to the LDAP directory. The caller is blocked until the addition is complete dbms_ldap.add_s( ld IN session, entrydn IN VARCHAR2, modptr IN mod_array) RETURN PLS_INTEGER;

TBD

BER_FREE

Undocumented dbms_ldap.ber_free( ber IN ber_element, freebuf IN PLS_INTEGER);

TBD

BIND_S

Synchronously authenticates to the directory server using a Distinguished Name and some arbitrary credentials dbms_ldap.bind_s( ld IN session, dn IN VARCHAR2, cred IN VARCHAR2, meth IN PLS_INTEGER) RETURN PLS_INTEGER;

See Demos Below

CHECK_INTERFACE_VERSION

Checks the Support for the interface version dbms_ldap.check_interface_version(interface_version IN VARCHAR2) RETURN PLS_INTEGER;

TBD

COMPARE_S

Compares a value with a attribute value contained in an entry dbms_ldap.compare_s( ld IN session, dn IN VARCHAR2, attr IN VARCHAR2, value IN VARCHAR2) RETURN PLS_INTEGER;

TBD

COUNT_ENTRIES

Determines the number of entries in an LDAP result message chain dbms_ldap.count_entries( ld IN session, msg IN message) RETURN PLS_INTEGER;

See Demos Below

COUNT_VALUES

Counts the number of values returned by get_values() dbms_ldap. count_values(vals IN string_collection) RETURN PLS_INTEGER;

TBD

COUNT_VALUES_BLOB

Counts the number of values returned by get_values_blob() dbms_ldap.count_values_blob(vals IN blob_collection) RETURN PLS_INTEGER;

TBD

COUNT_VALUES_LEN

Counts the number of values returned by get_values_len() dbms_ldap.count_values_len(vals IN binval_collection) RETURN PLS_INTEGER;

TBD

CREATE_MOD_ARRAY

Gets the pointer of the ldapmod representation which contains size, count, and a pointer to an array of ldapmod structure dbms_ldap.create_mod_array(num IN PLS_INTEGER) RETURN mod_array;

TBD

DELETE

Deletes an entry from the LDAP directory dbms_ldap.delete( ld IN session, entrydn IN VARCHAR2) RETURN PLS_INTEGER;

TBD

DELETE_S

Deletes an entry from the LDAP directory. The caller is blocked until the deletion is complete dbms_ldap.delete_s( ld IN session, entrydn IN VARCHAR2) RETURN PLS_INTEGER;

TBD

ERR2STRING

Gets the string representation of an LDAP return code dbms_ldap.err2string(ldap_err IN PLS_INTEGER) RETURN VARCHAR2;

TBD

EXPLODE_DN

Breaks a Distinguished Name (DN) up into its components dbms_ldap.explode_dn( dn IN VARCHAR2, notypes IN PLS_INTEGER) RETURN string_collection;

TBD

FIRST_ATTRIBUTE

Returns the first attribute in an entry dbms_ldap.first_attribute( ld IN session, ldapentry IN message, ber_elem OUT ber_element) RETURN VARCHAR2;

See Demos Below

FIRST_ENTRY

Returns the first entry in a chain of results dbms_ldap.first_entry ( ld IN session, msg IN message) RETURN message;

See Demos Below

FREE_MOD_ARRAY

Frees up the memory used by the ldapmod representation (array) dbms_ldap.free_mod_array(modptr IN mod_array);

TBD

GET_DN

Retrieves the Distinguished Name of an entry dbms_ldap.get_dn( ld IN session, ldapentry IN message) RETURN VARCHAR2;

TBD

GET_SESSION_INFO

Undocumented dbms_ldap.get_session_info( ld IN session, data_type IN PLS_INTEGER, data OUT VARCHAR2) RETURN PLS_INTEGER;

TBD

GET_TRACE_LEVEL

To be used by Oracle Support Analysts ONLY according to the docs dbms_ldap.get_trace_level RETURN PLS_INTEGER;

SELECT dbms_ldap.get_trace_level

      FROM dual;

      

      GET_TRACE_LEVEL

      ---------------

              0

GET_VALUES

Retrieves values associated with a char attribute for a given entry dbms_ldap.get_values( ld IN session, ldapentry IN message, attr IN VARCHAR2) RETURN string_collection;

See Demos Below

GET_VALUES_BLOB

Retrieves large binary values (greater than 32kb) associated with an attribute for a given entry dbms_ldap.get_values_blob( ld IN session, ldapentry IN message, attr IN VARCHAR2) RETURN BLOB_COLLECTION;

See Demos Below

GET_VALUES_LEN

Retrieves binary values associated with an attribute for a given entry dbms_ldap.get_values_len( ld IN session, ldapentry IN message, attr IN VARCHAR2) RETURN BINVAL_COLLECTION;

TBD

INIT

Initializes the LDAP library and return a session handler for use in subsequent calls dbms_ldap.init( hostname IN VARCHAR2, portnum IN PLS_INTEGER) RETURN session;

See Demos Below

MODIFY_S

Modifies an existing LDAP directory entry. The caller is blocked until the modification is complete dbms_ldap.modify_s( ld IN session, entrydn IN VARCHAR2, modptr IN MOD_ARRAY) RETURN PLS_INTEGER;

TBD

MODRDN2_S

Renames the given entry to have the new relative distinguished name. The caller is blocked until the renaming is complete dbms_ldap.modrdn2_s( ld IN session, entrydn IN VARCHAR2, newrdn IN VARCHAR2, deleteoldrdn IN PLS_INTEGER) RETURN PLS_INTEGER;

TBD

MSGFREE

Undocumented dbms_ldap.msgfree(lm IN message) RETURN PLS_INTEGER;

SELECT dbms_ldap.msgfree(utl_raw.cast_to_raw('Library'))

      FROM dual;

      SELECT 
      dbms_ldap.msgfree(utl_raw.cast_to_raw('Library'))

             *

      ERROR at line 1:

      ORA-31201: DBMS_LDAP: generic error: Invalid input pointer

      ORA-06512: at "SYS.DBMS_LDAP_API_FFI", line 747

      ORA-06512: at "SYS.DBMS_LDAP", line 1318

NEXT_ATTRIBUTE

Returns the next attribute contained in an entry dbms_ldap.next_attribute( ld IN session, ldapentry IN message, ber_elem IN BER_ELEMENT) RETURN VARCHAR2;

See Demos Below

NEXT_ENTRY

Returns the next entry in a chain of search results dbms_ldap.next_entry( ld IN session, msg IN message) RETURN MESSAGE;

See Demos Below

NLS_CONVERT_FROM_UTF8

Convert to the NLS character set from UTF8
Overload 1 dbms_ldap.nls_convert_from_utf8(data_utf8 IN VARCHAR2) RETURN VARCHAR2;

See NLS_CONVERT_TO_UTF8 Demo Below

Overload 2 dbms_ldap.nls_convert_from_utf8(data_utf8 IN string_collection) RETURN string_collection;

TBD

NLS_CONVERT_TO_UTF8

Convert from the NLS character set to UTF8
Overload 1 dbms_ldap.nls_convert_to_utf8(data_utf8 IN VARCHAR2) RETURN VARCHAR2;

DECLARE

       testStr VARCHAR2(30) := 'This is a test';

       msgStr  VARCHAR2(120);

      BEGIN

        testStr := dbms_ldap.nls_convert_to_utf8(testStr);

      

        SELECT DUMP(testStr,1016)

        INTO msgStr

        FROM dual;

      

        dbms_output.put_line(msgStr);

      

        testStr := dbms_ldap.nls_convert_from_utf8(testStr);

      

        SELECT DUMP(testStr,1016)

        INTO msgStr

        FROM dual;

      

        dbms_output.put_line(msgStr);

      END;

      /

      Typ=1 Len=14 CharacterSet=AL32UTF8: 
      54,68,69,73,20,69,73,20,61,20,74,65,73,74

      Typ=1 Len=14 CharacterSet=AL32UTF8: 
      54,68,69,73,20,69,73,20,61,20,74,65,73,74

      

      PL/SQL procedure successfully completed.

Overload 2 dbms_ldap.nls_convert_to_utf8(data_utf8 IN string_collection) RETURN string_collection;

TBD

NLS_GET_DBCHARSET_NAME

Undocumented dbms_ldap.nls_get_dbcharset_name RETURN VARCHAR2;

SELECT dbms_ldap.nls_get_dbcharset_name

      FROM dual;

      

      NLS_GET_DBCHARSET_NAME

      -----------------------

      AL32UTF8

OPEN_SSL

Establishes a SSL connection dbms_ldap.open_ssl( ld IN session, sslwrl IN VARCHAR2, sslwalletpasswd IN VARCHAR2, sslauth IN PLS_INTEGER) RETURN PLS_INTEGER;

TBD

POPULATE_MOD_ARRAY

Populates the ldapmod structure, string value. If the return modptr is NULL, then there is an error

Overload 1 dbms_ldap.populate_mod_array( modptr IN mod_array, mod_op IN PLS_INTEGER, mod_type IN VARCHAR2, modval IN string_collection);

TBD

Populates the ldapmod structure, binary value. If the return modptr is NULL, then there is an error

Overload 2 dbms_ldap.populate_mod_array( modptr IN mod_array, mod_op IN PLS_INTEGER, mod_type IN VARCHAR2, modbval IN berval_collection);

TBD

Populates the ldapmod structure, large binary value (greater than 32kb). If the return modptr is NULL, then there is an error

Overload 3 dbms_ldap.populate_mod_array( modptr IN mod_array, mod_op IN PLS_INTEGER, mod_type IN VARCHAR2, modbval IN blob_collection);

TBD

RENAME_S

Performs modify dn operation dbms_ldap.rename_s( ld IN session, dn IN VARCHAR2, newrdn IN VARCHAR2, newparent IN VARCHAR2, deleteoldrdn IN PLS_INTEGER, serverctrls IN ldapcontrol DEFAULT NULL, clientctrls IN ldapcontrol DEFAULT NULL) RETURN PLS_INTEGER;

TBD

SEARCH_S

Searches for directory entries dbms_ldap.search_s ( ld IN session, base IN VARCHAR2, scope IN PLS_INTEGER, filter IN VARCHAR2, attrs IN string_collection, attronly IN PLS_INTEGER, res OUT message) RETURN PLS_INTEGER;

TBD

SEARCH_ST

Searches for directory entries, respecting a local timeout dbms_ldap.search_st ( ld IN session, base IN VARCHAR2, scope IN PLS_INTEGER, filter IN VARCHAR2, attrs IN string_collection, attronly IN PLS_INTEGER, tv IN TIMEVAL, res OUT message) RETURN PLS_INTEGER;

TBD

SET_TRACE_LEVEL

To be used by Oracle Support Analysts ONLY dbms_ldap.set_trace_level(new_trace_level IN PLS_INTEGER);

TBD

SIMPLE_BIND_S

Synchronously authenticates to the directory server using a Distinguished Name and password dbms_ldap.simple_bind_s( ld IN session, dn IN VARCHAR2, passwd IN VARCHAR2) RETURN PLS_INTEGER;

TBD

UNBIND_S

Synchronously disposes of an LDAP session, freeing all associated resources dbms_ldap.unbind_s(ld IN OUT SESSION) RETURN PLS_INTEGER;

See Demos Below

VALUE_FREE_BLOB

Frees the memory associated with binary attribute values that were returned by get_values_blob() function dbms_ldap.value_free_blob(vals IN OUT BLOB_COLLECTION);

TBD

Demos

Oracle's search.sql script ------------------------------------------------------------------------ -- $Header: $ -- -- Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved. -- -- FILE -- search.sql: A sample search program using DBMS_LDAP -- -- DESCRIPTION -- -- This SQL file contains the PL/SQL code required to perform -- a typical search against an LDAP server. -- -- This script assumes the following: -- LDAP server hostname: NULL (local host) -- LDAP server portnumber: 389 -- Directory container for employee records: o=acme, dc=com -- Username/Password for Directory Updates: cn=orcladmin/welcome -- -- NOTES -- Run this file after you have run the 'trigger.sql' and 'empdata.sql' -- scripts to see what entries were added by the database triggers. -- -- -- MODIFIED (MM/DD/YY) -- ****** 04/29/01 - Add calls to ber_free and msgfree -- ****** 07/21/00 - created ------------------------------------------------------------------------ set serveroutput on DECLARE retval PLS_INTEGER; my_session dbms_ldap.session; my_attrs dbms_ldap.string_collection; my_message dbms_ldap.message; my_entry dbms_ldap.message; entry_index PLS_INTEGER; my_dn VARCHAR2(256); my_attr_name VARCHAR2(256); my_ber_elmt dbms_ldap.ber_element; attr_index PLS_INTEGER; i PLS_INTEGER; my_vals dbms_ldap.string_collection; ldap_host VARCHAR2(256); ldap_port VARCHAR2(256); ldap_user VARCHAR2(256); ldap_passwd VARCHAR2(256); ldap_base VARCHAR2(256); BEGIN retval := -1; -- customize the following variables as needed ldap_host := NULL ; ldap_port := '389'; ldap_user := 'cn=orcladmin'; ldap_passwd := 'welcome'; ldap_base := 'o=acme,dc=com'; -- end of customizable settings dbms_output.put_line('DBMS_LDAP Search Example '); dbms_output.put_line('to directory .. '); dbms_output.put_line(RPAD('LDAP Host ',25,' ') || ': ' || ldap_host); dbms_output.put_line(RPAD('LDAP Port ',25,' ') || ': ' || ldap_port); -- choosing exceptions to be raised by DBMS_LDAP library dbms_ldap.use_exception := TRUE; my_session := dbms_ldap.init(ldap_host,ldap_port); dbms_output.put_line(RPAD('LDAP session ',25,' ') || ': ' || RAWTOHEX(SUBSTR(my_session,1,8)) || '(returned from init)'); -- bind to the directory retval := dbms_ldap.simple_bind_s(my_session, ldap_user, ldap_passwd); dbms_output.put_line(RPAD('simple_bind_s Returns ',25,' ') || ': ' || TO_CHAR(retval)); -- issue the search my_attrs(1) := '*'; -- retrieve all attributes retval := dbms_ldap.search_s(my_session, ldap_base, dbms_ldap.scope_subtree, 'objectclass=*', my_attrs, 0, my_message); dbms_output.put_line(RPAD('search_s Returns ',25,' ') || ': ' || TO_CHAR(retval)); dbms_output.put_line(RPAD('LDAP message ',25,' ') || ': ' || RAWTOHEX(SUBSTR(my_message,1,8)) || '(returned from search_s)'); -- count the number of entries returned retval := dbms_ldap.count_entries(my_session, my_message); dbms_output.put_line(RPAD('Number of Entries ',25,' ') || ': ' || TO_CHAR(retval)); dbms_output.put_line('------------------------------------------------'); -- get the first entry my_entry := dbms_ldap.first_entry(my_session, my_message); entry_index := 1; -- loop through each of the entries one by one WHILE my_entry IS NOT NULL LOOP -- print the current entry my_dn := DBMS_LDAP.get_dn(my_session, my_entry); -- dbms_output.put_line(' entry #' || TO_CHAR(entry_index) || -- ' entry ptr: ' || RAWTOHEX(SUBSTR(my_entry,1,8))); dbms_output.put_line(' dn: ' || my_dn); my_attr_name:=dbms_ldap.first_attribute(my_session,my_entry,my_ber_elmt); attr_index := 1; WHILE my_attr_name IS NOT NULL LOOP my_vals := DBMS_LDAP.get_values (my_session, my_entry, my_attr_name); IF my_vals.COUNT > 0 THEN FOR i in my_vals.FIRST..my_vals.LAST loop dbms_output.put_line(' ' || my_attr_name || ' : ' || SUBSTR(my_vals(i),1,200)); END LOOP; END IF; my_attr_name := dbms_ldap.next_attribute(my_session,my_entry, my_ber_elmt); attr_index := attr_index+1; END LOOP; -- free ber_element dbms_ldap.ber_free(my_ber_elmt, 0); my_entry := dbms_ldap.next_entry(my_session, my_entry); dbms_output.put_line('============================================='); entry_index := entry_index+1; END LOOP; -- free LDAP Message retval := dbms_ldap.msgfree(my_message); -- unbind from the directory retval := dbms_ldap.unbind_s(my_session); dbms_output.put_line(RPAD('unbind_res Returns ',25,' ') || ': ' ||TO_CHAR(retval)); dbms_output.put_line('Directory operation Successful .. exiting'); 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'); END; / --show errors

Get the active directory value objectSID (Binary Array value) CREATE OR REPLACE PROCEDURE UpdateSSOUserId AUTHID DEFINER IS TYPE filter_tbl IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; -- required parameters l_ldap_base VARCHAR2(256) := 'base'; l_ldap_host VARCHAR2(256) := 'host'; -- your LDAP server l_ldap_passwd VARCHAR2(256) := 'oracle12_3' ; l_ldap_port VARCHAR2(256) := '389'; -- your LDAP port l_ldap_user VARCHAR2(256) := 'user' ; MEMBER_OF VARCHAR2(500) := 'member'; l_attr_name VARCHAR2(256); l_attrs dbms_ldap.string_collection; l_ber_element dbms_ldap.ber_element; l_bin_val_count NUMBER; l_buffer VARCHAR2(32767); l_entry dbms_ldap.message; l_filter VARCHAR2(32767); l_filters filter_tbl; l_message dbms_ldap.message; l_objectSid RAW(32767); l_raw dbms_ldap.binval_collection; l_retval PLS_INTEGER; l_session dbms_ldap.session; l_vals dbms_ldap.string_collection; searchFilter VARCHAR2(1000); BEGIN -- connect to LDAP server l_session := dbms_ldap.init(hostname => l_ldap_host, portnum => l_ldap_port); l_retval := dbms_ldap.simple_bind_s(ld => l_session, dn => l_ldap_user, passwd => l_ldap_passwd); -- get all attributes l_attrs(1) := 'givenName'; --'*'; -- retrieve all attributes l_attrs(2) := 'objectSid'; --'*'; -- retrieve all attributes searchFilter := 'userPrincipalName=pb*'; l_retval := dbms_ldap.search_s(ld => l_session, base => l_ldap_base, scope => dbms_ldap.scope_subtree, filter => searchFilter, attrs => l_attrs, attronly => 0, res => l_message); IF dbms_ldap.count_entries(ld => l_session, msg => l_message) > 0 THEN -- retrieve search values l_entry := dbms_ldap.first_entry(ld => l_session,msg => l_message); WHILE l_entry IS NOT NULL LOOP -- retrieve entry attributes dbms_output.put_line('---------------------------------------'); l_attr_name := dbms_ldap.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element); -- attributes_loop WHILE l_attr_name IS NOT NULL LOOP l_vals := dbms_ldap.get_values(ld => l_session, ldapentry => l_entry, attr => l_attr_name); << values_loop >> FOR i IN l_vals.FIRST .. l_vals.LAST LOOP dbms_output.put_line('Full Attribute Name: ' || l_attr_name || ' = ' || rawtohex(l_vals(i))); IF (l_attr_name IN ('objectSid')) THEN my_raw := dbms_ldap.get_values_blob(ld => l_session, ldapentry => l_entry, attr => l_attr_name); dbms_output.put_line('ATTIBUTE_NAME full : ' || l_attr_name || ' = ' ||dbms_lob.substr(my_raw(i))); ELSE dbms_output.put_line('Full Attribute Name: ' || l_attr_name || ' = ' || l_vals(i)); END IF; END LOOP values_loop; l_attr_name := dbms_ldap.next_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element); END LOOP attibutes_loop; l_entry := dbms_ldap.next_entry(ld => l_session,msg => l_entry); END LOOP entry_loop; END IF; -- disconnect from the LDAP server l_retval := dbms_ldap.unbind_s(ld => l_session); dbms_output.put_line('l_retval: ' || l_retval); END UpdateSSOUserId; /