[Oracle-l] Flashback query error

Crisis averted ... got the data back from our replication site which
just happened to be up for BCP testing ... the fates are smiling on us
today :)

However ... I'd still like to figure out what happened with the
flashback procedure. I've used this successfully in the past so I'm
puzzled. The error is:

matl_doc_month.LOAD_DATE);

*

ERROR at line 62:
ORA-06550: line 62, column 19:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 37, column 1:
PL/SQL: SQL Statement ignored

Sorry for the incomplete post earlier ... I was watching the clock run
down on my retention window and stressing :)

Robyn

You are fetching into mal_doc_month_rec, but using matl_doc_month.

Robyn,

matl_doc_month is the table, right? And matl_doc_month_rec is the
record type?

So, in the insert statement, in the values clause, shouldn't each column
name be prefixed with "matl_doc_month_rec", i.e., the record type, and
not "matl_doc_month", which is the table?

-Mark

It looks like Ron found your problem, but I was wondering what version you
are using? In 9.2 and higher you could do something like the following,
which is a whole lot shorter and less error-prone:

insert into matl_doc_month (select * from matl_doc_month as of timestamp
(sysdate - 60/1440));
Jason Heinrich
Oracle Database Administrator
Pensacola Christian College

Thanks Alberto but the problem has been solved. The error was:

ERROR at line 62:
ORA-06550: line 62, column 19:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 37, column 1:
PL/SQL: SQL Statement ignored

And the statement should have been:

declare
cursor flash_recover is
select * from matl_doc_month;
matl_doc_month_rec matl_doc_month%rowtype;
begin
dbms_flashback.enable_at_time(sysdate - 60/1440);
open flash_recover;
dbms_flashback.disable;
loop
fetch flash_recover into matl_doc_month_rec;
exit when flash_recover%notfound;
insert into matl_doc_month
values

(matl_doc_month_rec.ACCT_DOC_FYP,
matl_doc_month_rec.MATL_NUM,
matl_doc_month_rec.PLANT,
matl_doc_month_rec.MOVMNT_TYPE,
matl_doc_month_rec.STOR_LOC,
matl_doc_month_rec.RCVING_STOR_LOC,
matl_doc_month_rec.DEBIT_CREDIT_IND,
matl_doc_month_rec.PROFIT_CTR,
matl_doc_month_rec.GL_ACCT_NUM,
matl_doc_month_rec.BASE_UOM,
matl_doc_month_rec.BASE_QTY,
matl_doc_month_rec.TRANS_CNT,
matl_doc_month_rec.ACCT_DOC_FY,
matl_doc_month_rec.ACCT_DOC_FQ,
matl_doc_month_rec.ACCT_DOC_FP,
matl_doc_month_rec.ACCT_DOC_CY,
matl_doc_month_rec.ACCT_DOC_CQ,
matl_doc_month_rec.ACCT_DOC_CP,
matl_doc_month_rec.ACCT_DOC_CYP,
matl_doc_month_rec.SL_INV_GRP,
matl_doc_month_rec.COST_CTR,
matl_doc_month_rec.LOCAL_CURRENCY_AMT,
matl_doc_month_rec.REASON_FOR_MOVMNT_IND,
matl_doc_month_rec.LOAD_DATE);

end loop;
close flash_recover;
commit;
end;
/