Solved

ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.

Posted on 2006-10-31
8
4,765 Views
Last Modified: 2009-12-16
Hi,

I received the following error. If someone worked on it then please explain me,

SQL> @C:\oracle\ora92\ldap\demo\plsql\trigger.sql
Dropping Table EMP ..
 
Table dropped.
 
Creating Table EMP ..
 
Table created.
 
Creating Trigger LDAP_EMP ..
 
Trigger created.
 
SQL> @C:\oracle\ora92\ldap\demo\plsql\empdata.sql
Trigger [LDAP_EMP]: Replicating changes to directory ..
LDAP Host                :
LDAP Port                : 389
Error code    : -31203
Error Message : ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
Exception encountered .. exiting
 
1 row created.
0
Comment
Question by:someone_genius
  • 4
  • 2
8 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
what is the code inside this @C:\oracle\ora92\ldap\demo\plsql\empdata.sql ?

also paste as to what is there in @C:\oracle\ora92\ldap\demo\plsql\trigger.sql

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
0
 

Author Comment

by:someone_genius
Comment Utility
trigger.sql code
==============
------------------------------------------------------------------------
-- $Header: $
--
-- Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
--  
-- FILE        
--      trigger.sql: A sample trigger using DBMS_LDAP
--
-- DESCRIPTION
--
--      This SQL file creates a database table called 'EMP'
--      and creates a trigger on it called LDAP_EMP which will
--      synchronize all changes happening to the table with an
--      LDAP server.
--      The changes to the database table are reflected/replicated
--      to the LDAP directory using the DBMS_LDAP package.
--
--      This script assumes the following:
--       LDAP server hostname:NULL
--       LDAP server portnumber:389
--       Directory container for employee records: o=acme, dc=com
--       Username/Password for Directory Updates: cn=orcladmin/welcome1
--
--      The aforementioned variables could be customized for different
--      environments by changing the appropriate variables in the code below.
--        
--      Table Definition:
--      Employee Details(Columns) in Database Table(EMP):
--      ------------------------------------------------------
--      EMP_ID       - Number    
--      FIRST_NAME   - Varchar2
--      LAST_NAME    - Varchar2
--      MANAGER_ID   - Number
--      PHONE_NUMBER - Varchar2
--      MOBILE       - Varchar2
--      ROOM_NUMBER  - Varchar2
--      TITLE        - Varchar2
--    
--      LDAP Schema Definition & mapping to relational schema EMP:
--      Corresponding Data representation in LDAP directory:
--      ----------------------------------------------------
--        DN - [cn=<FIRST_NAME LAST_NAME>, o=acme, dc=com]
--        cn              - <FIRST_NAME LAST_NAME>
--        sn              - <LAST_NAME>
--        givenname       - <FIRST_NAME>
--        manager         - <DN>
--        telephonenumber - <PHONE_NUMBER>
--        mobile          - <MOBILE>
--        employeeNumber  - <EMP_ID>
--        userpassword    - <FIRST_NAME>
--        objectclass     - person
--                          organizationalperson
--                          inetOrgPerson
--                          top
--
--
--
--
--  MODIFIED   (MM/DD/YY)
--  ******      07/21/00 - created
---------------------------------------------------------

-- Creating EMP table

PROMPT Dropping Table EMP ..

drop table EMP;


PROMPT Creating Table EMP ..

CREATE TABLE EMP (
   EMP_ID    NUMBER,     -- Employee Number
   FIRST_NAME  VARCHAR2(256), -- First Name
   LAST_NAME  VARCHAR2(256), -- Last Name
   MANAGER_ID  NUMBER,     -- Manager Number
   PHONE_NUMBER VARCHAR2(256), -- Telephone Number
   MOBILE   VARCHAR2(256), -- Mobile Number
   ROOM_NUMBER  VARCHAR2(256), -- Room Number
   TITLE   VARCHAR2(256)  -- Title in the company
 );


-- Creating Trigger LDAP_EMP

PROMPT Creating Trigger LDAP_EMP ..

CREATE OR REPLACE TRIGGER LDAP_EMP
AFTER INSERT OR DELETE OR UPDATE ON EMP
FOR EACH ROW

DECLARE
 retval   PLS_INTEGER;
 emp_session  DBMS_LDAP.session;
 emp_dn   VARCHAR2(256);
 emp_rdn   VARCHAR2(256);
 emp_array  DBMS_LDAP.MOD_ARRAY;
 emp_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;
 -- Please 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('Trigger [LDAP_EMP]: Replicating changes ');
 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;

 -- Initialize ldap library and get session handle.
 emp_session := DBMS_LDAP.init(ldap_host,ldap_port);

 DBMS_OUTPUT.PUT_LINE (RPAD('Ldap session ',25,' ')  || ': ' ||
     RAWTOHEX(SUBSTR(emp_session,1,8)) ||
     '(returned from init)');
       
 -- Bind to the directory
 retval := DBMS_LDAP.simple_bind_s(emp_session,
       ldap_user,ldap_passwd);

    DBMS_OUTPUT.PUT_LINE(RPAD('simple_bind_s Returns ',25,' ') || ': '
          || TO_CHAR(retval));
 
 -- Process New Entry in the database

 IF INSERTING THEN

  -- Create and setup attribute array for the New entry
  emp_array := DBMS_LDAP.create_mod_array(14);

  -- RDN to be - cn="FIRST_NAME LAST_NAME"

  emp_vals(1) := :new.FIRST_NAME || ' ' || :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'cn',emp_vals);

  emp_vals(1) := :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'sn',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'givenname',emp_vals);

  emp_vals(1) := 'top';
  emp_vals(2) := 'person';
  emp_vals(3) := 'organizationalPerson';
  emp_vals(4) := 'inetOrgPerson';

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'objectclass',emp_vals);

  emp_vals.DELETE;
  emp_vals(1) := :new.PHONE_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'telephonenumber',emp_vals);

  emp_vals(1) := :new.MOBILE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'mobile',emp_vals);

  emp_vals(1) := :new.ROOM_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'roomNumber',emp_vals);

  emp_vals(1) := :new.TITLE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'title',emp_vals);

  emp_vals(1) := :new.EMP_ID;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'employeeNumber',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_ADD,
            'userpassword',emp_vals);
   
  -- DN for Entry to be Added under 'ldap_base' [o=acme, dc=com]
 
  emp_dn := 'cn=' || :new.FIRST_NAME || ' ' ||
   :new.LAST_NAME || ', ' || ldap_base ;
  DBMS_OUTPUT.PUT_LINE(RPAD('Adding Entry for DN ',25,' ') || ': ['
           || emp_dn || ']');

  -- Add new Entry to ldap directory
  retval := DBMS_LDAP.add_s(emp_session,emp_dn,emp_array);
     DBMS_OUTPUT.PUT_LINE(RPAD('add_s Returns ',25,' ')  || ': '
           || TO_CHAR(retval));

  -- Free attribute array (emp_array)
  DBMS_LDAP.free_mod_array(emp_array);

 END IF; -- INSERTING

 -- Process Entry deletion in database

 IF DELETING THEN

  -- DN for Entry to be deleted under 'ldap_base' [o=acme, dc=com]

  emp_dn := 'cn=' || :old.FIRST_NAME || ' ' ||
   :old.LAST_NAME || ', ' || ldap_base ;
  DBMS_OUTPUT.PUT_LINE(RPAD('Deleting Entry for DN ',25,' ') ||
         ': [' || emp_dn || ']');

  -- Delete entry in ldap directory
  retval := DBMS_LDAP.delete_s(emp_session,emp_dn);
     DBMS_OUTPUT.PUT_LINE(RPAD('delete_s Returns ',25,' ') || ': ' ||
           TO_CHAR(retval));

 END IF; -- DELETING

 -- Process updated Entry in database

 IF UPDATING THEN


  -- Since two Table columns(in this case) constitue a RDN
  -- check for any changes and update RDN in ldap directory
  -- before updating any other attributes of the Entry.

  IF :old.FIRST_NAME <> :new.FIRST_NAME OR
     :old.LAST_NAME  <> :new.LAST_NAME THEN

   emp_dn := 'cn=' || :old.FIRST_NAME || ' ' ||
    :old.LAST_NAME || ', ' || ldap_base;

   emp_rdn := 'cn=' || :new.FIRST_NAME || ' ' || :new.LAST_NAME;

   DBMS_OUTPUT.PUT_LINE(RPAD('Renaming OLD DN ',25,' ') ||
           ': [' || emp_dn || ']');
   DBMS_OUTPUT.PUT_LINE(RPAD(' => NEW RDN ',25,' ') ||
           ': [' || emp_rdn || ']' );
   retval := DBMS_LDAP.modrdn2_s(emp_session,emp_dn,emp_rdn,
             DBMS_LDAP.MOD_DELETE);
   DBMS_OUTPUT.PUT_LINE(RPAD('modrdn2_s Returns ',25,' ') || ': ' ||
             TO_CHAR(retval));
  END IF;

  -- DN for Entry to be updated under 'ldap_base' [o=acme, dc=com]

  emp_dn := 'cn=' || :new.FIRST_NAME || ' ' ||
   :new.LAST_NAME || ', ' || ldap_base;

  DBMS_OUTPUT.PUT_LINE(RPAD('Updating Entry for DN ',25,' ') ||
            ': [' || emp_dn || ']');

  -- Create and setup attribute array(emp_array) for updated entry
  emp_array := DBMS_LDAP.create_mod_array(7);

  emp_vals(1) := :new.LAST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'sn',emp_vals);

  emp_vals(1) := :new.FIRST_NAME;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'givenname',emp_vals);

  emp_vals(1) := :new.PHONE_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'telephonenumber',emp_vals);

  emp_vals(1) := :new.MOBILE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'mobile',emp_vals);

  emp_vals(1) := :new.ROOM_NUMBER;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'roomNumber',emp_vals);

  emp_vals(1) := :new.TITLE;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'title',emp_vals);

  emp_vals(1) := :new.EMP_ID;

  DBMS_LDAP.populate_mod_array(emp_array,DBMS_LDAP.MOD_REPLACE,
            'employeeNumber',emp_vals);

  -- Modify entry in ldap directory
  retval := DBMS_LDAP.modify_s(emp_session,emp_dn,emp_array);

     DBMS_OUTPUT.PUT_LINE(RPAD('modify_s Returns ',25,' ') || ': ' ||
            TO_CHAR(retval));

  -- Free attribute array (emp_array)
  DBMS_LDAP.free_mod_array(emp_array);

 END IF; -- UPDATING

 -- Unbind from ldap directory
 retval := DBMS_LDAP.unbind_s(emp_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;
/

==============================

code for empdata.sql

------------------------------------------------------------------------
-- $Header: $
--
-- Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
--
-- FILE
--       empdata.sql: loads some data into EMP table
--
-- DESCRIPTION
--
--       This SQL file contains sample data to be inserted into the table EMP
--       which was defined in the file trigger.sql. These insertions will cause
--       corresponding updates to the LDAP server based on the trigger.
--        the EMP table.
--
-- NOTES
--      This file should be executed AFTER running trigger.sql.
--
-- MODIFIED    (MM/DD/YY)
--  ******      07/21/00 - created
------------------------------------------------------------------------

set serveroutput on size 30000
insert into emp values(1,'Harry','Bacon',NULL,'1 234 232 2343','1 234 345 4534','435O','Supervisor');
insert into emp values(2,'Tom','Baker',NULL,'1 234 232 2344','1 234 324 2423','234B','Programmer');
insert into emp values(3,'Leslie','Baldwin',NULL,'1 232 234 2323','1 234 233 2343','234A','Administrator');
insert into emp values(4,'Cheryl','Ball',NULL,'1 234 232 6789','1 234 232 2344','4345','Clerk');
insert into emp values(5,'Deanna','Ball',NULL,'1 234 232 4509','1 234 245 3245','3456','Supervisor');
insert into emp values(6,'Terrence','Banks',NULL,'1 234 232 4609','1 234 345 4534','3464','Administrative Assistant');
insert into emp values(7,'Kalid','Barker',NULL,'1 234 232 4698','1 234 345 5365','4356','Vice President');
insert into emp values(8,'Mathew','Anderson',NULL,'1 232 234 4098','1 234 345 5345','2346','Clerk');
insert into emp values(9,'Gilmore','Bacon',NULL,'1 234 232 3455','1 234 445 3423','4366','Clerk');
insert into emp values(10,'Natt','Ball',NULL,'1 234 232 4599','1 234 454 4545','4545','Manager');
====================
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 125 total points
Comment Utility
looks like this is the line which is causing the issue.

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

your ldap_host variable is null. why this is not initialized with others?

Can you try to dummy trigger and just put the code only till this and see whether this call is getting success or not.

Thanks
0
 

Author Comment

by:someone_genius
Comment Utility
Thanks for the reply

I have given NULL because it is default [ local host ] but i think you are right problem is here only can you please tell me for this I have to configure this local host as a LDAP server ?
If yes then please tell me steps for the same.

In advance thanks for the reply
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Yes, i think. But i am not sure on how to configure local host as LDAP server.

May be some expert has to help us.

thanks
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

771 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

11 Experts available now in Live!

Get 1:1 Help Now