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!

Variables are a client construct. You need to tell us what client you are using. — APC, Nov 07 '11 at 17:39

score 17 · Answer 1 · edited Apr 22 '15 at 03:57

17

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;

edited Apr 22 '15 at 03:57

Jon Heller

34,999
6
74
132

answered Nov 07 '11 at 17:10

Xavi López

27,550
11
97
161

4

So is there no way to do it succinctly like in MS SQL where it's a simple `declare @varible set @variable`? I'd like to later use the variable in a `WHERE` clause, how would I do that? How would I also print the variable to screen? – m.edmondson Nov 07 '11 at 17:14
I'm not familiar with MS SQL, but if you're referring to some kind of global variable, it's possible to define a variable in a package, thus making it visible to all the package's members, even though global variables are something you'll probably want to avoid. You can print the variable to screen using [`dbms_output.put_line`](http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#i1000105). – Xavi López Nov 07 '11 at 17:22
Not a global variable - just a normal variable that I can assign and then use later on – m.edmondson Nov 07 '11 at 18:21
I've been about to post this before but wasn't sure this is what you wanted. You can also use the `DEFINE` statement to interpolate simple string substitution variables. See my updated on the answer for an example. – Xavi López Nov 07 '11 at 18:57

Allan · Answer 2 · 2011-11-07T18:50:13.773

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.

score 4 · Answer 3 · answered Oct 16 '15 at 20:40

This is an old post, but in case anyone stumbles on this (as I just did), you can handle this with a 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 the date-specific stuff and from dual).

score 4 · Answer 4 · answered Nov 07 '11 at 18:29

4

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).

answered Nov 07 '11 at 18:29

friol

6,996
4
44
81

Slim Aloui · Answer 5 · 2020-10-20T09:51:54.290

0

Solution

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

edited Oct 20 '20 at 09:51

answered Apr 21 '15 at 15:16

Slim Aloui

430
8
14

Simple Oracle variable SQL Assignment

5 Answers5

Solution

Linked