drop table t2 purge
Table dropped.
drop table t1 purge
Table dropped.
create table t1
as
select *
from all_objects
where rownum <= 10000
Table created.
create table t2
as
select *
from t1
where rownum <= 10
Table created.
create or replace procedure p1( myscn in varchar2 ) as
cursor c1 is
select v1.object_name
from
t1 as of scn myscn v1
join
t2 as of scn myscn v2
on
v2.object_id = v1.object_id
;
l_row c1%rowtype;
begin
open c1;
fetch c1 into l_row;
dbms_output.put_line(l_row.object_name);
close c1;
end;
Errors: PROCEDURE P1 Line/Col: 3/16 PLS-00341: declaration of cursor 'C1' is incomplete or malformed Line/Col: 4/17 PL/SQL: ORA-00984: column not allowed here Line/Col: 4/17 PL/SQL: SQL Statement ignored Line/Col: 13/15 PL/SQL: Item ignored Line/Col: 17/9 PL/SQL: SQL Statement ignored Line/Col: 17/23 PLS-00320: the declaration of the type of this expression is incomplete or malformed Line/Col: 18/9 PL/SQL: Statement ignored Line/Col: 18/30 PLS-00320: the declaration of the type of this expression is incomplete or malformedMore Details: https://docs.oracle.com/error-help/db/ora-24344
create or replace procedure p1( myscn in varchar2 ) as
cursor c1 is
select v1.object_name
from
(select * from t1 as of scn myscn) v1
join
(select * from t2 as of scn myscn) v2
on
v2.object_id = v1.object_id
;
l_row c1%rowtype;
begin
open c1;
fetch c1 into l_row;
dbms_output.put_line(l_row.object_name);
close c1;
end;
Procedure created.