Re: Query to return PK sequence gap ??

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 12 Oct 2007 12:03:12 -0700
Message-ID: <1192205992.071697.101280@z24g2000prh.googlegroups.com>

On Oct 12, 5:09 pm, Brian Tkatch <N/A> wrote:
> On Fri, 12 Oct 2007 10:53:16 -0000, "Vladimir M. Zakharychev"
>
>
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
> >On Oct 10, 5:59 pm, Brian Tkatch <N/A> wrote:
> >> On Wed, 10 Oct 2007 15:30:15 +0200, codadilupo
>
> >> <codadil..._at_operamail.com> wrote:
> >> >Brian Tkatch wrote:
>
> >> >> But this solution does not work in 9i.It does work in 10g.
>
> >> >It doesn't work in 9.0.1 but it works in 9.2.
>
> >> I stand corrected.
>
> >> I quicky tested by running: select 1 from dual connect by level <=
> >> 10; on its own. That does not work in 9.2, but does work in 10g. As a
> >> subquery, however, it works even in 9.2.
>
> >> B.
>
> >Works for me in 9.2.0.8.
>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
> PL/SQL Release 9.2.0.8.0 - Production
> CORE 9.2.0.8.0 Production
> TNS for Linux: Version 9.2.0.8.0 - Production
> NLSRTL Version 9.2.0.8.0 - Production
>
> SQL> SELECT 1 FROM Dual CONNECT BY LEVEL <= 10;
>
> 1
> ----------
> 1
>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
> PL/SQL Release 10.2.0.1.0 - Production
> CORE 10.2.0.1.0 Production
> TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
> NLSRTL Version 10.2.0.1.0 - Production
>
> SQL> SELECT 1 FROM Dual CONNECT BY LEVEL <= 10;
>
> 1
> ----------
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
> 1
>
> 10 rows selected.
>
> B.

SQL> select banner from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production

SQL> select 1 from dual connect by level <= 10;

         1

---

         1
         1
         1
         1
         1
         1
         1
         1
         1
         1

10 rows selected

But I must admit I cheated a bit: this is from PL/SQL Developer command window, which I used to test the queries, not from SQL*Plus, which gives exactly the same picture as yours: one row returned (and that's regardless of the client version - tried with 10.1.0.5 client against the same db with the same outcome.) Not sure who's to blame here... Different Oracle versions even have different caps on levels of hierarchy: 9.2.0.8 has 100, 9.2.0.6 reportedly had 250, 10g seem to have no limit at all.

So it seems to be a not completely reliable way to generate monotonic sequences when used standalone, but it appears to be working identically in all versions since 9.2 when used in subqueries.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Oct 12 2007 - 14:03:12 CDT