Handling Errors in a distributed Transaction
Ivan, September 30, 2002 - 4:18 pm UTC
I can think of one case where DATABASE B needs to do a commit when it is being called from DATABASE A. The case being to log errors. How can you log errors in DATABASE B if DATABASE A has the only way to commit?
My scenerio is I have a remote loader who updates data in our main server. However a log file needs to be populated for all changes that happen to the database. However is an error occurs than the errors are never logged because it will never all DATABASE B to commit the logging.
Before we had distributed transactions I was able to use PRAGMA AUTONOMOUS_TRANSACTION and commit the error. Now I am unable in 8.1.7.2.1.
Your thoughts please...
October 01, 2002 - 9:35 am UTC
UTL_FILE would be an opportunity there.
If an error occurs, the changes NEVER occurred and hence, why would you want to commit log messages that never happened??
That the autonomous transaction *sometimes* and in very specific cases worked in 816 was a bug -- it was a dangerous situation.
Writing to a table within the database
ivan, October 10, 2002 - 1:58 pm UTC
Tom,
This is a follow up to my previous post in this subject.
I wasn't very clear on what I was writing to and the purpose. I am calling a package from another Oracle server and trying to commit into a table (distributed transaction). The purpose is to log errors into an error table so that I can see where the loader is not following the "rules". Whether it is a loader trying to update a column that they do not have rights to or when a column changes (history). I thought UTL_FILE just writes to a text file. I tried to use DBMS_PIPE but I still get a "ORA-00164: autonomous transaction disallowed within distributed transaction" error.
How can I do this?
October 11, 2002 - 7:39 pm UTC
Sorry, i thought I was clear -- you cannot, not in a distributed transaction.
UTL_FILE was my attempt to give you a workable solution, one that would let you capture the errors and move forward.
Autonomous transaction disallowed - why?
Ranga Chakravarthi, October 10, 2003 - 3:35 pm UTC
Tom,
"That the autonomous transaction *sometimes* and in very
specific cases worked in
816 was a bug -- it was a dangerous situation."
Could you please explain why it is dangerous?
Why can't I do an autonmous transaction over a distributed
transaction? Why is it disallowed?
TIA
October 10, 2003 - 4:32 pm UTC
in 8i, it was NOT supported.
in 9i it is now supported.
Reason for not allowing an autonmous transaction over distributed
Ranga Chakravarthi, October 13, 2003 - 3:22 pm UTC
Tom,
Could you comment on why it was not supported and what
the reason was for disallowing it?
Thanks
October 13, 2003 - 8:05 pm UTC
it wasn't supported -- thats all that can be said.
the code didn't exist.
therefore, it didn't work.
About -02041
Mariano, January 09, 2004 - 5:47 pm UTC
Hi, Tom.
I've read this page and its links (similar in fact to expert 1-on-1 chapter) but i cannot make sense about a situation i have with my databases.
i'm working in an 8.1.7.0.0, using it for a website with ASP and ODBC connection (The ora client in the web IIS server -not the same machine for the database server- is 9.2.0.4)
I'm using dbms_obfuscation_toolkit to encript/desencript passwords for the website but, cause this package is not working in my 8i, i send the password to be desencripted to a remote 9.2.0.4.0 database.
Since i changed the webserver oraclient from 7.3 to actual 9.2, when i try to execute the procedure i get ora-02041 error over and over again (this is not happening from sqlplus). in the remote database i just execute dbms_obfuscation_toolkit.des3decrypt and this action is wrapped in a procedure in my 8i with some data processing actions after using dbms_obfuscation_toolkit.
Can you suggest an action in order to correct this situation?
Regards
January 10, 2004 - 10:27 am UTC
ahh -- lets fixed the dbms_obfuscation thing.
It is a byte order issue. See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:791026226790#8473587738442 <code>
(doesn't affect the latest releases -- all platforms use the same byte ordering now)
You have an 8 byte key right now -- say the key was:
12345678
the key to unlock your data is:
43218765
swap the bytes -- in your decrypt just use the key:
substr(key,4,1)||substr(key,3,1)||substr(key,2,1)||substr(key,1,1)||
substr(key,8,1)||substr(key,7,1)||substr(key,6,1)||substr(key,5,1)
So, use the 8i stuff and just "fix the key" -- have your decrypt routine accept the key "as normal" but apply this tranformation to it.
Please don't answer my question
Mariano, January 09, 2004 - 7:32 pm UTC
now it's ok. it was all about dealing with MTS in the webserver.
regards.-
synonym for remote function doesn't work
James Su, February 05, 2004 - 5:33 pm UTC
SQL> create or replace function ff return number is begin return 200; end;
2 /
Function created.
SQL> conn XXXX/XXXX@dev32
Connected.
SQL> create database link dbl_rep connect to XXXXXXX identified by XXXXXXXX using 'dev2';
Database link created.
SQL> select * from dual@dbl_rep;
D
-
X
SQL> select ff@dbl_rep from dual;
FF
----------
200
SQL> create or replace synonym sy_test for ff@dbl_rep;
Synonym created.
SQL> select sy_test from dual;
select sy_test from dual
*
ERROR at line 1:
ORA-00904: "SY_TEST": invalid identifier
February 06, 2004 - 8:31 am UTC
look on the other page where you asked *the same thing*.
another question about remote function
James Su, February 06, 2004 - 9:15 am UTC
Sorry for posting my question twice. It seemed that something was wrong with this site yesterday. After I posted my question, I could never found it again.
I have a new question about remote function:
select sf_myfunction(colname)
from mytable@somelink
where ......
group by sf_myfunction(colname);
sf_myfunction is a local function and mytable is a remote table.
This query runs slowly. Can I improve the performance by moving sf_myfunction to @somelink that links to the same server where mytable located?
I was trying to do that yesterday and I failed to access to a synonym for remote function. Something is wrong with my Oracle9i for linux. So I am planning today to create a view on that server, and create a synonym for that view on the local server.
February 07, 2004 - 12:35 pm UTC
it takes a minute (or 15) for new things to be indexed. i update the text index on a schedule.
it probably would go faster to have a view at the remote site HOWEVER, I would recommend you look at my_function and say "do i really need a function or can i do this in sql" as that would be infinitely "faster" than calling plsql from sql.
sub sql vs function
James Su, February 09, 2004 - 10:08 am UTC
I can use a sub query instead of a function, but that will make the whole sql too complex. And the function is called more than once, in several sql statements. It's a hierarchical query using an argument as part of the "start with" clause, and I don't know how to implement it as a view other than a function. Another reason to use a function is that I can capture all the exceptions in the function and return a default value, and I don't know how to do that in a sub query. How much slower it could be to use a function instead of a subquery ?
Can you tell me which of the following is better? (suppose table B and table A has a one-to-many relationship and table A has a foreign key constraint on column b_id )
select A.*,B.colname
from table_a A,table_b B
where A.b_id = B.id;
select A.*,
(select B.colname
from table_b B
where B.id=A.b_id
)
from table_a A;
Thank you so much for your help.
February 09, 2004 - 10:28 am UTC
"views" - use a view to hide the "complex sql". it could well be the difference between
a) the query returning in a reasonable amount of time
b) the query never seeming to return at all
the two queries are not "the same"
select A.*,B.colname
from table_a A,table_b B
where A.b_id(+) = B.id;
and
select A.*,
(select B.colname
from table_b B
where B.id=A.b_id
)
from table_a A;
are if A.B_ID is nullable (if A.B_ID is not null -- then your two queries are the same).....
the first approach is the standard one.
a view based on hierarchical query
James Su, February 09, 2004 - 10:52 am UTC
hi Tom,
I thought of using a view to hide the complex sql. But, as I mentioned above,the function is a hierarchical query using an argument as part of the "start with" clause:
.......
SELECT CATL_TITLE
into v_sport
FROM CATEGORY,CATEGORTITLES
WHERE CTGR_PARENT=0 AND CTGR_ID = CATL_CTGR_ID
START WITH CTGR_ID=p_ctgr_id
CONNECT BY PRIOR CTGR_PARENT=CTGR_ID;
return v_sport;
.......
you see I use an argument "p_ctgr_id" in "start with".
How can I define a view to do that? Thank you.
February 09, 2004 - 10:58 am UTC
application contexts can be used for that - but if this query is going "into plsql code" (assuming that based on p_ctgr_id) what do you care if
a) the query is nastly complex
b) the query is simple
???
You can just use a parameterized cursor and "hide the nastiness" in a package spec somewhere (instead of a view)
create or replace package my_nasty_query
as
cursor c( p_ctgr_id in number ) is
select ..........;
end;
and then others would
for x in my_nasty_query.c( some_input )
loop
.....
hiding a complex sub sql
James Su, February 09, 2004 - 11:16 am UTC
I'm using this subquery in a view:
CREATE OR REPLACE VIEW VW_TRANSACTION AS
select TRANSACTION.*
,VW_ACCOUNT.*
,( SELECT CATL_TITLE
FROM CATEGORY,CATEGORTITLES
WHERE CTGR_PARENT=0 AND CTGR_ID = CATL_CTGR_ID
START WITH CTGR_ID=EVENTS.CTGR_ID
CONNECT BY PRIOR CTGR_PARENT=CTGR_ID
) as CTGRTITLE
from TRANSACTION
,VW_ACCOUNT
,EVENTS
,LINE
where TRANSACTION.ACC_ID = VW_ACCOUNT.ACC_ID
and TRANSACTION.LINE_ID = LINE.LINE_ID
and LINE.LINE_EVENT_ID = EVENTS.EVENT_ID ;
and I access to this view in my plsql:
select .......
from VW_TRANSACTION
where .......
group by CTGRTITLE;
^^^^^^^^^
I use this subquery in quite a few views, and I don't like to write it everytime. Is there any way to hide it?(other than a function)
February 09, 2004 - 12:07 pm UTC
no
Distributed transaction and Rollback segment
Sami, April 20, 2004 - 6:57 pm UTC
Dear Tom,
Thanks for your time and effort.
Version 8.1.7
The requirement is to update 2 databases using distributed transaction. Either both of them should be completed or none of them. We are updating the remote DB(in my case NewYork) using database link.
New York
=========
create or replace procedure p1
as
begin
set transaction use rollback segment BIG_RBS_NY;
for i in 1..10000
loop
update r1 set site=upper(site);
end loop;
end;
/
New Jersey
=========
create or replace procedure p1
as
begin
--set transaction use rollback segment BIG_RBS_NJ; <== middle of the
trasaction
for i in 1..10000
loop
update r1 set site=upper(site);
end loop;
end;
/
From New Jersey, I am invoking
New Jersey
=========
begin
p1@newyork;
p1;
end;
/
Assumption
1)update consumes lot of Rollback segment space on both sides
2)all or none. Both sites should be updated or none of them.
Question:
How do I specify the rollback segment for New Jersey database.
p1@newjersey did not go thru because of rollback segment error.
Any ideas how to get around this?
Thanks in advance
April 21, 2004 - 12:10 pm UTC
you need to contact the dba in newjersey and tell them they have misconfigured their database. (eg: you cannot).
I am a big believer in "set transaction use rollback segment" should be outlawed. I really like AUM in 9i for this reason -- there is no such statement anymore!! (we have an init.ora to "ignore" it)
Need your help
Sami, April 21, 2004 - 3:29 pm UTC
Dear Tom,
A)
newjersey dba is asking us to use rollback segment "BIG_RBS_NJ" but we cannot set that rollback segment because we are in the middle of the transaction.
our process is like below [both (1) and (2) are in single transaction]
--------transaction starts--------
(1)call procedure at NY from NJ over dblink
1a)set big_RBS
1b)huge DMLs
(2)call procedure at NJ from NJ
2b)huge DMLs
--------transaction ends--------
B) until we upgrade to 9i we have to work around with 8i limitation.
April 21, 2004 - 9:12 pm UTC
see above, my response does not change.
NJdba needs to set up equi-sized rbs's.
OK
Raju, April 21, 2004 - 11:32 pm UTC
Dear Sir,
After a procedure or function has been created successfully
Can we rename them? like
SQL> alter procedure p rename to proc;
But it throws errors.Do you have any other way to do this?
Please do reply.
April 22, 2004 - 7:28 am UTC
No, you just drop the old and recreate the new.
Distributed database and optimizer modes, version 9.2.0.3.0
Raja, May 03, 2004 - 4:41 pm UTC
Hi Tom,
I don't know if this is the correct thread to ask this question. If not, please re-direct me to the more appropriate thread. Here's my question.....
We have a existing production database which uses RULE based optimizer. We are planning to move some tables and records within these tables to from the current database to another database(distributed/remote database) and will be doing a continuous archival of these records on a regular schedule based on certain date ranges.
However, we will be accessing the archived information quite often for dates which fall within the archived database range.
Now, the current database uses a RULE based optimizer (RBO), whereas, for the remote database, we are proposing that the COST base optimizer (CBO) be used.
I wanted to request your expert opinion on whether using RBO on one database and CBO on the other would evolve any potential issues/problems you could visualize and whether the 'Distributed Query Optimization' feature will be effectively used or would it even apply to this scenario at all.
Thanks in advance for your time and effort!
May 03, 2004 - 7:59 pm UTC
distributed query really relies on CBO and joining non-analyzed to analyzed tables is always a bad idea.
so, if you query both in a single query - that would be problematic.
ORA-164
A reader, May 26, 2004 - 10:53 am UTC
I have a simple update over the db link, and got 164 error.
How can I debug this?
Thanks.
SQL> update P2PA_FUDS_IN_STG_PRJ@p2_dev
2 set project_name = 'K06TX003408 CON/HTRW TEST'
3 where project_number = 102774;
update P2PA_FUDS_IN_STG_PRJ@p2_dev
*
ERROR at line 1:
ORA-00164: autonomous transaction disallowed within distributed transaction
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-04092: cannot COMMIT in a trigger
ORA-04088: error during execution of trigger 'APPS.P2PA_FUDS_IN_STG_PRJ_TRG'
ORA-02063: preceding 4 lines from P2_DEV
May 26, 2004 - 12:49 pm UTC
ORA-00164: autonomous transaction disallowed within distributed transaction
which indicates to me that that table has a trigger, trigger has an autonomous transaction in it and that is a hugely bad bad bad bad bad idea.
that trigger should not exist, I say that without even knowing what it does - I can make it misbehave and do things the coder did not ever anticipate.
Call remote procedure to initiate MV refresh
A reader, June 11, 2004 - 4:43 am UTC
Hi Tom
we have a process that populates various tables, which have Materialized View logs on them. Once we have finished our days processing, we want to refresh the Materialized views on a separate instance.
Can we call a procedure / package on the remote database, via a db link, to call the dbms_refresh.refresh procedure ?
Is this the best way to do this ?
Many thanks
June 11, 2004 - 3:49 pm UTC
there can be issues with calling a remote procedure that does a commit, so i would recommend that you call "dbms_job@remote_site" to schedule a refresh instead.
so, just:
create synonym remote_dbms_job for dbms_job@remote;
and call
remote_dbms_job.submit( l_job, 'dbms_mview.refresh( ... )', instance => remote_dbms_job.any_instance );
A reader, January 05, 2005 - 5:17 pm UTC
That the autonomous transaction *sometimes* and in very
specific cases worked in
816 was a bug -- it was a dangerous situation."
Could you please explain why it is dangerous?
Why can't I do an autonmous transaction over a distributed
transaction? Why is it disallowed?
TIA
----------------
How come a bug in prior release of Oracle 8i becomes a feature of 9i. that was discontinued in Oracle?
---
I have a very specific requirement where I have log table that need to be used to log the errors to a database whenever all the other changes are rolled back. I need to only commit the logs in a separate instance thru a db link
Could you please suggest a solution..
Thanks!
January 05, 2005 - 8:07 pm UTC
you will have to
a) rollback;
b) insert into table@remote;
c) commit;
is all. no need for an atrans here - you just said "when all else are rolled back" -- so you just need "a transaction"
A reader, January 06, 2005 - 10:32 am UTC
How will it work in case of triggers?
January 06, 2005 - 11:17 am UTC
what triggers?
A reader, January 06, 2005 - 12:01 pm UTC
I mean database triggers. Commit don't work in triggers. I will have to do it thru autonomous transaction. Is it changed. If so how can I accomplish it thru triggers. What I intend to do is to use the same procedure or a different one to capture errors from database triggers
Thanks for the help
January 06, 2005 - 12:41 pm UTC
umm, no - the caller should do this logging. The person that runs this process should do that.
you don't want to remotely log via a trigger, mostly because "you cannot", but even still -- you don't want to.
say I have a table T.
In table T there are two rows -- only two rows.
Tell me, what is the maximum number of times a row trigger will fire on that table?
ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 2, null );
1 row created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
2 before update on t for each row
3 begin
4 dbms_output.put_line
5 ( 'for key = ' || :new.x ||
6 ' we are changing y from "' ||
7 :old.y || '" to "' || :new.y || '"' );
8 end;
9 /
Trigger created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> pause
ops$tkyte@ORA9IR2> update t set y = 42 where x > 0;
for key = 1 we are changing y from "" to "42"
for key = 2 we are changing y from "" to "42"
for key = 1 we are changing y from "" to "42"
for key = 2 we are changing y from "5" to "42"
2 rows updated.
Hmm, if I were to have done something non-transactional in that trigger, i could be in a big heap of trouble. A-trans are generally "evil", if you are using them, we really need to explore "why", they have 100% fallen out of favor with me, I haven't really seen any appropriate uses of them.
(hint, during the pause, i ran:
ops$tkyte@ORA9IR2> update t set y = 5 where x = 2;
for key = 2 we are changing y from "" to "5"
1 row updated.
ops$tkyte@ORA9IR2> commit;
Commit complete.
in another session)
do NOT do things in triggers that are NOT transactional -- you'll get very well burnt over time if you do.
A reader, January 06, 2005 - 4:59 pm UTC
I am asking about, how I to do logging in a trigger, if there is an exception in the trigger itself.
For example
create or replace trigger my_trigger
after insert on my_table
for each row
...
BEGIN
rest of the processing...
..
exception when others
Log_error
..
end ;
How do I achieve it
Thanks!
January 06, 2005 - 7:23 pm UTC
read the above and ask "do I really want to" or do i want to
exception
when others then
raise_application_error( ...., ....... );
end;
to just FAIL the statement and let the caller, who is responsible after all for figuring out whether this transaction is just "dead" and must be rolled back or "survivable" and can continue, figure out what is best to do.
(i many times wish we didn't have triggers, or when others, or a-trans, or lots of stuff....)
A reader, January 07, 2005 - 10:14 am UTC
I am giving up. Your answers are really useless
January 07, 2005 - 10:19 am UTC
bravo, go for the triggers -- you've been told how to do this right, but hey -- there you go.
trigger should FAIL the statement
client or top level should LOG ERRORS resulting from failed statements.
doing things that are NON TRANSACTION in triggers is a provably *bad idea* -- that and what you are asking to do *just cannot happen*. Sorry I could not rewrite the Oracle Kernel to make you happy -- sometimes things work the way they work -- not the way "a reader" would like them to work.
This is like asking "how do I make a symbolic link on Windows" and getting mad when told "well, you don't really -- here is how to achieve the closest thing"
Hmmm
Tarry Singh, January 07, 2005 - 11:58 am UTC
'a reader', you just can't handle a bit of tech discoussion, now can you.
Nothing is useless, even your comments aren't useless. People will read them and NOT/WON'T follow them..heh
A reader, January 07, 2005 - 12:41 pm UTC
Given the situation. I would have gone for autonomous transaction with care, by commiting the logs that I need and rolling back the other. This is even a suggested method in metalink - I don't recall the document or thread and I have implemented it. I was bit curious when you said this is a bad idea..
Anyway thanks for the help
January 08, 2005 - 3:50 pm UTC
the a-trans is a ticking time bomb.
the a-trans in a trigger is pure evil (i hope you can sort of see why with the example above)
the trigger logging an error over a dblink is a bug itself, in the design. So what happens when the dblink fails, when the other site isn't up. Oh, gee, so sorry -- you cannot insert good data into this table, because the remote site is down right now -- no work for you.
There are so many reasons this is a bad idea (tm)
a) it quite simply is not a supported operation
b) dblinks in a trigger doing this make the entire system fragile
c) doing things that are not transactional in the trigger is a huge mistake
and so on.
The error logging quite simply doesn't belong here. People try to be really fancy and have 50 things happen by magical side effects from triggers and all they end up doing is shooting themselves in the foot.
So what happens when the "due care" you take to find some way to accidently make the atrans work -- stops working when patch X is applied, or when version B is installed, or some other piece of code is added that makes your a-trans just stop working in the current release?
one more clarification
raajesh, May 03, 2005 - 1:15 am UTC
how can i call a .sql file from a stored procedure..
May 03, 2005 - 8:48 am UTC
you cannot, sqlplus runs scripts -- plsql is not sqlplus. You'd need to write a program that read a file, parsed it, executed the sql therein.
You could write a plsql procedure do that that, but you should just use sqlplus at that point.
how do i call procedure from oracle database and execute it
Mamata, December 09, 2005 - 2:07 am UTC
i had created a procedure and stored it in database. but now when i run it it does not run..what do i do?
December 09, 2005 - 2:14 am UTC
one would typically demonstrate how one is attempting to run something in order to get an answer ;)
how about an EXAMPLE?
got error ORA-02041 when calling a packaged.
Sean, March 03, 2006 - 11:15 am UTC
I also got this error
ORA-02041: client database did not begin a transaction
I guess that it is related to calling procedure in another database, but I could not be able to repeat the error using simple code, which means that I don't know what cause this error.
Both database is 9207.
Thanks so much for your help.
DATABASE A and DATABASE B
Mark, March 09, 2006 - 8:50 am UTC
I'm a little fuzzy on this.
Database A is our live system.
Database B is our read-only reporting system.
We use Database B for reports that generally load the LIVE server down too much.
What I want to know is if I have an identical function on Database A and Database B:
FUNCTION GET_GROUPTOTALS(in_group_id in number)
returns number
is
v_return number;
begin
select sum(amount)
into v_return
from my_transactions
where group_id = in_group_id;
return v_return;
end;
/
I want to know which database the FUNCTION is getting it's totals from in these SQLs.
This one runs on DATABASE A entirely (no db link):
SELECT GET_GROUPTOTALS(g.group_id)
FROM my_groups g;
What about this one:
SELECT GET_GROUPTOTALS(g.group_id)
FROM my_groups@DATABASE_B g;
My guess is that the last statement runs the function on DATABASE A because internally it access's the local database (no db link), even though you are passing values from DATABASE_B via the db link.
Do I have this right in theory?
March 09, 2006 - 2:39 pm UTC
you would be running the local copy of the function, it would get the data from "g" and then run the local function.
dbms_job and db_link
Branka, March 15, 2006 - 6:49 pm UTC
I created job, that execute SP
In SP I query local and remote database.
I get error:
ORA-12012: error on auto execute of job 6
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from CDSP
ORA-06512: at "CDS01.COMPARE_CDS_AND_CDSP", line 218
ORA-06512: at "CDS01.COMPARE_CDS_AND_CDSP", line 70
ORA-06512: at line 1
When I logon and execute same job, I don't have problem.
Any suggestion?
Thanks
Branka
March 16, 2006 - 7:48 am UTC
you have a database link created without a username and password.
It therefore uses the password of the currently connected session to open the dblink.
In the job, there is no password for the current session - therefore there is no password that can be used to open the dblink to the remote site.
dbms_job and db_link
Branka, March 16, 2006 - 9:48 am UTC
In other words, If I want to use dbms_job, and I have db_link I have to create db_link like:
CREATE DATABASE LINK TEST_DB_LINK CONNECT TO TEST_USER IDENTIFIED BY test_password USING 'TEST_DATABASE'
Is it any way I can solve this problem without creating db_link with username and password?
DB group in this company insist on private db_link without username and password.
Thanks
March 16, 2006 - 2:32 pm UTC
you can use other types of dblinks but they require directories and such (and the storing of credentials).
dbms_job and db_link
Branka, March 16, 2006 - 3:05 pm UTC
I didn't understand this.
Did you say that I can use db_link created like :
CREATE DATABASE LINK test_link USING 'test_db'
and still schedule dbms_job to execute SP that have db_link.
March 16, 2006 - 3:12 pm UTC
not without storing the credentials somewhere, you are by definition "not there to supply them"
db_link and dbms_job
Branka, March 16, 2006 - 3:53 pm UTC
Where can I store the credentials? I use Oracle9i.
In DBMS_JOB I can execute only SP. That mean that I should store credentials somewhere in sp?
Is it correct?
March 17, 2006 - 4:38 pm UTC
In the simpliest form, you would have a dbline with the username/password.
You can also use a directory (LDAP) to store the credentials or a wallet. but, you'd have to get your DBA to "help you"
I'm confused
Steven Zhang, March 17, 2006 - 12:01 am UTC
Hi Tom,
You Said "In a distribute transaction -- one in which "DATABASE A" calls "DATABASE B", only "DATABASE A" may commit -- "
But here is an example,I can start a transaction at Database A, and then commit at Database B via a procedure.
--------------------------------------------------------
SQL> create database link sprrsteven connect to a identified by a using 'EASTPRD1';
Database link created.
SQL> insert into t values(1);
1 row created.
SQL> insert into t@sprrsteven values(2);
1 row created.
SQL> exec p@sprrsteven
PL/SQL procedure successfully completed.
What the procedure in remote db 'EASTPRD1' is like :
QL> create procedure p as
2 begin
3 insert into t values(1);
4 commit;
5 end;
6 /
Procedure created.
--------------------------------------------------
The procedure p is parsed and executed at remote site.And it issues a commit.So the distribute transaction is commited at remote site,not local site.
Is there any conficts with your explaination?
Thanks,
Steven
March 17, 2006 - 5:17 pm UTC
it is not supported. It can and will fail under various scenarios - I would not suggest doing it.
It is one of the reasons DDL over a dblink isn't supported (and autonomous transactions and other transaction control statements in fact)
$ oerr ora 2074
02074, 00000, "cannot %s in a distributed transaction"
// *Cause: A commit or rollback was attempted from session other than
// the parent of a distributed transaction.
// *Action: Only commit or rollback from the parent session.
Two-Phase Commit/Distributed Transactions
Mark, April 13, 2006 - 2:50 pm UTC
Hey Tom,
I have a question on two-phase commit/distributed transactions. From the Wikipedia article on Two-Phase Commit - </code>
http://en.wikipedia.org/wiki/Two_phase_commit <code>:
---
Disadvantages
The greatest disadvantage of the two phase commit protocol is the fact that it is a blocking protocol. A node will block while it is waiting for a message. This means that other processes competing for resource locks held by the blocked processes will have to wait for the locks to be released. A single node will continue to wait even if all other sites have failed. If the coordinator fails permanently, some cohorts will never resolve their transactions. This has the effect that resources are tied up forever. The algorithm can block indefinitely in two ways:
1. When the coordinator has sent commit to the cohorts, it will block until all cohorts have acknowledged. Thus, if a cohort is permanently down, the coordinator will block indefinitely.
2. When a cohort has sent an agreement message to the coordinator, it will block until a commit or rollback is received. If the coordinator is permanently down, the cohort will block indefinitely.
Another disadvantage is the protocol is conservative. It is biased to the abort case rather than the complete case. Also it cannot recover from cases where a node has failed in the commit stage (due to internal or network failures) after indicating that it is ready to commit. In this case, resources that committed prior to this failure cannot be rolled back.
A lot of database research has been done on ways to get most of the benefits of the two-phase-commit protocol without the costs.
---
(That last sentence is a bit vague, but I figure if anyone has found a way around the costs, Oracle has implemented it.)
Could you possibly comment on these disadvantages when using distributed transactions, and how to deal with them in Oracle (or how Oracle deals with them)? Does Oracle's multi-versioning play a role in limiting these blocks?
Thanks,
Mark
April 14, 2006 - 12:01 pm UTC
the resources will be locked - but because you have been MODIFYING them (they were in fact locked well before the commit started).
The obvious disadvantage of the 2pc is that - well - it is distributed, there is more than one player - more can go wrong. And yes, if something like the coordinator fails - the participants get sort of stuck (if they voted YES to commit and are awaiting the outcome of the vote). Here, Oracle has methods to manually force the distributed in doubt transactions.
And yes, we have some "faster than 2pc" ways to "2pc" in various environments - for example, our AQ code has optimizations when pushing queues from one machine to another. It is sort of a 2pc, but can take shortcuts since we know the number of participants and control the data entirely.
But in general, a 2pc for you will be a 2pc.
Thanks!!!
Mark, April 14, 2006 - 5:50 pm UTC
dbms_output does not send the text over the dblink
A reader, June 21, 2006 - 5:03 pm UTC
Tom,
Is this an expected behavior? Any work-around by which I can display the dbms_output over the dblink?
My database version is 9.2.0.7 Both databases are on Solaris 2.8.
In my local database:
CREATE OR REPLACE PROCEDURE call_me IS
BEGIN
dbms_output.put_line ('
Hello world!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END call_me;
Procedure created.
sql>
In my remote database:
SQL> set serverout on
SQL> exec vigat.call_me@db_link
PL/SQL procedure successfully completed.
SQL>
I do not see any output.
Thanks for your help
June 22, 2006 - 11:48 am UTC
that'll be writing to the dbmsoutput@db_link package. In order to "see it", you would have to
a) run dbms_output.enable@db_link (just to enable it at the remote site)
b) run your procedure (which populates the remote dbms_output package)
c) run dbms_output.getXXXX@db_link (two different apis to get the remote data) to retrieve the remote data and call dbms_output locally to "print it"
HINT:
when others then
raise;
is a
a) waste of keystrokes
b) a worst practice
c) a way to simply hide the line number that the error happened on
d) not useful
e) something to remove from your coding practices
Thanks
A reader, June 28, 2006 - 11:40 am UTC
Tom,
Thanks for the advice on not using RAISE.
Could you please explain what you mean by:
run dbms_output.getXXXX@db_link
What is XXXX here?
thanks.
June 28, 2006 - 4:41 pm UTC
ops$tkyte@ORA9IR2> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE NUMBER(38) IN DEFAULT<b>
PROCEDURE GET_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES TABLE OF VARCHAR2(255) OUT
NUMLINES NUMBER(38) IN/OUT</b>
PROCEDURE NEW_LINE
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A NUMBER IN
the "get" routines to retrieve the dbms_output buffer contents from the remote site.
Calling procedure over db_link
Santanu, February 26, 2008 - 3:56 pm UTC
Hi Tom,
I believe you surely have the answer of the following problem.
I need to call procedure proc2 in db2 from proc1 at db1 over database link. I am using local loopback for easy testing purpose. Using 10g database.
CREATE DATABASE LINK hq.acme.com@hq_1
connect hq_1 identified by pass_hq_1
USING 'string_to_hq_1';
I created synonym of proc2 with the db link in db1.
Proc2 procedure is working fine with input parameter as number and saving it in table.
But it is not working when input parameter is of either XML Type doc (dbms_xmlgen.getxmltype) or collection of object (table of database type).
Compilation errors for PACKAGE BODY
Error: PLS-00306: wrong number or types of arguments in call to 'proc2'.
Please let me know if oracle does not permit this operation or I am doing something wrong. In actual case it won¿t be loopback db link. It will be two different databases.
Best regards,
Santanu
February 26, 2008 - 4:06 pm UTC
the remote database doesn't know of your local types, you would need to use the remote types at best.
just because two types have the same "name" doesn't mean they are the same.
Calling procedure over db_link
Santanu, February 26, 2008 - 4:26 pm UTC
Not clear Sir. In the following case xmtype is general one.
proc1 in db1--> procedure proc1()
begin
v_xml :=dbms_xmlgen.getxmltype(ref cursor);
synonym_proc_2(v_xml);
end;
Proc2 in db2--> procedure proc2(p_xml xmltype) is
begin
dbms_xmlstore ...
end;
Is loopback db_link is creating problem?
Best Regards,
Santanu
February 27, 2008 - 2:23 am UTC
no, they are not the same
pretend it is not a loopback, because we do (pretend it is not)
there is the local type XMLType, there is the remote type XMLType and object types are not database link friendly.
Thank you
Santanu, February 27, 2008 - 8:29 am UTC
Thank you Tom.
Best regards,
Santanu
db link stored procedures out parameter
Raju, October 23, 2009 - 9:33 am UTC
Hi Tom,
this is a great thread on calling stored procedures through database links. But a question i have though is , when i am calling a stored procedure (with an out parameter) through database link, at the calling side, i always get a null value for the out parameter.
example :
database A:
declare
var1 number;
param1 number;
param2 number;
param3 number;
begin
proc1@link_B(param1,param2,param3,var1);
end;
where param1, param2,param3 are input parameters and var1 is an output parameter. i get null value for var1.
the database version is : 9.0.2
Please respond.
October 23, 2009 - 2:50 pm UTC
if what you say is true and you can verify that var1 should in fact have a value
that is, you can produce a cut and paste that looks just like this:
SQL> variable var1 number
SQL> connect user/pass@whatever_link_b_points_to
SQL> exec proc1( 1,2,3,:var1)
SQL> print :var1
SQL> connect user/pass@database_you_are_using_now
SQL> exec proc1@link_b( 1,2,3,:var1 );
SQL> print :var1
can you do that and cut and paste the results.
distributed transaction
A reader, March 31, 2013 - 3:39 pm UTC
Hi，Tom
I have a question about Oracle distributed transaction.
I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:
Can Oracle always guarrantee the data consistent in a distributed transaction?
For example,there is a distributed transaction on node a,node b and nod c.
node b and node c informed node a they were prepared,
so node a committed,and inform node b and node c commit.
then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?
If node c rollback ,the data on local node, consistent in this distributed transaction was failed ,yes?
Could you please give some explanation and tell if this statement is true?
Can Oracle always guarrantee the data consistent in a distributed transaction?
Thank you very much!
alan