PLSQL Code Function not returning Value

Question

I am using function as

create or replace FUNCTION FSS_REF_GETREFIDBYCODEANDORDER (v_Code In CHAR, v_OrderBy In number )
RETURN number IS v_RefID NUMBER(10);

BEGIN

 SELECT REFID
 INTO v_RefID
 FROM REFERENCE
 WHERE Code like (v_Code || '%')
 AND
 ORDERBY = v_OrderBy
 and ROWNUM = 1;

 RETURN (v_RefID); 
 END;

It should return a Value because if I run it separately without function given below its giving the result as 1 desired 1 value

 SELECT REFID
 -- INTO v_RefID
 FROM REFERENCE
 WHERE Code like ('001' || '%')
 AND
 ORDERBY = 4
 and ROWNUM = 1;

Can anybody please help me out with any error in function. As I am new in PL/SQL programming.

"It should return a Value" What does it do instead? Do you get an error message? — Matthew Strawbridge
– Matthew Strawbridge, Commented Feb 29, 2016 at 7:46
I assume that the problem has to do with your use of the char datatype instead of using varchar2. Yet, without any hint on how you call function nor if you get an error message, this is impossible to tell. — René Nyffenegger
– René Nyffenegger, Commented Feb 29, 2016 at 9:05
@PeterLang -- I am simply creating this function in Sql developer and Compiling it.. It compile successfully but when I play/run the function it is returning me below output ORA-01403: no data found ORA-06512: at "PSBD.FSS_REF_GETREFIDBYCODEANDORDER", line 7 — sasha
– sasha, Commented Mar 1, 2016 at 8:33
@MatthewStrawbridge I know that my concern it should return a value. But its giving me no value with output. ORA-01403: no data found ORA-06512: at "PSBD.FSS_REF_GETREFIDBYCODEANDORDER", line 7 — sasha
– sasha, Commented Mar 1, 2016 at 8:35

Andris Krauze · Accepted Answer · 2016-03-02 07:30:10Z

1

Try changing "v_Code In CHAR" to "v_Code In VARCHAR2". I would also recommend that you add NO_DATA_FOUND exception handler. E.g.

CREATE OR REPLACE FUNCTION fss_ref_getrefidbycodeandorder(v_code IN VARCHAR2, v_orderby IN NUMBER) RETURN NUMBER IS
  v_refid NUMBER(10);

BEGIN

  SELECT refid
    INTO v_refid
    FROM reference
   WHERE code LIKE (v_code || '%')
     AND orderby = v_orderby
     AND rownum = 1;

  RETURN(v_refid);
EXCEPTION
  WHEN no_data_found THEN
    RETURN NULL;
END;

answered Mar 2, 2016 at 7:30

Andris Krauze

2,1428 gold badges31 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLSQL Code Function not returning Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related