Oracle Functions that Take No Parameters

Question

Why is it that CURRENT_DATE, CURRENT_TIMESTAMP, SYSDATE, and SYSTIMESTAMP are called without parentheses. I understand that they take no parameters, but in other languages, you would still call the functions using parentheses. Is it the case in Oracle that any function that never takes parameters cannot be called with parentheses?

If someone could point me to the documentation on this, I'd appreciate it.

Answer 1

Oracle is weird in many ways. It plays fast and loose with lots of things: the meaning of NULL, implicit data type conversions, and a whole number of other things.

Among them, as you noticed, is their inconsistent syntax for calling functions with no parameters. (For declaring such functions, too - see below.)

Native functions like sysdate and current_timestamp, which do not take arguments, must be written without parentheses. You ask for documentation... the most direct (and yet not entirely satisfactory) pointer is to the documentation of each function, where the syntax is shown very clearly without parentheses. What are you looking for - a separate mention in the documentation, where they state this explicitly?

Compare this with analytic functions like rownumber(), for example, which also do not take arguments. You must write them with empty parentheses!

Worse: For functions you write yourself in PL/SQL, and call from SQL statements: if the function takes no arguments, then it must be defined without parentheses. However, when you invoke it (in a select statement, for example) you can call it with or without (empty) parentheses - both syntaxes are valid. Not so, alas, with the native functions, like sysdate. Why? Again, a good question to ask Oracle.

It doesn't end there, either. connect_by_root is a "hierarchical function" (a function that can be used in hierarchical queries). It takes an argument - which can be given in parentheses (as in any normal function) or without parentheses! Go figure.

If you ask WHY??? - you are not alone. I have no clue either.

Answer 2

Those are all Pseudocolumns which are used in SQL or PL/SQL statements like columns but they aren't really stored on the disk. They can be thought of as special-purpose data elements within the SQL statements just as if they were part of the table.

A DML statement neither be applied on Pseudocolumns, nor user-defined they are, so they do not need to be considered as standard functions or procedures, and the style is conventionally defined by Oracle, itself.

By the way, this syntax(without parentheses) is valid even for a function in Oracle provided that no needs to have any parameter as seems more logical rather than what other systems do.

Oracle says : Parameter declarations are optional. Functions that take no parameters are written without parentheses

Answer 3

To further confuse things, some SQL functions have corresponding PL/SQL functions defined in the "STANDARD" PL/SQL package. This allows functions like SYSDATE to be invoked on the right hand side of an assignment statement in a PL/SQL program block. Because the function in the "STANDARD" package is a PL/SQL function (and not an SQL one), it can be invoked with or without parentheses.

DECLARE
  x DATE;
BEGIN
  x:= SYSDATE();  -- Valid
  x:= SYSDATE;    -- Also valid
END;