Using function in SQL - not possible ????

Hi everyone !

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

Well then not totally defeated we tried PL/SQL:

DECLARE
res VARCHAR(199);
BEGIN
res := my_cat('a','b');
END; Which was OK but then we tried:

DECLARE
res VARCHAR(199);
BEGIN
select my_cat('a','b') into res from dual END; Which gave the error PLS-00231: Function MY_CAT may not be used in SQL

So what gives ?? Is it just not possible to use self-defined functions in SQL ? Why not - as far as I can see to_date and to_char are created as functions.

Since we tried using functions like this in ACCESS we really hoped something like the above would be possible.

/Jacob (totally depressed by the fact that a $ 100.000 database is outgunned

        by a Mcrosoft Office product) Received on Fri Aug 18 1995 - 00:00:00 CEST