Re: "order siblings by" problem with Oracle 9.0.1.1

"Vladimir M. Zakharychev" wrote:

> SQL> select banner from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
> PL/SQL Release 9.2.0.2.1 - Production
> CORE 9.2.0.2.0 Production
> TNS for 32-bit Windows: Version 9.2.0.2.0 - Production
> NLSRTL Version 9.2.0.2.0 - Production
>
> SQL> declare
> 2 v integer;
> 3 begin
> 4 select x into v from (
> 5 select 1 x from dual
> 6 )
> 7 start with x=1
> 8 connect by prior x = x - 1
> 9 order siblings by x;
> 10 dbms_output.put_line ('X is:'||v);
> 11 end;
> 12 /
> X is:1
>
> PL/SQL procedure successfully completed.
>
> So I think this is a bug in PL/SQL (is was claimed that 9i has
> unified engine used by both SQL and PL/SQL, but apparently
> this was not the case until 9.2).
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.

I believe it was never claimed that the SQL and PL/SQL engines were merged until 9.2.

But in addition to the separate SQL and PL/SQL engines prior to 9.2 the major reason why things work in SQL*
Plus but not when wrapped into a PL/SQL block is permissions granted through roles.

Daniel Morgan Received on Mon Feb 17 2003 - 10:33:14 CST