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.