I made my first function in Oracle database, but its returned with error (ORA-06503):
ORA-06503: PL/SQL: Function returned without value
function GetNumberOfReceipt(psID varchar2) return number is
nID_Order number;
nID_Bill number;
nID_DeliveryNO number;
nID_WithoutDeliveryNO number;
nID number;
nExists number;
begin
select count(*) into nExists from <mytable> where <mywhereclause> = psID;
--query blocks
--ORDER
select id into nID_Order from <mytables> where <mywhereclause> = psID group by id;
--PL
select id into nID_Bill from <mytables> where <mywhereclause> = psID group by id;
--DeliveryNote
select id into nID_DeliveryNO from <mytables> where <mywhereclause>=psID;
--DeriveryNoteWithout
select id into nID_WithoutDeliveryNO from <mytables> where <mywhereclause> =psID;
--Format multiple values/IDs
select listagg(ID,',') within group(order by ID ) into nID from (
select nID_Order as ID from dual
union all
select nID_Bill as ID from dual
union all
select nID_DeliveryNO as ID from dual
union all
select nID_WithoutDeliveryNO as ID from dual
group by nID_Order,nID_Bill,nID_DeliveryNO,nID_WithoutDeliveryNO);
IF nExists=0 THEN
--raise
<myerrormsg> -- pckg procedure
ELSE
return nID;
END IF;
exception
when no_data_found then
nID := 0;
end GetNumberOfReceipt;
In the query block, the queries are working, because i tested each one.
Should i delete the if condition ? Or what did i miss ?
As Koen pointed out in the comments, the issue is that you have an exception block that doesn't raise an exception or return a value.
However, the flow of your function doesn't really make sense - why are you checking for the existence of an order id, but not doing anything about it until the end?
Also, why collect all the ids into their various variables only to write a dummy query to concatenate them all; why not do all that in a single query?
I think your function would be more efficient if you rewrote it to be something like:
CREATE OR REPLACE FUNCTION getnumberofreceipt(psid VARCHAR2) RETURN NUMBER IS
nid NUMBER;
nexists NUMBER;
BEGIN
SELECT COUNT(*)
INTO nexists
FROM < mytable >
WHERE < mywhereclause > = psid;
IF nexists = 0 -- Moved the IF statement here, to avoid doing unnecessary work when we know the order id doesn't exist
THEN
-- either raise an exception here via RAISE_APPLICATION_ERROR(), or set the nid variable to a dummy value, depending on your requirements
ELSE
-- combined all your queries into one. Depending on the tables and join conditions in each subquery, it may be possible to combine the queries further,
-- but it's difficult to say because you've over-simplified your queries.
WITH orders AS (SELECT ID -- if you're only selecting this one column, you can remove the group by and just use DISTINCT instead
FROM < mytables >
WHERE < mywhereclause > = psid
GROUP BY ID),
pl AS (SELECT id -- if you're only selecting this one column, you can remove the group by and just use DISTINCT instead
FROM < mytables >
WHERE < mywhereclause > = psid
GROUP BY ID),
deliverynote AS (SELECT ID
FROM < mytables >
WHERE < mywhereclause >= psid),
DeriveryNoteWithout AS (SELECT id
INTO nid_withoutdeliveryno
FROM < mytables >
WHERE < mywhereclause > = psid)
--Format multiple values/IDs
SELECT listagg(id, ',') within GROUP(ORDER BY id)
INTO nid
FROM (SELECT id
FROM orders
UNION -- Using UNIONs instead of UNION ALLs because it seems we only want unique IDs
SELECT id
FROM pl
UNION
SELECT id
FROM deliverynote
UNION
SELECT id
FROM deriverynotewithout);
END IF;
RETURN nid; -- Arguably, it's good practice to have a single point of return in the main block
-- i.e. set the variable at the relevant points and then return it at the end.
END getnumberofreceipt;
/
N.B. this will handle the case where you could get multiple ids returned for a given psid, whereas your original function would raise a TOO_MANY_ROWS error. If that's desired behaviour, you're stuck with your current separate queries.