I am terrible with database links. We are having a problem with a link from a 9.0.1 database to an 8.0.4 database.
Following is the problem:
(Embedded image moved to file: pic16827.pcx) Full Description: I am
trying to run a SQL query with an inline view (see below) in
EDWDEV01 using a link to DWDEV01 to access the ACCT_PAY_TYPE
table. If I run the query in EDWDEV01 I get "no rows
selected". If I move the query to DWDEV01 and remove the
conneciton descriptions, I get 10 rows selected. Is this
permissions, database versions, or what? Any suggestions,
ideas?
SELECT
apt.acct_no,
apt.pay_seq_no,
apt.src_chng_batch_win_dt_key wh_date_key,
0 wh_time_key,
apt.data_whse_mod_dt_tm wh_mod_dt_tm,
DECODE(apt.src_chng_batch_win_dt_key, subq.maxkey,'C','H') wh_row_sts,
apt.data_src_mod_dt_tm wh_row_eff_dt_tm,
apt.data_src_mod_dt_tm upd_dt_tm,
apt.acct_pay_desc,
apt.status
FROM acct_pay_type@whse_dbo_dwdev01 apt,
(SELECT
acct_no,
data_sts,
pay_seq_no,
eff_dt_x,
MAX(src_chng_batch_win_dt_key) maxkey
FROM acct_pay_type@whse_dbo_dwdev01
GROUP BY acct_no, data_sts, pay_seq_no, eff_dt_x) subq
WHERE apt.acct_no = subq.acct_no
AND apt.data_sts = subq.data_sts
AND apt.pay_seq_no = subq.pay_seq_no
AND apt.eff_dt_x = subq.eff_dt_x
ORDER BY 1, 2, 3 DESC;
Following is the link information:
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
---------- --------------- ---------- ---------- ----------
PUBLIC WHSE_DBO_DWDEV0 WHSE_DBO DWDEV01 21-JAN-02
1.GELCO.COM
If I do a select * from acct_pay_type@whse_dbo_dwdev01 from within the
remote database (not DWDEV01), I get ten rows returned. If I run this sql
from the remote database, (not DWDEV01), I get no rows returned. If I run
the SQL from DWDEV01 with the @whse_dbo_dwdev01 removed, I get 10 rows
returned.
What is my problem? Seems like I'm missing permissions somewhere or
something like that. Any help is appreciated.
Cherie Machler
Oracle DBA
Gelco Information Network
pic16827.pcx
Description: Binary data
