Oracle DBMS_LDAP
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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 EXECUTE granted to PUBLIC
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;

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'));
*
ORA-31201: DBMS_LDAP: generic error: Invalid input pointer
 
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;

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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_LDAP_API_FFI
DBMS_LDAP_UTL
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved