Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

oracleoracle-sql-developersqlplustools

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.

Best Answer

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;
/

[...]