Skip to Main Content
  • Questions
  • Oracle dynamic database link from variables

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, MA.

Asked: December 02, 2016 - 6:50 am UTC

Last updated: December 02, 2016 - 2:35 pm UTC

Version: 11gR2

Viewed 1000+ times

You Asked

hi tom i am facing this query in dynamic sql using dblinks plz simplifie this query and explain me



sqlquery := 'select sv.display_value status_id,s.parent_account_no,s.emf_config_id,cm.mkt_code'
|| ' from service@'
|| TRIM (v_ds_database)
|| ' '
|| 's, service_status@'
|| TRIM (v_ds_database)
|| ' '
|| 'ss, status_values@'
|| TRIM (v_ds_database)
|| ' '
|| 'sv,'
|| 'cmf@'
|| TRIM (v_ds_database)
|| ' '
|| 'cm'
|| ' where s.subscr_no=:b_subscr_no'
|| ' and s.subscr_no_resets= :b_subscr_no_resets'
|| ' and s.subscr_no= ss.subscr_no'
|| ' and s.subscr_no_resets = ss.subscr_no_resets'
|| ' and ss.status_id=sv.status_id'
|| ' and s.parent_account_no = cm.account_no and ss.inactive_dt is null';

and Chris said...

It's taking a variable to dynamically provide the database link name. So the query becomes a join of the tables over the db link:

select sv.display_value status_id,s.parent_account_no,s.emf_config_id,
 cm.mkt_code
from service@dblink s, 
   service_status@dblink ss, 
   status_values@dblink sv,
   cmf@dblink cm
where s.subscr_no       =:b_subscr_no
and s.subscr_no_resets  = :b_subscr_no_resets
and s.subscr_no         = ss.subscr_no
and s.subscr_no_resets  = ss.subscr_no_resets
and ss.status_id        =sv.status_id
and s.parent_account_no = cm.account_no
and ss.inactive_dt     is null ; 


What it means beyond that, you'll need to speak with someone who knows and understands your database...

Is this answer out of date? If it is, please let us know via a Comment