Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00604: error occurred at recursive SQL when calling proc via db_link

698658Aug 10 2011 — edited Aug 11 2011
Hi,
I'm on 9.2.0.8 and got strange issue with simple test case
on source db:
CREATE OR REPLACE PROCEDURE ADMIN.gg_ref(out_tokens OUT SYS_REFCURSOR) is
  BEGIN
  OPEN out_tokens for select dummy from dual;
END ;
/


Now testing code localy:
SQL> var r refcursor
SQL> declare
  2   output sys_refcursor;
  3  begin
  4   adminx.gg_ref(output);
  5  :r:=output;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print r

D
-
X


So its working.

I've got db_link to that db , and now call that proc via dblink from other 9.2.0.8 DB:


var r refcursor
  1  declare
  2   output sys_refcursor;
  3  begin
  4   admin.gg_ref@LINK_NAME(output);
  5  :r:=output;
  6* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
Whats wrong with my code ?
Are there any restriction I'm not aware of ?
Regards
GregG
This post has been answered by Billy Verreynne on Aug 10 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 8 2011
Added on Aug 10 2011
14 comments
1,505 views