1

I have a scenario where I am updating an table in oracle APEX (v 20.2.0.00.20) every day. I would like to display the information in the page navigation bar at top or as a card anywhere on the page, as when the table gets updated as

"Last updated : date & time"

Is this achievable or possible in oracle APEX , could not find any documents or information related to that? Any leads would be great

MT0
  • 143,790
  • 11
  • 59
  • 117
Rebecca
  • 37
  • 5
  • 1
    Please [edit] your question to include the `CREATE TABLE` statement(s) for your table(s). Do you have a `DATE` or `TIMESTAMP` column that stores the time a column was updated? If not, how do you expect to determine when a column was updated? – MT0 Sep 09 '21 at 13:56
  • @MT0 No I am not because , I thought that it would be functionality to get the information that when the table is getting updated like DMV – Rebecca Sep 11 '21 at 13:39

2 Answers2

1

enter image description here

Step 1: Go to Shared components / Application Items and create one item. Call it whatever you want.

enter image description here

Step 2: Go to Shared components / Application Processes and create one and write your query to save the result into the Application Item that you created before.

enter image description here

Step 3 and final step: Add the Application Item into a new item in Shared components / Lists / Desktop Navigation Bar. You have to add a &ITEMNAME. as part of substitution string.

enter image description here

enter image description here

Also, you can use this item wherever you want because is stored in session

Enrique Flores
  • 716
  • 4
  • 13
  • 1
    But this would display the current date and time. Can I use that select statement to get data from any table in the apex? – Rebecca Sep 11 '21 at 13:45
0

Presuming that table contains last_update_date column which is populated "somehow" - could be a database trigger, for example:

create or replace trigger trg_biu_your_table
  before insert or update on your_table
  for each row
begin
  :new.last_update_date := sysdate;
end;
/

then you'd create an item (on that particular Apex page) whose source is SQL query which looks like this:

select max(last_update_date)
from your_table

That should do it.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • if I want to use this will it work ? `SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'dbname') AND OBJECT_ID=OBJECT_ID('tablename')` – Rebecca Sep 11 '21 at 13:47
  • I don't know. You're selecting from a table (I don't know) owned by SYS. You used some functions (OBJECT_NAME, DB_ID) I don't know. But, if you run that query in SQL*Plus or SQL Developer or Apex' SQL Workshop and it returns data you expect, then I'd say that this *will* work in Apex as well. – Littlefoot Sep 11 '21 at 15:15