CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_LDAP
Version 11.1
 
General
Note: DBMS_LDAP
Source {ORACLE_HOME}/rdbms/admin/dbmsldap.sql

Constants

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
  NUMBER --
INVALID_LDAP_AUTH_METHOD NUMBER 1025
INVALID_LDAP_SEARCH_SCOPE NUMBER 1026
INVALID_LDAP_TIME_VALUE NUMBER 1027
INVALID_LDAP_MESSAGE NUMBER 1028
INVALID_LDAP_ENTRY_DN NUMBER 1029
INVALID_LDAPMOD NUMBER 1030
INVALID_LDAP_DN NUMBER 1031
INVALID_LDAP_NEWRDN NUMBER 1032
INVALID_LDAP_NEWPARENT NUMBER 1033
INVALID_LDAP_DELETEOLDRDN NUMBER 1034
INVALID_SSLWRL NUMBER 1035
INVALID_SSLWALLETPASSWD NUMBER 1036
INVALID_SSLAUTH NUMBER 1037

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

Exceptions

Number

Name
-31202 general_error
-31203 initialization failed
-31204 invalid session
-31205 Invalid LDAP Auth method
-31206 Invalid LDAP search scope
-31207 Invalid LDAP search time value
-31208 Invalid LDAP Message
-31209 LDAP count_entry error
-31210 LDAP get_dn error
-31211 Invalid LDAP entry dn
-31212 Invalid LDAP mod_array
-31213 Invalid LDAP mod option
-31214 Invalid LDAP mod type
-31215 Invalid LDAP mod value
-31216 Invalid LDAP rdn
-31217 Invalid LDAP newparent
-31218 Invalid LDAP deleteoldrdn
-31219 Invalid LDAP notypes
-31220 Invalid LDAP SSL wallet location
-31221 Invalid LDAP SSL wallet passwd
-31222 Invalid LDAP SSL authentication mode
-31398 Not supporting MTS mode
Variables dbms_ldap.use_exception BOOLEAN DEFAULT TRUE;
Security Model Owned by SYS with no privileges granted to any role
 
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;
TBD
 
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;
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);
 
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;
TBD
 
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. ldapmod structure contains mod_op, mod_type, and an array of string/berval. If the return value is NULL, then there is an error 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;
TBD
 
FIRST_ENTRY
Returns the first entry in a chain of results dbms_ldap.first_entry (ld IN SESSION, msg IN MESSAGE) RETURN MESSAGE;
TBD
 
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 dbms_ldap.get_trace_level RETURN PLS_INTEGER;
SELECT dbms_ldap.get_trace_level
FROM dual;
 
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;
TBD
 
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;
TBD
 
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;
TBD
 
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;
TBD
 
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;
TBD
 
NEXT_ENTRY
Returns the next entry in a chain of search results dbms_ldap.next_entry(ld IN SESSION, msg IN MESSAGE) RETURN MESSAGE;
TBD
 
NLS_CONVERT_FROM_UTF8
Overload 1 dbms_ldap.nls_convert_from_utf8 (data_utf8 IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 2 dbms_ldap.nls_convert_from_utf8 (data_utf8 IN STRING_COLLECTION)
RETURN STRING_COLLECTION;
TBD
 
NLS_CONVERT_TO_UTF8
Overload 1 dbms_ldap.nls_convert_to_utf8(data_utf8 IN VARCHAR2)
RETURN VARCHAR2;
TBD
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;
TBD
 
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;
TBD
 
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
 
DEMO

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');

-- 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');
END;
/

--show errors
 
Related Topics
dbms_ldap_utl
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [63 users online]    © 2010 psoug.org