SQL vs PL/SQL: FROM clause incompatible?

From: P. Smith <pam.smith_at_jhuapl.edu>
Date: 1995/07/18
Message-ID: <3ugveg$rr5_at_aplinfo.jhuapl.edu>#1/1

At the bottom is some SQL or PL/SQL that I'm attempting work through. I have a select statement whose "FROM" clause is an embedded query. It works fine using SQL*PLUS but does not compile when I attempt to create a PL/SQL cursor with the same select statement. Any ideas?

Thanks.

---

DROP TABLE mytable;
CREATE TABLE mytable (
  var1 INTEGER,
  var2 INTEGER,
  var3 INTEGER);

INSERT INTO mytable values (1,2,3);
INSERT INTO mytable values (1,2,4);
INSERT INTO mytable values (1,2,5);
INSERT INTO mytable values (1,2,6);
INSERT INTO mytable values (2,2,3);
INSERT INTO mytable values (2,2,4);
INSERT INTO mytable values (2,2,5);
INSERT INTO mytable values (2,2,10);



-- the following select statement is accepted by SQL*PLUS


	SELECT var2 FROM 
	   (SELECT var2, var3 FROM mytable WHERE var1 = 1
	    UNION
	    SELECT var2, var3 FROM mytable WHERE var1 = 2);

• the following PL/SQL does not compile (**identical select statement**)) CREATE OR REPLACE PROCEDURE myproc IS

  CURSOR c1 IS

	SELECT var2 FROM 
	   (SELECT var2, var3 FROM mytable WHERE var1 = 1
	    UNION
	    SELECT var2, var3 FROM mytable WHERE var1 = 2);

BEGIN
  Open c1;

END myproc;
/

I get the following:

QL> sho err
Errors for PROCEDURE MYPROC:

LINE/COL ERROR

---

4/5      PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         <an identifier> <a double-quoted string>
                                                      

Received on Tue Jul 18 1995 - 00:00:00 CEST