5

I use the software SQuirreL SQL Client Version 3.2.1 and I want to declare variable like

define dateFrom = '13/04/2012';
define dateTo = '13/04/2012'

And to use it in my sql query like that

SELECT * FROM table_name WHERE TRUNC(column_name) BETWEEN to_date('&dateFrom','YYYY-MM-DD') AND to_date('&dateTo','YYYY-MM-DD');

But it doesn't work. How can I define and use variable in SQuirreL.

Greg Chabala
  • 1,125
  • 2
  • 23
  • 35
BasicCoder
  • 1,661
  • 10
  • 27
  • 42
  • SQuirreL is a client, not the actual RDBMS. What RDBMS are you calling? That is, the variable will be declared in the SQL you send from SQuirreL to the RDBMS – gbn May 09 '12 at 08:46
  • I'm calling a Oracle database. – BasicCoder May 09 '12 at 10:14
  • 1
    What error message are you getting? The format mask reads 'YYYY-MM-DD', whereas your constant values are formatted 'DD/MM/YYYY'. Did you try that format in the query? – Joseph B Feb 18 '14 at 19:24

3 Answers3

1

Maybe not quite what you want, but have you tried loading plugin 'sqlparam'? It offers variable substition, e.g. 

SELECT * FROM table_name WHERE TRUNC(column_name) BETWEEN :dateFrom and :dataTo

When running this query Squirrel will ask for values.

Tom Verweij
  • 21
  • 2
0

I know this question is really old but it helped me find a solution and I thought I'd share it. Using SquirrelSQL 3.8.1 this works when running against an oracle database. 

SELECT * FROM table_name WHERE TRUNC(column_name) BETWEEN to_date( :dateFrom ,'YYYY-MM-DD') AND to_date( :dateTo,'YYYY-MM-DD');

Note: The space between the the function call and the bind variable colon is needed -- without it you will get an error.

Enoch Norton
  • 11
  • 2
-1

"SQuirreL (just like Oracle's SQL-Plus) needs to know when you have finished your anonymous procedure. That is usually done by adding a line at the end of your procedure with a sinlge slash (/) character. For example:"

DECLARE
v1  NUMBER(3);

BEGIN
  v1 := 3;
  select 1 from dual
END;
/

Please look here: http://sourceforge.net/p/squirrel-sql/mailman/message/28580491/

Now when you select your SQL including the slash you will be able to run it with Ctrl+Enter.

josliber
  • 43,891
  • 12
  • 98
  • 133
wholenewstrain
  • 199
  • 1
  • 8
  • The select requires an into portion and is missing the semicolon at the end. But this will not return any value in its current form. – Marcell Feb 05 '20 at 08:30