Re: strange PL/SQL Error

The problem is not that inline views are not supported, but rather that anything out of the ordinary (eg use of minus) causes them to fail.

"Gene Hubert" <gwhubert_at_hotmail.com> wrote in message news:7e3fa619.0107300731.2685f561_at_posting.google.com...
> "Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message
 news:<smY87.22017$vN4.160911_at_news11-gui.server.ntli.net>...
> > The problem is almost certainly because the PL/SQL engine has its own
> > (backlevel) SQL interpreter that doesn't properly support inline views.
>
> PL/SQL may interpret SQL differently than for interactive statements
> but it does work with some inline views as can be seen in the second
> stored procedure below. As far as what caused the original problem
> I'm afraid I don't know. I have seen other sql statements that work
> interactively but fail in a stored procedure.
>
> Gene Hubert
>
> ------------
>
> SQL> desc g1
> Name Null? Type
> ------------------------------- -------- ----
> ID NUMBER
> VAL NUMBER
>
> SQL> select * from g1;
>
> ID VAL
> ---------- ----------
> 1
> 2
>
> 1 create or replace procedure junk as
> 2 cnt pls_integer;
> 3 begin
> 4 select count(*) into cnt from g1;
> 5 dbms_output.put_line(cnt);
> 6* end;
>
> Procedure created.
>
> SQL> set serveroutput on
> SQL> execute junk
> 2
>
> PL/SQL procedure successfully completed.
>
> 1 create or replace procedure junk as
> 2 cnt pls_integer;
> 3 begin
> 4 select count(*) into cnt from (
> 5 select * from g1);
> 6 dbms_output.put_line(cnt);
> 7* end;
>
> Procedure created.
>
> SQL> execute junk
> 2
>
> PL/SQL procedure successfully completed.
Received on Mon Jul 30 2001 - 11:55:11 CDT