Re: Using variables

 Message from: Amarrete 

Yes, it was a TOAD question, and I think that was the problem. I was just creating the query, and apparently the default is "SQL Style" and that does not work (your query does work there, but not mine), but if I select "T-SQL Style" or even "Text' my query works, it seems I will need to RTFM as I do not know the differences. Thanks!


_______________________________________

Historical Messages 


Author: Hugo
Date: Mon Jan 30 15:18:18 PST 2012
Yes, it was a TOAD question, and I think that was the problem. I was just creating the query, and apparently the default is "SQL Style" and that does not work (your query does work there, but not mine), but if I select "T-SQL Style" or even "Text' my query works, it seems I will need to RTFM as I do not know the differences. Thanks!

__

Author: Richard Squires
Date: Mon Jan 30 13:48:48 PST 2012
I'm a bit confused as to whether this is an SQL, PLSQL or TOAD question, but let's assume it's a TOAD question.  You can run a statement like yours in TOAD as follows:

 	select * from all_tables 
	where trunc(last_analyzed) = :thedate 
	order by last_analyzed;

The pop up that appears allows you to identify :thedate as a DATE and to enter a value.

In TOAD you could have many statements in the same editor window that use :thedate and they would use the same value, you wouldn't therefore need to create a PLSQL programme unit and specify a variable(s).

__

Author: Hugo
Date: Mon Jan 30 13:06:57 PST 2012
I am new to Toad and I am trying to do something very simple, but cannot figure it out, I want to set a value for a/some variable/s and then use them, for example:

begin
    :theDate := '20120101';
    select * from myTable WHERE TRANDATE = to_date(:theDate, 'YYYYMMDD') order by TRANDATE, LOCAL_TIME;
end;

But this has two problems to begin with, first it shows a window for me to set the value of the variable :theDate1, second it just fails for it expects an INTO clause (ORA-06550: line 3, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement), so, how can I set up variables and then use them in subsequently sql statements (my version of TOAD is 11.0.0.116 if that matters.

TIA

__
_______________________________________