Oracle | DBMS

Oracle DBMS_LDAP Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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; /