Flashback query bug?

From: Stephens, Chris <Chris.Stephens_at_adm.com>
Date: Wed, 19 Sep 2012 14:17:04 -0500
Message-ID: <D95BD5AFADBB0F4E9BB6C53F14D3A05005F5AC897C_at_JRCEXC1V1.research.na.admworld.com>

11.2.0.3 on Linux 5
Any idea why the procedure fails when joining 2 tables?

admin_at_DB> create table cs_t1(c1 number);

Table created.

admin_at_DB> create table cs_t2(c1 number);

Table created.

admin_at_DB> select current_scn from v$database;

CURRENT_SCN

---

3872916384

admin_at_DB> SELECT c1.c1
  2 FROM cs_t1 AS OF SCN 3872916384 c1

  3       JOIN cs_t2 AS OF SCN 3872916384 c2
  4         ON c1.c1 = c2.c1;

no rows selected

admin_at_DB> create or replace
  2 procedure cs_p( myscn NUMBER ) IS
  3 CURSOR cur IS
  4 SELECT c1.c1
  5 FROM cs_t1 AS OF SCN 3872916384 c1

  6       JOIN cs_t2 AS OF SCN 3872916384 c2
  7         ON c1.c1 = c2.c1;

  8
  9 l_row cur%rowtype;
10 begin
11 open cur;
12 fetch cur into l_row;
13 close cur;
14 end;
15 /

Procedure created.

db_admin_at_REDDB> show errors
No errors.
db_admin_at_REDDB> create or replace
  2 procedure cs_p( myscn NUMBER ) IS
  3 CURSOR cur IS
  4 SELECT c1.c1
  5 FROM cs_t1 AS OF SCN myscn c1

  6       JOIN cs_t2 AS OF SCN myscn c2
  7         ON c1.c1 = c2.c1;

  8
  9 l_row cur%rowtype;
10 begin
11 open cur;
12 fetch cur into l_row;
13 close cur;
14 end;
15 /

Warning: Procedure created with compilation errors.

db_admin_at_REDDB> show errors
Errors for PROCEDURE CS_P:

LINE/COL ERROR
-------- -----------------------------------------------------------------

2/8      PLS-00341: declaration of cursor 'CUR' is incomplete or malformed
3/13     PL/SQL: SQL Statement ignored
3/13     PL/SQL: ORA-00984: column not allowed here
8/7      PL/SQL: Item ignored
11/1     PL/SQL: SQL Statement ignored
11/16    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

db_admin_at_REDDB> create or replace
  2 procedure cs_p( myscn NUMBER ) IS
  3 CURSOR cur IS
  4 SELECT c1.c1
  5 FROM cs_t1 AS OF SCN myscn c1;
  6
  7 l_row cur%rowtype;
  8 begin
  9 open cur;
10 fetch cur into l_row;
11 close cur;
12 end;
13 /

Procedure created.

db_admin_at_REDDB> show errors
No errors.
db_admin_at_REDDB>

CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 19 2012 - 14:17:04 CDT