Constants in Oracle SQL query

Question

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.

Answer 1

DECLARE 
DateIndex CONSTANT NUMBER(10,0) := 24;
TargetVariable NUMBER;
BEGIN
      SELECT DateIndex
      INTO TargetVariable
      FROM DUAL;
END;

Answer 2

The error is not to do with your constant, the error is because you are using a SELECT statement without an INTO. A SELECT in an anonymous block is not the same as if you were to run a SELECT statement from SQL*Plus for example. It doesn't make sense to select something and do nothing with it, which is why it is prompting you for an into:

SELECT colA, colB
INTO variable_a, variable_b
WHERE something = DateIndex;

This of course assumes your query will only return one row. I have a feeling what you are really after is writing a function that contains your logic and returns a nested table type that you could select from.

EDIT: nevermind, I see that are not able to create type

Answer 3

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
)

Answer 4

When you want to return a result set you need a ref cursor.

create or replace procedure getlogs(p_sys_refcursor out sys_refcursor)
is
begin
  open p_sys_refcursor for
    select *
    from   log
    where  monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');
end;
/

The value of sysdate will be determined only once at the start of the query, so there is no need for declaring some kind of constant inside sql or pl/sql.

Edit1

When you don't want to call a stored proc, do:

select *
from   log
where  monthindex = 12 * to_char(sysdate,'yyyy') + to_char(sysdate,'mm');