Hi   TOM,  
  I   have   a   problem   with   ref   cursors.I'll   try   to   explain   it(sorry   if   my   english   is    
  not   very   good).  
  I   have   2   databases   and   i   want   to   return   values   from   one   DBto   the   other.  
  In   the   DB   that   i   want   to   recieve   the   data   i   have   the   call(with   a   procedure)   and      
  i   create   a   variable    
  wich   the   type   is   REF   CURSOR   from   the   second   DB.   In   example:  
  --the   variable    
  vResultCursor   user_DB2.pk_k1.vSqlCursorD@DB2;    
  --where   pk1   is   a   package   in   which   i   declare   the   REF   CURSOR   variable  
  ..  
  --The   call  
  user_DB2.pk_k1.P_1@DB2(vResultCursor);  
  --where   P1   is   the   procedure   in   wich   i   open   the   cursor   and    
  after   that   i   want   to   work   with   this   cursor  
   
  loop            
  --vx   is   varchar2              
                  FETCH   vResultCursor   INTO   vx;  
                        EXIT   WHEN   vResultCursor%NOTFOUND;  
                        insert   into   tbl_probe   values   (sysdate,'vx',vx);              
                      commit;                          
             
  end   loop;  
  close     vResultCursor;  
   
  In   the   first   DB   i   have   in   PK_K1   the   declaration   of   the   ref   cursor,   and   the    
  procedure   wich   open   the   dinamic  
    cursor:  
  CREATE     OR   REPLACE   PACKAGE   PK_K1   IS    
  TYPE   vSqlCursorD   IS   REF   CURSOR;  
  PROCEDURE   P_RESOLVECURSOR   (vSQLCURSOR   OUT   vSqlCursorD);  
  END   PK_K1;  
   
  CREATE   OR   REPLACE   PACKAGE   BODY   PK_K1   IS  
   
  PROCEDURE   P_RESOLVECURSOR   (vSQLCURSOR   OUT   vSqlCursorD)   IS  
  vSqlCursortxt   VARCHAR2(4096);  
  BEGIN  
  vSqlCursortxt:=   'SELECT   *   FROM   DUAL';  
  OPEN   vSQLCURSOR   FOR   vSqlCursortxt;  
  EXCEPTION  
                    WHEN   OTHERS   THEN  
                                IF   (vSQLCURSOR%ISOPEN)   THEN  
                                        CLOSE   vSQLCURSOR;  
                              END   IF;  
  END;    
  END   PK_K1;  
  The   problem   that   i   have   is,   that   if   i   make   a   procedure   in   the   package   PK_K1   and    
  i   call   the   procedure   P_RESOLVECURSOR  
  it   works,   but   when   i   call   from   the   other   DB   it   doesnt   work.   The   error   is   ERROR    
  ORA-01001   when   whe   make   the   FETCH  
  I   gave   the   EXECUTE   grant   from   one   DB   to   the   OTHER  
  GRANT   EXECUTE   ON   PK_K1   TO   USERDB1;  
  could   u   help   me?  
  Thanks    
     
   
   
  Followup:  
   
  ref   cursors   cannot   be   used   over   a   dblink   like   that.  
   
   
  http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1448  
   
  ....  
  Note:  
   
          *     Using   a   REF   CURSOR   variable   in   a   server-to-server   RPC   results   in   an    
  error.   However,   a   REF   CURSOR   variable   is   permitted   in   a   server-to-server   RPC   if    
  the   remote   database   is   a   non-Oracle   database   accessed   through   a   Procedural    
  Gateway.  
          *     LOB   parameters   are   not   permitted   in   a   server-to-server   RPC.  
   
  .....    
   
  Passing   a   cursor   from   one   DB   to   the   other     March   23,   2006  
  Reviewer:     Jorge     from   Spain  
   
  Thank   for   the   explanation,   we   solve   the   problem   opening   and   closing   the   cursor    
  in   one   DB   and   passig   the   data   to   the   other   server   in   an   TABLE   Object   by   means   of    
  a   function.  
  Thanks   a   lot     

  ==========================================================
看來這幾天的努力白費(fèi)了，氣憤啊