| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Function and Datatype Question
On 3 Feb 1997 16:27:51 GMT, "Tim Romano" <tim_at_ot.com> wrote:
>I'm trying to create a function and use it in a SELECT statement. However,
>the function returns a Boolean and it would seem that the function must
>return a value of the database of the underlying column, which doesn't make
>sense to me. Here's an example:
>
>CREATE FUNCTION ISNULL(MYVAL IN VARCHAR2) RETURN BOOLEAN IS
>bResult BOOLEAN;
>BEGIN
> IF MYVAL IS NULL THEN
> bResult := TRUE;
> ELSE
> bResult := FALSE;
> END IF;
>RETURN (bResult);
>END;
>
>Now, if I do this (PO7 for Windows):
>
>SELECT ID, ISNULL(DESC) FROM MYTABLE
>
>I get an error that the expression is the wrong datatype. If I change the
>BOOLEAN to VARCHAR2 and set bResult to 'Y' or 'N' accordingly, the function
>works.
>
From the server application developers guide:
Basic Requirements
To be callable from SQL expressions, a user–defined PL/SQL function must meet the following basic requirements:
It must be a stored function, not a function defined within a PL/SQL block or subprogram.
It must be a row function, not a column (group) function; that is, it cannot take an entire column of data as its argument.
All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.
The datatypes of its formal parameters must be Oracle Server internal types such as CHAR, DATE, or NUMBER, not PL/SQL types such as BOOLEAN, RECORD, or TABLE.
Its return type (the datatype of its result value) must be an Oracle Server internal type.
Basically, a SQL Select statement can only return a CHAR, DATE, NUMBER. Boolean is a pl/sql extension and not understand in a pure SQL context. Even if you could return a BOOLEAN and call it from SQL, SQL*Plus in this case would have no idea what a boolean is, how to display it, or how to bind to it....
>Is there something obvious that I'm doing wrong?
>Thanks,
>Tim
>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |