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

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_at_myDBLink 
	   WHERE product_key=pkey;

   --ADD THIS LINE!!
   COMMIT;      END GetStuff;

END SCOTTPAK;
/

SHOW ERRORS Received on Fri Dec 20 2002 - 07:41:43 CST