Autotransx
Programmer
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
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