A Note About ORA-02041: client database did not begin a transaction...

[Scott]

I received this error message when I was running my
package.procedure to access a remote database
view -- via a database link -- on one database
to another. (I am using the Oracle OLEDB Provider: Provider=OraOLEDB.Oracle)
I was able to eliminate it by adding this line of code to
my procedure:

SET TRANSACTION READ ONLY;

before the SELECT statement in my procedure and add

COMMIT;

after the SELECT statement in my procecure. Hope this
helps someone out there!!

My final package/procedure (called SCOTTPAK.SQL) looks like this:

CREATE OR REPLACE PACKAGE SCOTTPAK AS

TYPE curREFCUR IS REF CURSOR;

PROCEDURE GetStuff (pkey IN NUMBER,
a_cursor OUT curREFCUR);

END SCOTTPAK;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY SCOTTPAK AS

PROCEDURE GetStuff (pkey IN NUMBER,
a_cursor OUT curREFCUR) IS

BEGIN

--ADD THIS LINE!!
SET TRANSACTION READ ONLY;

OPEN a_cursor FOR
SELECT COL1,COL2,COL3
FROM ops$admin.vwMyView@myDBLink
WHERE product_key=pkey;

--ADD THIS LINE!!
COMMIT;

END GetStuff;

END SCOTTPAK;
/
SHOW ERRORS

[Scott]

I received this error message when I was running my
package.procedure to access a remote database
view -- via a database link -- on one database
to another. (I am using the Oracle OLEDB Provider: Provider=OraOLEDB.Oracle)
I was able to eliminate it by adding this line of code to
my procedure:

SET TRANSACTION READ ONLY;

before the SELECT statement in my procedure and add

COMMIT;

after the SELECT statement in my procecure. Hope this
helps someone out there!!

My final package/procedure (called SCOTTPAK.SQL) looks like this:

CREATE OR REPLACE PACKAGE SCOTTPAK AS

TYPE curREFCUR IS REF CURSOR;

PROCEDURE GetStuff (pkey IN NUMBER,
a_cursor OUT curREFCUR);

END SCOTTPAK;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY SCOTTPAK AS

PROCEDURE GetStuff (pkey IN NUMBER,
a_cursor OUT curREFCUR) IS

BEGIN

--ADD THIS LINE!!
SET TRANSACTION READ ONLY;

OPEN a_cursor FOR
SELECT COL1,COL2,COL3
FROM ops$admin.vwMyView@myDBLink
WHERE product_key=pkey;

--ADD THIS LINE!!
COMMIT;

END GetStuff;

END SCOTTPAK;
/
SHOW ERRORS