Why pi() function does not work in Oracle using it

问题:

Query 1 works fine with the Oracle JDBC driver but Query 2 doesn't. I don't understand why. Please, could someone help me?

Query 1:

SELECT {fn pi()} FROM table;

Query 2:

SELECT pi() FROM table;

回答1:

There is no native pi() function in Oracle. The JDBC driver will simply replace that with a hardcoded value.

If you run

SELECT {fn pi()} FROM dual;

The driver will send:

SELECT ( 3.141592653589793238462643383279502884197169399375 ) 
FROM
 dual

to the server - that's why you can use the {fn pi()} against an Oracle database even though there is no such function available.

回答2:

There doesn't seem to be any native function called PI. The error suggests that you haven't created a user-defined function either.

The {fn foo()} syntax is provided by JDBC, thus won't work without it:

JDBC escape syntax for fn keyword

You can specify functions in JDBC escape syntax by using the fn keyword. Syntax

{fn functionCall}

where functionCall is the name of one of the scalar functions listed below.

... and following the previous link we can see that the underlying PI() function is provided by Java DB and/or Apache Derby.