Error in Subquery in PL/SQL Procedure

From: Brian Schalme <bschalme_at_mbnet.mb.ca>
Date: Wed, 14 Apr 1999 19:09:42 GMT
Message-ID: <3714E815.A5EE66B3@mbnet.mb.ca>

        Folks:

        Do subqueries really work in PL/SQL? For example when I try to compile this procedure:

create or replace package body brian_test is procedure uno is
BEGIN

SELECT NVL(le.lab_key,' '),
       NVL(le.reg_hours_calc,0),
       NVL(labd.adj_code,' '),
       le.emp_no, le.abbrev_name, le.dept, le.attendance_dept
FROM (SELECT labm.lab_key,
             labm.reg_hours_calc,
             em.emp_no, em.abbrev_name, em.dept,
             em.attendance_dept
      FROM labm, emp_mstr em
      WHERE SUBSTR(labm.lab_key(+),11) = em.emp_no) le,
     labd

WHERE le.lab_key = labd.lab_key (+);
end;
end;
/
show errors
exit

        I get the error message "PLS-00320: the declaration of the type of this expression is invalid" on the "(SELECT..." statement (the subquery). Now nothing is mis-spelled because I can take the guts of this and run it in SQL*Plus - it works fine. And I can see no forward references here.

        I try to run this in an MF COBOL program and the precompiler kicks out the same message. (procob...sqlcheck=semantics...).

	Environment:
	HP-UX		10.20
	Oracle Server	7.3.2.3.0
	PL/SQL		2.3.2.3.0
	SQL*Plus	3.3.2.0.0
	Pro*COBOL	1.8.2.0.0

	TIA
		Brian Schalme
		bschalme_at_mbnet.mb.ca

Received on Wed Apr 14 1999 - 14:09:42 CDT