PLSQL and Flashback Data Archive Problems. – Keep Learning Keep Living

Hi,

I have written about flashback data archive before. if you didn’t read please check these post first.

https://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/

https://mustafakalayci.me/2020/09/10/flashback-data-archive-problems/

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:

create flashback archive fda tablespace users retention 1 year;

create table tmp(id number, cust_id number, order_date date) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 10, sysdate-10);
insert into tmp values (2, 20, sysdate-20);
commit;

exec dbms_lock.sleep(7);

select sysdate from dual;

SYSDATE
-------------------
30/01/2021 14:49:42

--------------------------------------------------

create or replace procedure sp_X(p_main date) as 
  cursor C_DATA (p date) is
    select * from tmp as of timestamp (p);
  
  r_tmp C_DATA%rowtype;
begin
  open c_data(p_main);
  fetch c_data into r_tmp;
  close c_data;
end;
/

create flashback archive fda tablespace users retention 1 year;

create table tmp(id number, cust_id number, order_date date) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 10, sysdate-10);

insert into tmp values (2, 20, sysdate-20);

commit;

exec dbms_lock.sleep(7);

select sysdate from dual;

SYSDATE

-------------------

30/01/2021 14:49:42

--------------------------------------------------

create or replace procedure sp_X(p_main date) as

cursor C_DATA (p date) is

select * from tmp as of timestamp (p);

r_tmp C_DATA%rowtype;

begin

open c_data(p_main);

fetch c_data into r_tmp;

close c_data;

end;

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.

exec sp_X(sysdate);

PL/SQL procedure successfully completed.


exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

exec sp_X(sysdate);

PL/SQL procedure successfully completed.

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

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:

alter table tmp drop column order_Date;

1	alter table tmp drop column order_Date;

and re run the procedure:

exec sp_X(sysdate);

Error starting at line : 20 in command -
BEGIN sp_X(sysdate); END;
Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MUSTAFA.SP_X", line 8
ORA-06512: at line 1
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

exec sp_X(sysdate);

Error starting at line : 20 in command -

BEGIN sp_X(sysdate); END;

Error report -

ORA-00932: inconsistent datatypes: expected - got -

ORA-06512: at "MUSTAFA.SP_X", line 8

ORA-06512: at line 1

00932. 00000 - "inconsistent datatypes: expected %s got %s"

*Cause:

*Action:

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!

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

Error starting at line : 20 in command -
BEGIN sp_X(sysdate); END;
Error report -
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "MUSTAFA.SP_X", line 8
ORA-06512: at line 1
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

exec sp_X(to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

Error starting at line : 20 in command -

BEGIN sp_X(sysdate); END;

Error report -

ORA-00932: inconsistent datatypes: expected - got -

ORA-06512: at "MUSTAFA.SP_X", line 8

ORA-06512: at line 1

00932. 00000 - "inconsistent datatypes: expected %s got %s"

*Cause:

*Action:

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:

MUSTAFA@orcl-1>select * from tmp as of timestamp (sysdate);

        ID    CUST_ID
---------- ----------
         1         10
         2         20

MUSTAFA@orcl-1>select * from tmp as of timestamp (to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

        ID    CUST_ID ORDER_DATE
---------- ---------- -------------------
         1         10 20/01/2021 14:49:29
         2         20 10/01/2021 14:49:29

MUSTAFA@orcl-1>select * from tmp as of timestamp (sysdate);

ID CUST_ID

---------- ----------

1 10

2 20

MUSTAFA@orcl-1>select * from tmp as of timestamp (to_date('30/01/2021 14:49:42', 'dd/mm/yyyy hh24:mi:ss'));

ID CUST_ID ORDER_DATE

---------- ---------- -------------------

1 10 20/01/2021 14:49:29

2 20 10/01/2021 14:49:29

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.

EDIT:

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:

create table tmp(c1 number, c2 number, c3 number) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 2, 3);

commit;

exec dbms_lock.sleep(7);

alter table tmp rename column c2 to c20;

create table tmp(c1 number, c2 number, c3 number) flashback archive fda;

exec dbms_lock.sleep(15);

insert into tmp values (1, 2, 3);

commit;

exec dbms_lock.sleep(7);

alter table tmp rename column c2 to c20;

there columns c1,c2,c3 and they have ordered number in them (1,2,3).

select * from tmp;

        C1        C20         C3
---------- ---------- ----------
         1          2          3

select * from tmp;

C1 C20 C3

---------- ---------- ----------

1 2 3

column order is not changed but when I used this with flasback query:

select * from tmp as of timestamp(sysdate);

        C1         C3        C20
---------- ---------- ----------
         1          3          2

select * from tmp as of timestamp(sysdate);

C1 C3 C20

---------- ---------- ----------

1 3 2

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:

set serveroutput on;

create or replace procedure sp_X(p_main date) as 
  cursor C_DATA (p date) is
    select * from tmp as of timestamp (p);
  
  r_tmp C_DATA%rowtype;
begin
  open c_data(p_main);
  fetch c_data into r_tmp;
  dbms_output.put_line(r_tmp.c1||'-'||r_tmp.c20||'-'||r_tmp.c3);
  close c_data;
end;
/

exec sp_x(sysdate);
1-3-2

set serveroutput on;

create or replace procedure sp_X(p_main date) as

cursor C_DATA (p date) is

select * from tmp as of timestamp (p);

r_tmp C_DATA%rowtype;

begin

open c_data(p_main);

fetch c_data into r_tmp;

dbms_output.put_line(r_tmp.c1||'-'||r_tmp.c20||'-'||r_tmp.c3);

close c_data;

end;

exec sp_x(sysdate);

1-3-2

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.

Leave a Reply Cancel reply