SQL Developer

Hi,

I have created a package in Oracle sql developer to communicate with LDAP; which has two procedures and one function.

Two Procedures - One for establishing a connection and another for closing the connection

Function - To search a particular string in LDAP and return 1 if the string is exists else it will return 0.

Apart from this, I have a query in which I used to call the function. ie the output of the query will retrieve a set of records and one of the data (string) in a column will go as an input to the function. The function will search for the string in the LDAP and return the number. If the return value is 1 we will display it as 'Active' in our query. The query will run multiple times based on the user selection.

The reason for the creation of package is to keep the LDAP connection alive. If I use it as a single function each time (for each string) it has to establish a connection with LDAP, search the string and closing the connection and it badly affects the performance.

Now my issue is, I'm getting the error: Error(6,14): PLS-00103: Encountered the symbol "create" (seems to be issue occurs in sql developer)

I have tried lot and searched lot but nothing resolved the issue. Also tried by including slash (/) after the end; as suggessted in many forums, but no use. I'm getting the same error.

I have posted the package below. It would be great if some one help me to resolve it. The aim of the package is to keep the LDAP connection alive.

Thanks in advance.

CREATE OR REPLACE
PACKAGE GET_LDAP AS

PROCEDURE GET_LDAP_CON (l_retval PLS_INTEGER, l_session DBMS_LDAP.session);
FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2, l_attrs DBMS_LDAP.string_collection, l_count NUMBER) RETURN NUMBER;
PROCEDURE GET_LDAP_DISCON (l_retval PLS_INTEGER);
END GET_LDAP;

CREATE OR REPLACE
PACKAGE BODY GET_LDAP AS

ldapLogon:='FALSE';
l_ldap_host VARCHAR2(256) := '';
l_ldap_port VARCHAR2(256) := '';
l_ldap_user VARCHAR2(256) := '';
l_ldap_passwd VARCHAR2(256) := '';
l_ldap_base VARCHAR2(256) := '';
l_retval PLS_INTEGER;
l_session DBMS_LDAP.session;
l_attrs DBMS_LDAP.string_collection;
l_message DBMS_LDAP.message;
l_filter varchar2(35):='<xxxx>='|| loginname;
l_count NUMBER:=0;

-- code for procedure GET_LDAP_CON

PROCEDURE GET_LDAP_CON (l_session, l_retval) is
BEGIN

l_session := DBMS_LDAP.init(l_ldap_host, l_ldap_port);
l_retval := DBMS_LDAP.simple_bind_s(l_session, l_ldap_user, l_ldap_passwd);
END GET_LDAP_CON;

-- code for function GET_LDAP_SEARCH

FUNCTION GET_LDAP_SEARCH (loginname, l_attrs, l_count)
RETURN NUMBER
IS
BEGIN
DBMS_LDAP.USE_EXCEPTION := TRUE;
l_attrs(1) := 'XXXX';
l_retval := DBMS_LDAP.search_s(l_session, l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => l_filter, attrs => l_attrs, attronly => 0, res => l_message);
l_count:=DBMS_LDAP.count_entries(l_session, l_message);
return l_count;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error :'||SQLERRM);
return 0;

END GET_LDAP_SEARCH;

-- code for procedure GET_LDAP_DISCON

PROCEDURE GET_LDAP_DISCON (l_retval) is
BEGIN
l_retval := DBMS_LDAP.unbind_s(l_session);
END GET_LDAP_DISCON;

END GET_LDAP;