I have an Apex page which selects databases from drop down:
All
DBDEV
DBTEST
POCTEST
The IG Query currently is:
select db_name, description,enable,allow ,
(select database_link from links a where a.db_name = b.db_name) link_db from db_details b;
DB_DETAILS table data in local database.
DB_NAME DESCRIPTION ENABLE ALLOW
DBDEV DATA 1 Y Yes
DBTEST DATA 2 Y Yes
POCTEST DATA 3 Y Yes
DBDEV DATA 4 Y Yes
DBTEST DATA 5 Y Yes
LINKS TABLE DATA:
DB_LINK DATABASE
DDEV DBDEV
DTEST DBTEST
PTEST POCTEST
In this query i need to add a column that compares the table db_details with db_details table in different databases.
Ex: DB_DETAILS and DB_DETAILS@DBDEV have same table structure and same 4 columns: db_name, description,enable,allow
So, there has to be column by column comparison and the difference needs to be reflected in the field in form of count or a colour indication.
This field should be a link and that should then open a pop-up showing the rows from both tables, i.e. local and the database.
How can this be achieved in apex?
You could do it with collections so that you fetch the remote data into a local collection and then use that, eg
DECLARE
l_query varchar2(200) := 'SELECT * FROM DB_DETAILS@'||:P1_DB_LINK;
begin
IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'MYDATA') THEN
APEX_COLLECTION.DELETE_COLLECTION (p_collection_name => 'MYDATA');
end if;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
p_collection_name => 'MYDATA',
p_query => l_query,
p_generate_md5 => 'YES');
End;
which then lets you either build reports or run SQL along the lines of:
select
seq_id link_item,
seq_id ID,
c001 mycol1,
c002 mycol2,
c003 mycol3,
...
from apex_collections
where collection_name = 'MYDATA';
But it sort of sounds to me like you're building a cross-database comparison tool - there are plenty of tools (free/paid) out there that already do this, so perhaps its not worth re-inventing the wheel?