Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can autonomous transactions execute in a distributed environment?

Status
Not open for further replies.

Autotransx

Programmer
Jul 31, 2001
1
US
I'm getting ORA-00164. I didn't know that simply selecting over a database link is considered a "distributed transaction". I have numerous queries in procedures & functions like the following:

INSERT INTO dmo.dmo_budget_measure
SELECT COLUMNS...
FROM
(
SELECT * FROM
(SELECT COLUMNS...
FROM stew.stew_budget_header@ods db,
dmo.dmo_budget_category_dimension bc,
(
SELECT COLUMNS...
FROM stew.stew_budget_daypart@ods
UNION
SELECT COLUMNS...
FROM stew.stew_budget_adver_ae@ods
UNION
SELECT COLUMNS...
FROM stew.stew_budget_buy_type@ods
UNION
SELECT COLUMNS...
FROM stew_budget_office@ods
UNION
SELECT COLUMNS...
FROM stew.stew_budget_selling_title@ods
) a
WHERE db.budget_id = a.budget_id
AND bc.budget_category_code = db.budget_category
GROUP BY COLUMNS...
)
) b,
stew.stew_account_list@ods al
WHERE al.net_id(+) = b.net_id
AND al.advertiser_id(+) = b.advertiser_id;

I can't execute them unless I remove the autonomous transaction pragma from all procedures that are called by the procedure/function with the query that has the link. Pretty annoying! Does anyone have any experience in this area.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top