Simple Oracle variable SQL Assignment

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;

To accomplish what you're attempting in Toad, you don't need to declare the variable at all. Simply include your variable prefaced with a colon and Toad will prompt you for the variable's value when you execute the query. For example:

select * from all_tables where owner = :this_is_a_variable;

If this doesn't work initially, right-click anywhere in the editor and make sure "Prompt for Substitution Variables" is checked.

If you really want to do it similarly to the way SQL Server handles variables (or you want to be able to do the same thing in SQL*Plus), you can write it as follows:

var this_is_a_variable varchar2(30); exec :this_is_a_variable := 'YOUR_SCHEMA_NAME';print this_is_a_variable;select * from all_tables where owner = :this_is_a_variable;

However, to make this work in Toad, you'll need to run it through "Execute as script", rather than the typical "Execute statement" command.

Take in mind that Oracle's PL/SQL is not SQL.

PL/SQL is a procedural language. SQL is not procedural, but you can define "variables" the user can enter via the "&var" syntax (see http://www.orafaq.com/node/515).