Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

PLSQL and Flashback Data Archive Problems.

Mustafa, 2021-01-302021-01-30

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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
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:

Oracle PL/SQL
1
alter table tmp drop column order_Date;

and re run the procedure:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
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!

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
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).

Oracle PL/SQL
1
2
3
4
5
select * from tmp;
 
        C1        C20         C3
---------- ---------- ----------
         1          2          3

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

Oracle PL/SQL
1
2
3
4
5
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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.

19c Administration Development errorflashback data archiveflashback queryinvalid numberORA-00932: inconsistent datatypesORA-01007: variable not in select listoracleplsql

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes