title: Calling LDAP using JAVA from Oracle Procedure
body: <p><strong>Aim:</strong> Using a Oracle Procedure/Function to execute a Java Code to connect LDAP, fetch details from it and generate an excel file with these details.</p>
  
  <p><strong>Progress so far:</strong></p>
  
  <p>Presently, I have been successful in </p>
  
  <p>1) Calling a java code from oracle using external java libraries imported in oracle to generate a hard coded excel file on the server.</p>
  
  <p>2) Executing a java code separately to connect LDAP, fetch details and generate an excel.</p>
  
  <p>But when trying to call the java code from a procedure to connect LDAP, fetch details and generate Excel, the code is executing successfully and generating an Excel file but <strong>THIS EXCEL FILE IS BLANK</strong>.</p>
  
  <p>Also, the following error message is coming,
  <strong>error when trying to create the context</strong></p>
  
  <p>Following is the sample code</p>
  
  <p>1) <strong>LOADING EXTERNAL JARS</strong></p>
  
  <pre><code>loadjava -r -v -u  username/password  -s -g PUBLIC /oracle/awr/poi-3.7.jar
  </code></pre>
  
  <p>2) <strong>PACKAGE AND PACKAGE BODY</strong></p>
  
  <pre><code>CREATE OR REPLACE PACKAGE FetchADDetails AS 
  FUNCTION callGenerateReport(p1 IN varchar2) RETURN VARCHAR2; 
  END FetchADDetails;
  /
  CREATE OR REPLACE PACKAGE BODY FetchADDetails AS 
  FUNCTION callGenerateReport(p1 IN varchar2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'FetchADDetails.callGenerateReport(java.lang.String) 
  return java.lang.String'; 
  END FetchADDetails;
  /
  </code></pre>
  
  <p>3) <strong>COMPILING JAVA CLASS</strong></p>
  
  <pre><code>CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "FetchADDetails" AS
  public class FetchADDetails
  {
      public static void main(String[] args)
      {
          System.out.println(callGenerateReport(null));
      }
  
      public static ArrayList&lt;String&gt; fetchDetails(String userid, String password)
      {
          ArrayList&lt;String&gt; fetchedData=new ArrayList&lt;String&gt;();
          String url = "ldap://IP_ADDRESS";
          Hashtable&lt;String,String&gt; env = new Hashtable&lt;String,String&gt;();
          String securityPrincipal = userid+"@DOMAIN_NAME";
          String securityCredentials = password;
          env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
          env.put(Context.PROVIDER_URL, url);
          env.put(Context.SECURITY_AUTHENTICATION, "simple");
          env.put(Context.SECURITY_PRINCIPAL, securityPrincipal);
          env.put(Context.SECURITY_CREDENTIALS, securityCredentials);
  
          try
          {           
              DirContext ctx = new InitialDirContext(env);
              try
              {
                  SearchControls searchCtls = new SearchControls();
                  String[] returnedAtts = {"sn", "givenName", "displayname", "mail", "telephonenumber"};
                  searchCtls.setReturningAttributes(returnedAtts);
                  searchCtls.setSearchScope(2);
  
                  String searchFilter = "(&amp;(objectClass=user)(|(userPrincipalName=" + userid + "@DOMAIN_NAME)))";
                  String searchBase = "DC=corp,DC=DOMAIN_NAME,DC=com";
  
                  NamingEnumeration&lt;SearchResult&gt; answer = ctx.search(searchBase, searchFilter, searchCtls);
                  //System.out.println(answer.next().getAttributes());
                  if (answer.hasMore())
                  {
                      Attributes attrs = ((SearchResult) answer.next()).getAttributes();
                      String fullName=(String) attrs.get("displayname").get();
                      String eMail;
                      String phoneNumber;
                      if(attrs.get("mail")==null)
                      {
                          eMail="NA";
                      }
                      else
                      {eMail=(String) attrs.get("mail").get();}
                      if(attrs.get("telephonenumber")==null)
                      {phoneNumber="NA";}
                      else
                      {phoneNumber=(String) attrs.get("telephonenumber").get();}
                      //System.out.println(userid.toUpperCase()+","+fullName+","+eMail+","+phoneNumber);                  
                      fetchedData.add(userid.toUpperCase()+","+fullName+","+eMail+","+phoneNumber);
                  }
                  else
                  {throw new Exception("Invalid User");}
  
              } 
              catch (Exception ex){ex.printStackTrace();}
              ctx.close();
          }
          catch (AuthenticationNotSupportedException ex){System.out.println("The authentication is not supported by the server");}
          catch (AuthenticationException ex){System.out.println("incorrect password or username");}
          catch (NamingException ex){System.out.println("error when trying to create the context");}
          return fetchedData;
      }
  
      public static String callGenerateReport(String text)
      {
          ArrayList&lt;String&gt; adDetailsList=new ArrayList&lt;String&gt;();
          adDetailsList=fetchDetails(&lt;username&gt;,&lt;password&gt;);
          generateReport(adDetailsList);
          return "Successful!!!";
      }
  
      public static void generateReport(ArrayList&lt;String&gt; adDetailsList)
      {
          HSSFWorkbook workbook = new HSSFWorkbook();
          HSSFSheet activeDirectoryDetails = workbook.createSheet("Active Directory Details");        
  
          **CODE GOES HERE**
  
          try
          {
              FileOutputStream out=new FileOutputStream(new File("MyFilePath/Test.xls"));
              workbook.write(out);
              out.close();             
          }
          catch (FileNotFoundException e){e.printStackTrace();}
          catch (IOException e){e.printStackTrace();}
      }
  }
  </code></pre>
  
  <p>4) <strong>CALL FUNCTION</strong></p>
  
  <pre><code>begin
  declare
  result varchar2(4000);
  begin
  dbms_output.ENABLE;
  result := FetchADDetails.callGenerateReport('My First Excel');
  dbms_output.put_line(substr('poi result='||result,1,250));
  exception
  when OTHERS then
  dbms_output.put_line(substr('poi error='||SQLERRM,1,250));
  end;
  end;
  </code></pre>
  
  <p>This is running successfully but generating a blank excel file. Please help me in resolving the issue and generating an excel file with details.</p>