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

jcd (jcd) wrote:
: 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)

Hi Jacob,

Cheer up. Your great idea is available in Oracle 7.1. You can write functions and use them as columns in SQL.

--
---------------------------------------------------------------------------
Kannan  (Mastech Corporation)
Email: skannan_at_fs.com

The above are my own comments and opinion. They do not purport that of 
anybody else.
---------------------------------------------------------------------------