I have several stored procredures which use minus, but they do not use
in-line views
Gene Hubert wrote in message
<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 Tue Jul 31 2001 - 11:17:11 CDT
