How do I query Novell Identity Vault from Oracle PL/SQL?

Posted on 2009-02-13
Last Modified: 2012-05-06

I need to get user and group information stored in Novell Identity Vault for an Oracle APEX application that we are creating.  But I am having problem connecting to the server using LDAP.  The server is configured to use SSL and I can connect successfully using an LDAP tool that I have installed on the Oracle server.

When I attempt the call DBMS_LDAP.open_ssl I constantly get an ORA-31202 Unknown LDAP error.  I also created an Oracle Wallet, imported the SSL certificate from the LDAP server and changed the parameters to the DBMS_LDAP.open_ssl to include the wallet location and password, but still get the same error.  I have also tried all 3 sslauth parameters with same error.

Thanks for any help you can give!


 ldap_host            VARCHAR2(256);

 ldap_port            PLS_INTEGER;

 ldap_user            VARCHAR2(256);

 ldap_passwd          VARCHAR2(256);

 ldap_base            VARCHAR2(256);

 retval               PLS_INTEGER;

 my_session           DBMS_LDAP.session;

 subscriber_handle    DBMS_LDAP_UTL.HANDLE;

 sub_type             PLS_INTEGER;

 subscriber_id        VARCHAR2(2000);


 my_property_names    DBMS_LDAP.STRING_COLLECTION;

 my_property_values   DBMS_LDAP.STRING_COLLECTION;

 user_handle          DBMS_LDAP_UTL.HANDLE;

 user_id              VARCHAR2(2000);

 user_type            PLS_INTEGER;

 user_password        VARCHAR2(2000);

 my_mod_pset          DBMS_LDAP_UTL.MOD_PROPERTY_SET;

 my_attrs             DBMS_LDAP.STRING_COLLECTION;

 user_dn              VARCHAR2(256);


 -- Please customize the following variables as needed

 ldap_host := '' ;

 ldap_port := 636;

 ldap_user := 'xzy';

 ldap_passwd := 'abc';


 subscriber_id := NULL;


 user_id := 'xyz';

 user_password := 'abc';

 -- Choosing exceptions to be raised by DBMS_LDAP library.


 DBMS_OUTPUT.PUT_LINE('Input Parameters: ');

 DBMS_OUTPUT.PUT_LINE('LDAP HOST: ' || ldap_host);

 DBMS_OUTPUT.PUT_LINE('LDAP PORT: ' || ldap_port);


 IF subscriber_id IS NULL THEN



    DBMS_OUTPUT.PUT_LINE('REALM : ' || subscriber_id);


 DBMS_OUTPUT.PUT_LINE('USER ID : ' || user_id);



 -- Connect to the LDAP server

 -- and obtain and ld session.


 DBMS_OUTPUT.PUT('Connecting to ' || ldap_host || ' ...');

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

 DBMS_OUTPUT.PUT_LINE(': Connected.');


 -- Bind to the directory



 DBMS_OUTPUT.PUT('Binding to directory as ' || ldap_user || ' ... ');

 retval := DBMS_LDAP.open_ssl(  

                    ld              => my_session,  

                    sslwrl          => NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle  

                    sslwalletpasswd => NULL, -- wallet password  

                    sslauth         => 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3 

code continues, but fails at this point.

I also configured a Wallet and used:

 retval := DBMS_LDAP.open_ssl(  

                    ld              => my_session,  

                    'C:\Oracle\Wallets'          => NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle  

                   'password' => NULL, -- wallet password  

                    sslauth         => 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3

Open in new window

Question by:TerryDean
    LVL 18

    Assisted Solution

    Can you make a non-ssl connection from Oracle?  Consider going to the LDAP Group object in ConsoleOne or iManager and removing the check to "require TLS for clear text password" and see if that helps.

    Author Comment

    I finally got an answer back to my request to remove the "require TLS for clear text password" option and I was turned down since this is a production server.  So the non-ssl connection is not an option.  Any other suggestions?  Thanks!
    LVL 18

    Accepted Solution

    They are un-willing to let you try this, even for a couple of minutes to see if this is the cause?  If it is, it gives us something to track down.  Un-check, click OK...let you try, see results, go back click the check box on, click OK...

    I guess another route you can take is to try and trace the login process.  Do you have a NetWare server available?  If so, type the following at the server console and then switch to the DSTrace Console screen:

    load dstrace
    dstrace -all +LDAP screen on

    switch to the screen and then try to make your connection from Oracle and see if anything appears on the DSTrace console.  If it does, please post the error codes you're seeing.
    LVL 1

    Assisted Solution

    Are you using correct root CA certificate to establish SSL connection with option 2?
    Whichever is your CA, you may want to verify that your SSL certificate type and Root CA certificate installed in wallet match.Looking at your statement "imported the SSL certificate from the LDAP server" it does sound to me that you are not using rootCA certificate of server certificate.Please verify.

    Author Closing Comment

    Agency decided on a different solution.  Thanks for you help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now