Flashback query bug?
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Sep 2012 14:17:04 -0500
11.2.0.3 on Linux 5
Any idea why the procedure fails when joining 2 tables?
admin@DB> create table cs_t1(c1 number);
Table created.
admin@DB> create table cs_t2(c1 number);
Table created.
admin@DB> select current_scn from v$database;
CURRENT_SCN
-----------
3872916384
admin@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@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@REDDB> show errors
No errors.
db_admin@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@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@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@REDDB> show errors
No errors.
db_admin@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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
