Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

[...]
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.