Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

Question

I have following example query.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);
begin
 select '01-01-2000', '30-11-2011'
 into :pStartDateBegin,:pEndDateFinish
 from dual;
end;
-- SELECT :pStartDateBegin,:pEndDateFinish FROM dual;
WITH EXAMPLE
AS
(
 SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
 WHERE T.LAST_ANALYZED BETWEEN :pStartDateBegin AND :pEndDateFinish
)
SELECT * FROM EXAMPLE;

When I run this query using TOAD. It runs successfully.

XXXX rows selected.
Time End: 06.12.2011 12:05:37
Elapsed Time for Script Execution: 3 secs

In SQL Developer, I get following Error.

Error report:
ORA-06550: line 12, column 2:
PLS-00103: Encountered the symbol "WITH" 
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

In sqlplus, I have similar error.

ATILLA@DENEME > @WithAndBindVariablesExample.sql
WITH EXAMPLE
*
ERROR at line 12:
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "WITH"

If I use following notation

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);
exec :pStartDateBegin := '01-01-2000';
exec :pEndDateFinish := '30-11-2011';
WITH EXAMPLE
AS
(
 SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
 WHERE T.LAST_ANALYZED BETWEEN to_date(:pStartDateBegin,'DD-MM-YYYY') AND to_date(:pEndDateFinish,'DD-MM-YYYY') 
)
SELECT * FROM EXAMPLE;

It runs successfully all three tools.

It seems that I lack a basic knowledge about this. I would like pointers to tutorials about this behavior, explanations etc.

Answer 1

In SQL*Plus and SQL Developer you need a / at the end of a PL/SQL block.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);
begin
 select '01-01-2000', '30-11-2011'
 into :pStartDateBegin,:pEndDateFinish
 from dual;
end;
/
[...]