On Jun 9, 8:15 am, David <dav.da...@gmail.com> wrote: > Hello > I have 2 databases: dbMain and dbFront. > > In dbFront I have a user (userFront) that have only privilege for > execute a dblink to dbMain (linkToMain). This link access to dbMain > through a dbMain user (userMain). > > In dbMain, userMain have permision to execute a procedure (mainProc). > This procedure returns a CURSOR. > > I have a java program that connects to a database and get the data > from de cursor. > If the program connects to dbMain using userMain and execute the > procedure there's no errors. > But if the program connects to dbFront using userFront and execute the > procedure (mainProc@linkToMain) I get the error ORA-01001 (invalid > cursor). > > The procedure is defined like this: > CREATE OR REPLACE PROCEDURE mainProc (ret IN OUT gDecTypes.cursorType) > AS > BEGIN > OPEN ret FOR > SELECT * FROM mainTable; > END mainProc; > > and the dblink: > CREATE DATABASE LINK linkToMain > CONNECT TO userMain > IDENTIFIED BY <PWD> > USING 'dbMain'; > > I have grant privileges to userMain for executing mainProc and for > gDecTypes: > GRANT ALL ON gDecTypes TO userMain; > GRANT ALL ON mainProc TO userMain; > > I don't know the cause of that error. Any idea? > Thanks
David, You cannot use REFCURSOR on database link. You can create a Package/Procedure on dbFront to do it. And then call it from Java. Read: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10369303150185 Thomas -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
