Re: strange PL/SQL Error

"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 - 10:31:40 CDT