?
Solved

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

Posted on 2011-04-22
9
Medium Priority
?
1,387 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:toooki
  • 5
  • 4
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35451168
>>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
 

Author Comment

by:toooki
ID: 35451209
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
 

Author Comment

by:toooki
ID: 35451215
sorry, the sql I meant to say:
sql>select LDAP_SEARCH('1starg', 'cn') from dual;
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35451224
In the exception handler comment back in the: return null
0
 

Author Comment

by:toooki
ID: 35451253
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35451267
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
 

Author Comment

by:toooki
ID: 35451283
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35451316
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
 

Author Comment

by:toooki
ID: 35451332
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

850 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