Function help (probably an ID10T error) 2

[commatom]

OK, Ive been staring at this one for an hour and can't see where my screw up is.

CREATE OR REPLACE FUNCTION DW.f_e164_dj (v_call varchar )
 RETURN  NUMBER IS
v_COUNT   NUMBER;
v_1 varchar2(1);
v_2 varchar2(2);
v_3 varchar2(3);
v_4 varchar2(4);
v_e164code varchar2(10);
v_short_code varchar2(30);


BEGIN
 
v_1 := substr(v_call,1,1);
v_2 := substr(v_call,1,2);
v_3 := substr(v_call,1,3);
v_4 := substr(v_call,1,4);

          
         SELECT  e164cd INTO v_e164code
          FROM   dw.lu_e164
          WHERE  e164cd IN (v_1, v_2, v_3, v_4);
     exception
          when others THEN v_e164code := null;

 
IF v_e164code is null THEN
v_e164code := 0;
END IF;


         RETURN v_e164code;



END;

When I use this function to update a table I am getting the following error

ORA-06503: PL/SQL: Function returned without value

Any suggestions?

Thanks again guys!

 

[jaxtell]

Try this...

CREATE OR REPLACE FUNCTION DW.f_e164_dj (v_call varchar )
 RETURN  NUMBER IS
v_COUNT   NUMBER;
v_1 varchar2(1);
v_2 varchar2(2);
v_3 varchar2(3);
v_4 varchar2(4);
v_e164code varchar2(10);
v_short_code varchar2(30);


BEGIN
 
v_1 := substr(v_call,1,1);
v_2 := substr(v_call,1,2);
v_3 := substr(v_call,1,3);
v_4 := substr(v_call,1,4);

          begin
         SELECT  e164cd INTO v_e164code
          FROM   dw.lu_e164
          WHERE  e164cd IN (v_1, v_2, v_3, v_4);
     exception
          when others THEN v_e164code := null;
end;
 
IF v_e164code is null THEN
v_e164code := 0;
END IF;


         RETURN v_e164code;



END;

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[commatom]

You are the man! It worked.

BUT WHY? PLEASE EXPLAIN!!!

 

[SantaMufasa]

And this should simplify things even further:

CREATE OR REPLACE FUNCTION DW.f_e164_dj (v_call varchar )
 RETURN  NUMBER IS
    v_COUNT   NUMBER;
    v_1 varchar2(1);
    v_2 varchar2(2);
    v_3 varchar2(3);
    v_4 varchar2(4);
    v_e164code varchar2(10);
    v_short_code varchar2(30);
BEGIN
    v_1 := substr(v_call,1,1);
    v_2 := substr(v_call,1,2);
    v_3 := substr(v_call,1,3);
    v_4 := substr(v_call,1,4);
    begin
        SELECT  e164cd INTO v_e164code
          FROM   dw.lu_e164
         WHERE  e164cd IN (v_1, v_2, v_3, v_4);
    exception
         when others THEN v_e164code := null;
    end;
    RETURN nvl(v_e164code,0);
END;

Let us know if this resolves your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[commatom]

Yes Dave but why!?!?!?

I dont get it.

Long time no see Dave, I was just thinking about you and your story about setting the garage on fire when you were a kid.

Good times

 

[SantaMufasa]

It worked. BUT WHY? PLEASE EXPLAIN!!!

Notice that Jaxtell's excellent suggestion created a "sub-block" (using nested "Begin...End") within your outer block. Therefore, if/when your SELECT throws any type of error, the sub-block sets "v_e164code := null;", then leaves that sub-block of code.


With the way that you had your original code, the only time Oracle executed your "IF" statement and the "RETURN" statement is if your SELECT threw an EXCEPTION, thus "return without value" error.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[commatom]

Yes, thank you. That is exactly the explanation I was looking for.