1

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.

3

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

[...]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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