SELECT works in SQL, but fails in PL/SQL - 7.3 specific

From: Ben Ryan <ben_ryan_at_vancity.com>
Date: 1997/03/25
Message-ID: <5h9dgc$ek5@sisyphus.tvinet.com>#1/1

A SELECT statement which works fine in SQL is failing in PL/SQL. The SELECT statement uses sub-queries in the FROM clause (which means it is 7.3 specific?).

A minimal SQL script, showing the problem, appears below. It was run on RDBMS 7.3.2.1.0 on SCO OS5.0.2. The stored procedure gives a runtime error of ORA-01410: Invalid ROWID.

Assuming the bug is in the RDBMS 7.3.2, can anyone suggest a work-around SELECT statement. My best shot so far is to break it into two SELECT statements and join the results using PL/SQL logic. Can anyone do it in one SELECT statement?

TIA

---

DROP TABLE details;
DROP TABLE master;
CREATE TABLE master (pk NUMBER NOT NULL,

                     group_val CHAR(1)
         CONSTRAINT check_group_val CHECK (group_val IN ('A','B'))
                    );

CREATE TABLE details (pk NUMBER NOT NULL,
                      master_fk NUMBER);

INSERT INTO master VALUES(1,'A');
INSERT INTO details VALUES(101,1);

INSERT INTO master VALUES (2,'B');
INSERT INTO details VALUES(201,1);
INSERT INTO details VALUES(202,2);

INSERT INTO master VALUES (3,'A');
COMMIT; SELECT a.group_val,a.totalcnt,b.hasdetails_cnt   FROM (SELECT group_val,COUNT(*) totalcnt

          FROM master
         GROUP BY group_val) a,
       (SELECT group_val,COUNT(*) hasdetails_cnt
          FROM master m
         WHERE EXISTS (SELECT NULL FROM details d WHERE d.master_fk =
m.pk)
         GROUP BY group_val) b

 WHERE a.group_val = b.group_val (+);

CREATE OR REPLACE PROCEDURE junk
AS

   CURSOR c_junk IS

      SELECT a.group_val,a.totalcnt,b.hasdetails_cnt
        FROM (SELECT group_val,COUNT(*) totalcnt
                FROM master
               GROUP BY group_val) a,
             (SELECT group_val,COUNT(*) hasdetails_cnt
                FROM master m
               WHERE EXISTS (SELECT NULL FROM details d WHERE
d.master_fk=m.pk)
               GROUP BY group_val) b
       WHERE a.group_val = b.group_val (+);

   ignore c_junk%ROWTYPE;
BEGIN
   FOR r IN c_junk LOOP

      ignore := r;
   END LOOP;
END;
/
show errors
EXECUTE junk;

Ben Ryan - VanCity C.U.
Vancouver, B.C.
Canada Received on Tue Mar 25 1997 - 00:00:00 CST