Re: Using function in SQL - not possible ????

jcd <jcd> wrote:
>Today one of us got a (we think) great idea - we would like to use functions
>in SQL much the same way it is possible to do in access, and excatly the
>same way you would use to_char or to_date.
>
>So we made a little test and created the following function (on a RDBMS
>version 7.0.16.6):
>
>function my_cat (str1 IN VARCHAR, str2 IN VARCHAR)
>RETURNS VARCHAR
>BEGIN
> RETURN(str1||str2);
>END;
>
>which we tried using like this (in SQL*PLus):
>
>SELECT my_cat('a','b') from dual;
>
>and got the error: ORA-00919 - illegal function
>

This is a new feature that was introduced in PL/SQL version 2.1 (which is bundled with RDBMS version 7.1)

There are several rules about what the function can and cannot do. These are described in the PL/SQL 2.1 release addendum. The function must be stored in the database (packaged functions are okay).

They also cannot modify database tables.

This is a very useful feature, as your function can also be used in UPDATE, INSERT and DELETE statements.

Roger Harris
DBA
Kraft Foods
White Plains, NY Received on Fri Aug 18 1995 - 00:00:00 CEST