Simple Oracle variable SQL Assignment

Question

Despite having spent an hour researching I can't seem to figure out how to correctly define a variable and then use it in your SQL.

This is what I have so far produced:

DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');

of which I get the reply:

ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin function package pragma procedure subtype type use form current cursor

Details: DECLARE startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy'); Error at line 1 ORA-06550: line 1, column 63: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin function package pragma procedure subtype type use form current cursor

I'd love to find out how to do such a simple task!

Answer 1

Your variable declaration is correct.

The DECLARE keyword is used to define variables scoped in a PL/SQL block (whose body is delimited by BEGIN and END;). How do you want to use this variable?

The following PL/SQL works fine for me:

DECLARE 
    startDate DATE := to_date('03/11/2011', 'dd/mm/yyyy');
    reccount INTEGER;
BEGIN
    SELECT count(*) INTO reccount 
        FROM my_table tab 
        WHERE tab.somedate < startDate;
    dbms_output.put_line(reccount);
END;

You can also use the DEFINE statement to use simple string substitution variables. They are suitable for a client like SQL/PLUS or TOAD.

DEFINE start_date = "to_date('03/11/2011', 'dd/mm/yyyy')"
SELECT COUNT(*) from my_table tab where tab.some_date < &start_date;