Create Function error.

[EM1107]

I am trying to create a small function in Oracle, but I am getting a compilation error. Here is the function I created.

CREATE OR REPLACE FUNCTION GETACLFORDOCKET
(I_DOCKET_NO IN NVARCHAR2)

RETURN NUMBER IS I_PRTCP NUMBER(10,0),
NVARCHAR2 IS I_PRTCPNAME NVARCHAR2(100);
BEGIN
SELECT B.PRTCP, PRTCPNAME
INTO I_PRTCP, I_PRTCPNAME
FROM PROCS A, WITEM B
WHERE A.PROCID = B.PROCID
AND A.PROCID IN(SELECT PROCID FROM RLVNTDATA
WHERE RLVNTDATANAME = 'DOCKET'
AND VALUE = I_DOCKET_NO)
ORDER BY B.PRTCPNAME;
RETURN (I_PRTCP, IPRTCPNAME);
END;

As you can see the function need to return two values.
one is a number (I_PRTCP) and the other is NVARCHAR2 (I_PRTCPNAME)

If I iliminate the second value the script get created without any problem, but when I add the second value it fail.

What did I do wrong!

 

[BJCooperIT]

Functions, by nature, only can return one value. If you are not using the function in SQL, you can use OUT parameters to return additional values:

CREATE OR REPLACE FUNCTION GETACLFORDOCKET 
(  I_DOCKET_NO IN  NVARCHAR2,
   I_NAME      OUT NVARCHAR2
)
...
...
RETURN (I_PRTCP);

PS - Why do you require an Order By clause since the code assumes it will only return one row?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[EM1107]

The function as to return more than 1 row and the content of there rows will have to be processed through an other function that I also need to create later.

Does returning more than 1 row cause Problem?
would you suggest using a procedure instead?

 

[Turkbear]

Hi,
A procedure the returns a REFCURSOR may be needed for multiple row returns..
( REFCURSOR is sorta' Oracle's term for a recordset)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BJCooperIT]

Yes, it will cause a problem. Unless you process SQL statements that return multiple rows inside a cursor, you will encounter a TOO_MANY_ROWS exception.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus