1 2 Previous Next 22 Replies Latest reply: Mar 7, 2013 5:01 AM by 995439 Go to original post RSS
      • 15. Re: Error with DBLINK
        Billy~Verreynne
        Jamie CC wrote:

        SQL> select * from dual@unanet_unanet;
        What about running it via a PL/SQL anonymous block, e.g.
        declare
          s varchar2(100);
        begin
          select dummy into s from dual@unanet_unanet;
        end;
        This will tell us if the problem is with access to the remote object PROJECT, or with using this db link in general in PL/SQL code.
        • 16. Re: Error with DBLINK
          user222828
          That works fine from PL/SQL Developer:
          declare
            s varchar2(100);
          begin
            select dummy into s from dual@unanet_unanet;
          end;
          
          Done in 0.313 seconds
          • 17. Re: Error with DBLINK
            user222828
            I have come across some forums that mention incompatibilities with the character sets and my old db and new db have different ones.
            So I am recreating and reloading the new db with the same character set as the old db and will see what that does.

            Anyone else have any insight as to what this could be please feel free to comment.

            Thanks
            • 18. Re: Error with DBLINK
              sb92075
              This will tell us if the problem is with access to the remote object PROJECT, or with using this db link in general in PL/SQL code.
              Anonymous PL/SQL behaves differently than NAMED PL/SQL procedures.
              • 19. Re: Error with DBLINK
                Billy~Verreynne
                sb92075 wrote:

                Anonymous PL/SQL behaves differently than NAMED PL/SQL procedures.
                The anonymous PL/SQL block test the OP did on the remote project table failed - so one step at a time in trying to isolate it.. PL/SQL seems to factor. And before looking at named procedures, the fact that it fails with anonymous code and not SQL (from the same session) with the exceptions it did, is rather strange.
                • 20. Re: Error with DBLINK
                  user222828
                  Well database recreated with same character set as old db and data reloaded and I am still seeing same error.
                  Works in the 9.2.0.4 db on Solaris but won't compile in the 9.2.0.8 db on HP-UX.

                  Still not sure why the dblinks needed to be recreated without the .world extension on the new db but not on the old one...

                  Both dbs have global_names set to false and db_domain isn't set.
                  • 21. Re: Error with DBLINK
                    user222828
                    Came across a note on metalink regarding this as being a bug in 9.2.0.8.
                    Downloaded and installed the appropriate patch and am still seeing the error.

                    Guess I can try to use 9.2.0.4 now instead of 9.2.0.8 and see if that resolves it.
                    • 22. Re: Error with DBLINK
                      995439
                      Hi Jamie,

                      I came across this problem today.
                      We are on a 9.2.0.8 db trying to reference remote objects on a 10.2.0.4 db from PL/SQL.
                      Our DB link works with plain SQL.
                      But is does not work within PL/SQL code (anonymous nor named PL/SQL code) - we are getting the same error codes as you describe.

                      Did you ever find a solution on the problem ?
                      I you remember the metalink that you mention, pls post it.

                      Best regards
                      Carsten
                      1 2 Previous Next