Constants in Oracle SQL query

constants, database, oracle, sql

Solution

Your code is not Oracle SQL but PL/SQL. In PL/SQL the result of a query has to be assigned to a variable. So you either have have to use a "select into clause" if you expect exactly one result, or you use a cursor.

In SQL on the other hand you can't declare a constant. You can sometimes work around this limitation by using an inline view like so

select something-complex-here, x.pi 
from sometable, (
    select 3.1415 as pi, 1234 other_constant 
    from dual
)

Problem

I am new to Oracle (though familiar with SQL) and have to write a fairly complex query where a value derived from the current date is used many times. Rather than calculate the value each time, it would seem obvious to declare a constant for the purpose. However, when I then try to use my DateIndex constant in the subsequent SELECT statement (which I wish to return values based on "DateIndex"), the parser tells me that it is exepcting SELECT INTO. What I have (simplified to the lowest form) is... ``` DECLARE DateIndex CONSTANT NUMBER(10,0) := 24; BEGIN SELECT DateIndex FROM DUAL; END; ``` Is it only possible to use constants when selecting into a table rather than returning results? Seems very odd. Note that I do not have write permissions on the database. Many thanks for any assistance.

Original source