CODEWITHSUNDEEP
sqlfunctionoracle10g
Jacob
Jacob

Reputation: 14731

function return without any value

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;

Upvotes: 0

Views: 96

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30765

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;

Upvotes: 1

A.B.Cade
A.B.Cade

Reputation: 16905

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

Upvotes: 1

Related Questions