Re: select from (select...)

Oren Nakdimon wrote:
>
> Hi.
> I'm trying to do a query with sub-query in the FROM clause, in PL/SQL
> (Oracle 7.3.3, PL/SQL 2.3.2.3.1 on Windows NT 4).
> The sub-query is using some variables, and it's not working.
>
> This is a simple example:
>
> declare
> a number;
> r dual%rowtype;
> begin
> select *
> into r
> from ( select *
> from dual
> where a=a );
> end;
> /
>
> declare
> *
> ERROR at line 1:
> ORA-06550: line 7, column 9:
> PLS-00320: the declaration of the type of this expression is incomplete
> or
> malformed
> ORA-06550: line 5, column 4:
> PL/SQL: SQL Statement ignored
>
> When I don't use variables in the sub-query it works.
> It works also when I use the sub-query in the FROM clause of other
> statements (e.g. DELETE).
>
> Can someone help me ?
> Thanks, Oren.
> =============================
> Oren Nakdimon
> Golden Screens Ltd.
> email: oren_at_gsit.co.il
> tel: +972-3-7510836
> fax: +972-3-7518221
> =============================

I had the same problem a while back. I found that I could execute this structure in straight sql, but if any plsql reference was made (variables, functions or even INTO) the statement hit the error. Sorry, no answers, just that it must be a bug(?) in the PL/SQL interface to sql. I never called Oracle on it. Anyone else? Received on Mon Jun 23 1997 - 00:00:00 CDT