Simple Oracle SQL variable assignment

Question

Despite having spent an hour of research, I cannot 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');

I get the answer:

ORA-06550: row 1, column 63: PLS-00103: the "end-of-file" character is encountered, expecting one of the following:

start function package pragma procedure subtype type use form current Cursor

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

start function package pragma procedure subtype type use form current Cursor

I would like to know how to make such a simple task!

Answer 1

Your variable declaration is correct.

The DECLARE keyword is used to define variables copied in a PL / SQL block (whose body is limited to 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 such as 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; 

Answer 2

To accomplish what you are trying to use in a toad, you do not need to declare a variable at all. Just include the colon-preceded variable, and Toad will offer you the value of the variable when you run the query. For instance:

 select * from all_tables where owner = :this_is_a_variable; 

If this does not work initially, right-click anywhere in the editor and make sure the "Request Substitution Variables" checkbox is checked.

If you really want to do this in the same way that SQL Server handles variables (or you want to be able to do the same in SQL * Plus), you can write it like this:

 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 need to run it through the "Execute as script", and not in the typical "Execute statement" command.

Answer 3

Keep in mind that Oracle PL / SQL is not SQL.

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

Answer 4

This is an old post, but in case someone stumbles upon this (as I did), you can handle this with the CTE:

 with params as ( select date '2011-11-03' as startdate from dual ) select . . . from params cross join . . . 

Almost the same syntax works in SQL Server (minus date special stuff and from dual ).

Answer 5

Can you try the following:

 DEF startDate = to_date('03/11/2011', 'dd/mm/yyyy'); Select &startDate from dual;