I have written about flashback data archive before. if you didn’t read please check these post first.
I’ve already mentioned about some problems on my second post but these are not kind of problems which will stop your work. The problem I am about talk is that kind of problem.
From my perspective, it seems that flashback is designed to be used in SQL. track the history of your tables (even ddl changes) etc but if you start to use flashback in PLSQL, you will hit some walls!
PLSQL is not much dynamic language yet. Please don’t get me wrong. I am and Oracle Database Developer and Administrator. I am not interested any other databases and use SQL and PLSQL all the time but there some limits that cause to use very hard solutions some times.
I have tested the codes below at 19.8 database.
Flashback Data Archive(FDA) is a very useful option. Even if you add, modify or drop a column, you will be able to see old versions of a table and that is where my problem starts:
I created a table and add it to a flashback data archive. I add some sleep codes to wait necessary internal tables to be created. in the end, at 14:49:42 (my local time) I have a table which contains 2 rows. a simple procedure called SP_X is getting a parameter as DATE and uses this date in flashback query.
as you can see this procedure works well. I didn’t demonstrate different data in different times but it works as well.
Let’s drop a column from this table:
and re run the procedure:
as you see even if I am trying to get data from sysdate, I got an error. problem is even if I tried to query with previous dates cause same error starting now!
even dropping and recreating the procedure is not working. as far as I understand, Oracle cannot decide exact definition of the query anymore. adding a column does not cause this problem but dropping a column does.
you might think of using FOR loop to fetch cursor but you will hit the same error.
by the way queries that we are trying to run are working correctly in sql:
so, using flashback queries in plsql seems a little bit dangerous. Actually one of my client also gets “ORA-01007: variable not in select list” error in plsql (sql is perfectly fine). so be careful while using flashback in plsql.
during my tests, I rename cust_id column and I started to got “ORA-00932: inconsistent datatypes: expected NUMBER got DATE” error. after cust_id column I got, order_date column so that make me think that, column order might be changing and I realized there is a greater problem in this!
here is sample code:
there columns c1,c2,c3 and they have ordered number in them (1,2,3).
column order is not changed but when I used this with flasback query:
column order has changed even if I asked for sysdate. in plsql this could cause problems because you might not aware of this and wrong variables might store your data:
as you see r_tmp.C20 variable is storing data of C3 column because it comes first in select list when flashback query is used.
so, very very careful about flashback query and plsql.
wish you healthy days.