Alex Rivera | Logout

Simple Oracle variable SQL Assignment

Asked 2011-11-07T17:06:43.170
10

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!

oracle-database variables plsql toad
Edit
Report

1 Answer

7

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.

answered 2011-11-07T18:40:40.890

Your Answer