Syntax error in SQL*Plus

Question

I'm trying to run the following query in SQL*Plus but I'm getting an error. However, the same exact query works fine with Aqua Data Studio.

SQL> CREATE OR REPLACE VIEW VIEW_WARREPORT
  2  AS
  3  WITH WGS (WORKGROUPID, Root) AS (
  4     SELECT WorkgroupID, CONNECT_BY_ROOT WORKGROUPID "Root"
  5        FROM TPM_WORKGROUPS
  6        START WITH PARENTWORKGROUPID=0
  7        CONNECT BY PRIOR WORKGROUPID = PARENTWORKGROUPID)
  8
SQL>     SELECT
  2            WG.NAME as Workgroup,
  3            WG.WORKGROUPID,
  4            TP.AREAID,
  5            WGS.Root as RootWorkgroup,
  6            DM.NAME as DeliveryMethod,
  7            D.TASKID,
  8            --D.ISMARKERCOMPLETION,
  9            T.NAME as TaskName,
 10            T.DESCRIPTION as TaskDescription,
 11            T.SHORTNAME,
 12            COALESCE(T.COURSECODE, 'N/A') as CourseCode,
 13            (U.FIRSTNAME || ' ' || U.LASTNAME) as HQOwner,
 14            ((T.DELIVERABLELENGTHHOUR * 60) + T.DELIVERABLELENGTHMINUTE) as TaskMinutes,
 15            TP.STARTDATE, TP.ENDDATE
 16          FROM TPM_TRAININGPLAN TP
 17          INNER JOIN TPM_WORKGROUPS WG ON TP.WORKGROUPID = WG.WORKGROUPID
 18          INNER JOIN TPM_DELIVERYMETHODS DM ON TP.METHODID = DM.METHODID
 19          INNER JOIN TPM_TRAININGPLANDELIVERABLES D ON TP.TRAININGPLANID = D.TRAININGPLANID
 20          INNER JOIN TPM_TASK T ON D.TASKID = T.TASKID
 21          INNER JOIN TPM_PROJECTVERSION V ON (T.PROJECTID = V.PROJECTID AND T.VERSIONID = V.VERSIONID AND V.STAGEID !
= 11 AND V.STAGEID != 12 AND V.STAGEID != 13 AND V.STAGEID != 15)
 22          INNER JOIN TPM_USER U ON V.BUSINESSSPONSOR = U.USERID
 23          INNER JOIN WGS ON WGS.WORKGROUPID = TP.WORKGROUPID
 24          WHERE TP.SCHEDULED=1 AND TP.TRAININGPLANTYPE='actual'
 25          ORDER BY STARTDATE;
        INNER JOIN WGS ON WGS.WORKGROUPID = TP.WORKGROUPID
                   *
ERROR at line 23:
ORA-00942: table or view does not exist

From what I can tell, the error is that WGS does not exist. But as you can see, I create this on line 3.

I'm running on not enough sleep and not enough caffeine, so the chances of me doing something stupid far outweigh the chances there's some weird SQL*Plus parsing bug, but I'm still curious as to why this works in Aqua. Oh also if I just run the query and not the view creation, I get the same error.

Thanks!

UPDATE:

Okay I think I see the problem. After the WITH clause, it starts a new query. So I guess my question is can this be prevented in SQL*Plus? I've tried doing a few things like putting a semicolon after WORKGROUPID = PARENTWORKGROUPID and that didn't help.

There is a line break causing SQL*Plus to start the new statement. Remove the line break after line 7 and it should be OK. — Ollie
– Ollie, Commented Jan 9, 2012 at 23:10
Awesome! Yea I guess SQL*Plus is really picky about that stuff. I thought you ended statements with a /. Feel free to add this as an answer and I'll accept and +1 it. — Mike Christensen
– Mike Christensen, Commented Jan 9, 2012 at 23:14
Yes it tends to be, line breaks are significant in SQL Plus. Done. — Ollie
– Ollie, Commented Jan 9, 2012 at 23:16

Ollie · Accepted Answer · 2012-01-09 23:17:00Z

6

There is a line break causing SQL*Plus to start the new statement. Remove the line break after line 7 and it should be OK

answered Jan 9, 2012 at 23:17

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Christensen Over a year ago

Yup, I just got started on Oracle recently and have been using Aqua to talk with it mainly. I'm still getting used to the finer points of SQL*Plus :)

René Nyffenegger · Accepted Answer · 2012-01-10 16:40:25Z

2

You need to tell SQL*Plus that it should allow empty lines with a

SQL> set sqlblanklines on

and everything will work fine.

answered Jan 10, 2012 at 16:40

René Nyffenegger

40.9k34 gold badges180 silver badges321 bronze badges

Collectives™ on Stack Overflow

Syntax error in SQL*Plus

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related