function return without any value

Question

I have a sql query in a function

SELECT  DISTINCT (product_id)
      INTO  prod
      FROM  products
     WHERE       mfg_no = 'TEL'             
                AND status = p_status

Sometimes product_id will be null or p_status will have no data. Because of this function doesn't return any value.

I tried nvl,decode and case statement to check product_id, but none didn't work. How can I make sure that my function executes fine even if product_id is null or empty?

I have tried to return a value in EXCEPTION block like the following, that didn't help either.

EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        prod := 'NA';

Edit 1

CREATE OR REPLACE FUNCTION my_function (p_status VARCHAR2)
    RETURN VARCHAR2
AS
    prod     VARCHAR2 (2000);
BEGIN
    prod := NULL;  


    SELECT  DISTINCT (product_id)
      INTO  prod
      FROM  products
     WHERE  mfg_no = 'TEL' AND status = p_status;

    RETURN prod;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        prod := 'N/A';
        DBMS_OUTPUT.put_line ('no data found ' || SQLERRM);
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('error ' || SQLERRM);
        prod := 'N/A';
END;

Can you show the full code ? where is the return prod; line ? — A.B.Cade
– A.B.Cade, Commented Dec 3, 2012 at 13:24
@A.B.Cade I have added my function in my question as Edit 1. Thanks — Jacob
– Jacob, Commented Dec 3, 2012 at 13:27

A.B.Cade · Accepted Answer · 2012-12-03 13:40:32Z

1

Try it like this:

CREATE OR REPLACE FUNCTION my_function (p_status VARCHAR2)
    RETURN VARCHAR2
AS
    prod     VARCHAR2 (2000);
BEGIN
    prod := NULL;

  BEGIN
    SELECT  DISTINCT (product_id)
      INTO  prod
      FROM  products
     WHERE  mfg_no = 'TEL' AND status = p_status;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        prod := 'N/A';
        DBMS_OUTPUT.put_line ('no data found ' || SQLERRM);
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('error ' || SQLERRM);
        prod := 'N/A';
  END;

  RETURN prod;

END;

To see the difference between no records and null value see this sqlfiddle demo

edited Dec 3, 2012 at 13:40

answered Dec 3, 2012 at 13:29

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

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

3 Comments

Jacob Over a year ago

Thanks a lot, this really helped. I have a question, what is the best way to handle product_id if it is null? is it better to catch in exception block?

A.B.Cade Over a year ago

There is a difference between null and no_data_found you can use nvl when there is a record with null value and you catch an exception when there is no records. See the Demo I added

Jacob Over a year ago

Great +one. Thanks again, appreciated.

Frank Schmitt · Accepted Answer · 2012-12-03 13:50:13Z

1

Another solution, using NVL / MAX instead of DISTINCT / Exception handling:

CREATE OR REPLACE FUNCTION my_function (p_status VARCHAR2)
    RETURN VARCHAR2
AS
    prod     VARCHAR2 (2000);
BEGIN
    SELECT  NVL(MAX(product_id), 'N/A')
      INTO  prod
      FROM  products
     WHERE  mfg_no = 'TEL' AND status = p_status;
  RETURN prod;

END;

answered Dec 3, 2012 at 13:50

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Collectives™ on Stack Overflow

function return without any value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related