How to return two types in pl/sql oracle?

return number -- here I tell than function reurns number, but i want to return number and string when an exception is thrown
is
prPrice number; -- product price
curFrom number; -- price
pgcount number := 0; -- product count
noProductsOnDate EXCEPTION;
wrongCurrency EXCEPTION;
begin
SELECT COUNT(*) INTO pgcount
from Products pr, Outgoing outg, Incoming inc 
where pr.PROD_ID = outg.PROD_ID and pr.PROD_ID = inc.PROD_ID  and inc.inc_date > d
Having sum(inc.quantity) > sum(outg.quantity);
if pgcount = 0 or pgcount is null then
    raise noProductsOnDate;
END IF;
if curTo > 2 OR curTo < 1 then
    raise wrongCurrency;
end if;
    If curTo = 1 
        then curFrom := 2;
    Elsif curTo = 2 
        then curFrom := 1;
    END IF;
    select pric.Value*cour.value into prPrice from Prices pric, Cources cour 
    where p = pric.prod_id and pric.DAYFROM <= d and (pric.DAYTO >= d or pric.DAYTO is null) and cour.cur_idto = curTo and cour.cur_idfrom = curFrom;
    return prPrice;  -- here i wanna return number 
exception
    when noProductsOnDate then return '1q'; -- here i wanna return string (error message) 
    when wrongCurrency then return '2q'; -- here i wanna return string (error message) 
end;

I can't return string in exception, because function return number Maybe I doing something wrong,

Please, tell me how can I return several data types from one function, maybe I should do everything differently, but I don't understand how to do it ((

CodePudding user response：

You can't do it that way, but can another way around - declare function to return varchar2. Then, in your code, you can

create function ... 
  return varchar2
is
begin
  ... do whatever you're doing
  return to_char(poPrice);

exception
  when ... then return '1q';
end;

CodePudding user response：

I write my functions often in this form:

• return 0 in case of success
• return a positive number in case of a warning (often not used)
• return a negative number in case of an error

And I use out parameters for the function results, in your case the price and/or a message.

Something along the lines of:

CREATE OR REPLACE FUNCTION my_function(vi_date  IN  DATE,
                                       vo_price OUT NUMBER,
                                       vo_msg   OUT VARCHAR) RETURN INTEGER IS
BEGIN
  ...
  IF pgcount = 0 OR pgcount IS NULL THEN
    vo_msg := 'No products on that date.';
    RETURN -20001; -- return custom error code
  END IF;
  ...
  IF vo_price < 0 THEN
    vo_msg := 'Price is negative. Calculation my be wrong.';
    RETURN 1; -- return warning flag (positive number)
  ELSE
    RETURN 0; -- means success
  END IF;
EXCEPTION WHEN OTHERS THEN
  -- Error. Return negative number. SQLCODE is always the negative ORA code
  -- except for ORA-1403 which is strangely SQLCODE 100 instead
  vo_msg := SQLERRM;
  RETURN CASE WHEN SQLCODE = 100 THEN -1403 ELSE SQLCODE END;
END my_function;

Then call it like this:

DECLARE
  v_code  INTEGER;
  v_price NUMBER (10,2);
  v_msg   VARCHAR(1000);
BEGIN
  v_code := my_function(DATE '2022-09-29', v_price, v_msg);

  IF v_code >= 0 THEN
    DBMS_OUTPUT.PUT_LINE('The price is: ' || v_price);
  END IF;

  IF v_code <> 0 THEN
    DBMS_OUTPUT.PUT_LINE('Message: ' || v_msg);
  END IF;
END;