SQL92 Inline view limitation in Oracle?

From: <drpaner_at_intrex.net>
Date: Fri, 21 Aug 1998 18:28:45 GMT
Message-ID: <6rke8s$287$1@nnrp1.dejanews.com>

Good afternoon,

Recently, I've been using the SQL92 standard inline view as part of some interesting queries. The inline view is essentially a query that is defined inside the FROM clause of a SQL statement. You are able to pass in parameters into this construct.

I've been able to use the inline view mechanism with great success except for one area. Using Oracle 7.2.2.4.0, I have placed a query with an inline view into both an Oracle function and Oracle stored procedure. The query runs successfully when ran outside of the wrapper of a function or procedure, however, when included in a procedure or function fails.

The following is a simple example..

CREATE OR REPLACE FUNCTION Simple()
RETURN NUMBER
IS

ln_Xcount INT := 0;

BEGIN select count( dualinner.dummy )
  into ln_Xcount
 from dual dual1,

      ( select dummy
          from dual dual2
      ) dualinner

where dual1.dummy = dualinner.dummy
;

Return ln_Xcount;

END Simple;

Again, the query that you see here works just fine as a stand-alone entity, but causes a compile error when included in the function.

I've scanned all of my Oracle docs but do not see any explanations. Is this a limitation of Oracle or this version or Oracle?

Any assistance on this is greatly appreciated! Thank you,
Daniel

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Aug 21 1998 - 13:28:45 CDT