Re: PL/SQL nested select ...

From: Peter H. Larsen <plarsen_at_dc.dynares.com>
Date: 1998/06/12
Message-ID: <35814914.29BB4B01@dc.dynares.com>#1/1

Hi Wlad,
Hmmm ...
Connected to:
Oracle7 Server Release 7.3.4.0.0 - Production With the distributed, replication and parallel query options PL/SQL Release 2.3.4.0.0 - Production

SQL> declare

  2     cursor c1(a number) is
  3     select Q.* from (select * from dba_tables where a=1) q
  4     where q.table_name = 'ABC';

  5 begin
  6 for w1 in c1(1) loop
  7 null;
  8 end loop;
  9 end;
 10 /

PL/SQL procedure successfully completed.

As you see, I'm running this on 7.3.4 (and on NT4) ... which does contain some NT patches, and I have no problems running the above. I would suggest that you reinstall the RDBMS, or ensure that your NT is running SP3.

• Peter H. Larsen

Wlad.M wrote:
>
> Hi!
>
> I'am running Oracle 7.3.3 on WinNT40 and
>
> > SQL> declare
> > 2 cursor c1(a number) is
> > 3 select Q.* from (select * from dba_tables where a=1) q
> > 4 where q.table_name = 'ABC';
> > 5 begin
> > 6 for w1 in c1(1) loop
> > 7 null;
> > 8 end loop;
> > 9 end;
> > 10 /
> >
>
> causes same error
>
> ------------------------------------------------------
> Error PLS-00320 the declaration of the type of this expression is
> incomplete or malformed :
>
> cursor curs(param integer) is
> select Q.* from (select st.* from some_table st where st.id<param) Q where
> Q.txt is not NULL;
> ------------------------------------------------------
> Success, but no indexes used:
>
> cursor curs(param integer) is
> select Q.* from some_table Q where Q.id<param and Q.txt is not NULL;
> ------------------------------------------------------
>
> Regards, Wlad.M.
>
> Peter H. Larsen <plarsen_at_dc.dynares.com> wrote
> <358123BC.B28D8A37_at_dc.dynares.com>...
> > Hi!
> >
> > Huh??
> > SQL> declare
> > 2 cursor c1(a number) is
> > 3 select Q.* from (select * from dba_tables where a=1) q
> > 4 where q.table_name = 'ABC';
> > 5 begin
> > 6 for w1 in c1(1) loop
> > 7 null;
> > 8 end loop;
> > 9 end;
> > 10 /
> >
> > PL/SQL procedure successfully completed.
> >
> > What version are you running this on?
> >
> > - Peter H. Larsen
> > Oracle Consultant
> >
> > Wlad.M wrote:
> > >
> > > Hello All !
> > >
> > > Please help
> > > Why PL/SQL dont understand this:
> > >
> > > cursor curs(param integer) is
> > > select Q.* from (select * from some_table where
 id<param) Q where Q.txt
> > > is not null;
> > >
> > > Regards, Wlad.M.
> >
Received on Fri Jun 12 1998 - 00:00:00 CDT