Oralce Error ORA-06503 (PL/SQL: Function returned without value)

In my Oralce (11gR2) database I wrote a function and when I call it, it works with usual parameters but when called with some "unusual parameters" (I could not figure out their values) as I call the function from a script and the parameters are decided dynamically.

I have attached the entire source code of the function. It takes two varchar2 parameters.
It immediately returns if the 1st input parameter is null or the 2nd input parameter is not one of the 5 pre-mentioned values.

It also returns right away if the LDAP serach returns no value.

However, I wrote a    EXCEPTION
        WHEN OTHERS THEN

there if I add the RETURN NULL; (currently commented in code) the error (error that I attached) goes away but the return value of the function becomes NULL all the time which I don't want.

How can I force the function to return a NULL value in the event of sudden exit (causing the error attached) but otherwise return the value that I want to return?
FUNCTION LDAP_SEARCH (p_in IN VARCHAR2, p_attib IN VARCHAR2) RETURN VARCHAR2 AS
    ldap_host                      VARCHAR2(512);          -- LDAP Directory Host
    ldap_port                      VARCHAR2(512);          -- LDAP Directory Port
    ldap_baseDN                    VARCHAR2(512);          -- Starting (base) DN
    retval                         PLS_INTEGER;            -- API return values.
    my_session                     DBMS_LDAP.SESSION;      -- Store LDAP Session
    res_attrs                      DBMS_LDAP.STRING_COLLECTION;
    search_filter                  VARCHAR2(512);
    res_message                    DBMS_LDAP.MESSAGE;
    temp_entry                     DBMS_LDAP.MESSAGE;
    temp_attr_name                 VARCHAR2(512);
    temp_ber_elmt                  DBMS_LDAP.BER_ELEMENT;
    attr_index                     PLS_INTEGER;
    temp_vals                      DBMS_LDAP.STRING_COLLECTION;
    ret_attr_value                 VARCHAR2(512);
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    retval       := -1;
    ldap_host    := 'myco.com' ;
    ldap_port    := '389';
    ldap_baseDN  := 'ou=People, ou=Internet, dc=xxx, dc=com';
    DBMS_LDAP.USE_EXCEPTION := TRUE;
    my_session := DBMS_LDAP.INIT(ldap_host, ldap_port);

    retval := DBMS_LDAP.SIMPLE_BIND_S(my_session, '', '');

    IF TRIM(p_in) IS NULL THEN
      RETURN NULL;
    END IF;

    IF TRIM(LOWER(p_attib)) = 'cn' THEN
    res_attrs(1) := 'cn';
    ELSIF TRIM(LOWER(p_attib)) = 'xxx' THEN
    res_attrs(1) := 'xxx';
    ELSIF TRIM(LOWER(p_attib)) = 'xxx' THEN
    res_attrs(1) := 'xxxx';
    ELSIF TRIM(LOWER(p_attib)) = 'xxx' THEN
    res_attrs(1) := 'xxx';
    ELSIF TRIM(LOWER(p_attib)) = 'xxx' THEN
    res_attrs(1) := 'xxx';
    ELSIF TRIM(LOWER(p_attib)) = 'xxx' THEN
    res_attrs(1) := 'xxx';
    ELSE
    RETURN NULL;
    END IF;

    DBMS_OUTPUT.PUT_LINE('TRIM(LOWER(p_attib)): ' || TRIM(LOWER(p_attib)) || ', res_attrs(1) val: ' || res_attrs(1));

    search_filter   := 'myattrib=' || TRIM(p_in);

    retval := DBMS_LDAP.SEARCH_S(
          ld         =>  my_session,
          base       =>  ldap_baseDN,
          scope      =>  DBMS_LDAP.SCOPE_SUBTREE,
          filter     =>  search_filter,
          attrs      =>  res_attrs,
          attronly   =>  0,
          res        =>  res_message);

    retval := DBMS_LDAP.COUNT_ENTRIES(my_session, res_message);

    IF retval = 0 THEN
      RETURN NULL;
    END IF;

    temp_entry := DBMS_LDAP.FIRST_ENTRY(my_session, res_message);

        temp_attr_name := DBMS_LDAP.FIRST_ATTRIBUTE(
              my_session,
              temp_entry,
              temp_ber_elmt);

        attr_index := 1;
        WHILE temp_attr_name IS NOT NULL LOOP

            temp_vals := DBMS_LDAP.GET_VALUES(my_session, temp_entry, temp_attr_name);
            IF temp_vals.COUNT > 0 THEN
                FOR i IN temp_vals.FIRST..temp_vals.LAST LOOP

                   DBMS_OUTPUT.PUT_LINE('temp_attr_name: ' || temp_attr_name || ', ' || 'temp_attr_val: ' || temp_vals(i));

                   ret_attr_value := temp_vals(i);

                END LOOP;
            END IF;
            temp_attr_name := DBMS_LDAP.NEXT_ATTRIBUTE(   my_session,
                                                          temp_entry,
                                                          temp_ber_elmt);
            attr_index := attr_index + 1;

        END LOOP;

    retval := DBMS_LDAP.UNBIND_S(my_session);

RETURN(ret_attr_value);

   EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error code : ' || TO_CHAR(SQLCODE));
            DBMS_OUTPUT.PUT_LINE('Error Message : ' || SQLERRM);
            --RETURN NULL;

END LDAP_SEARCH;

END;

Open in new window

error
toookiAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
What you are describing is proper behavior.

If you 'trap' an exception, you need to fulfill the 'RETURN' clause of a function.

If you don't trap the exception you will see the correct error and you need to account for it.

Take the text below.

First function is what you have.  You have a 'bad' select, trap the error but do not 'return' a value.

Second:  You have an error but have a return value so everything is 'good'.

The third:  'bad' code and not exception handling and you see the 'true' error.

You need to decide what you want.  Your code has a problem.  You are 'trapping' the real error.  Either let the exception 'raise' itself or ignore it.  It's up to you.

create or replace function myfunc return varchar2
is
	junk number;
begin
	select 1 into junk from dual where 1=2;

	exception when others then
		dbms_output.put_line('Hello');
end;
/

show errors

select myfunc() from dual;


create or replace function myfunc return varchar2
is
	junk number;
begin
	select 1 into junk from dual where 1=2;

	exception when others then
		dbms_output.put_line('Hello');
		return null;
end;
/

show errors

select myfunc() from dual;


create or replace function myfunc return varchar2
is
	junk number;
begin
	select 1 into junk from dual where to_date('Hello')=sysdate;
	dbms_output.put_line('Hello');
end;
/

show errors

select myfunc() from dual;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>goes away but the return value of the function becomes NULL all the time which I don't want.

What do you want to happen when an error occurs?

You cannot handle an exception AND return some value.
0
 
toookiAuthor Commented:
Thank you for help.
sql>select LOC_AGA_UTIL.MOT_LDAP_SEARCH('1starg', 'cn') from dual;
The above way of calling the function works perfectly.

When an error happens (whatever maybe the reason), I want the function to return NULL (not to show the error stack).
If I get rid of the 4-lines related to EXCEPTION, the error does not go away.

I call the function this way from the PL/SQL code:
===========
SELECT
....,
LDAP_SEARCH(u_asignee.id, 'cn')
FROM
...;
===========
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
toookiAuthor Commented:
sorry, the sql I meant to say:
sql>select LDAP_SEARCH('1starg', 'cn') from dual;
0
 
slightwv (䄆 Netminder) Commented:
In the exception handler comment back in the: return null
0
 
toookiAuthor Commented:
OK. In the exception handler I set it return null (was commented out before).
Now OK error is gone when called this way:
SELECT
....,
LDAP_SEARCH(u_asignee.id, 'cn')
FROM
...;
But the above always returns null.

But sql>select LDAP_SEARCH('1starg', 'cn') from dual; works.

0
 
slightwv (䄆 Netminder) Commented:
You are sending mixed messages.

It 'always returns null' then 'it works'.

If you were receiving an exception before, you have some issue somewhere.  You need to figure out what is causing the exception and fix it.
0
 
toookiAuthor Commented:
Sorry about that.
If I add:
RETURN NULL;
(In the  EXCEPTION
        WHEN OTHERS THEN)

The error (Error ORA-06503) goes away. But the the output of the function is always NULL. So it is no good.

If I comment out:
--RETURN NULL; (As in the attached function code too in my first post), I get error (Error ORA-06503) when I call the function from PL/SQL code.
0
 
toookiAuthor Commented:
Thanks a lot. I understand that you mentioned with such nice examples. I will check my function to see what changes I need to make.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.