How to call a PL/SQL Procedure/Package over a Loopback Database Link?

Question

I'm able to call a PL/SQL procedure and package over a real database link fine, but cannot seem to do so over a loopback database link (a database link referring to a schema in the same database, used for testing purposes), in two different databases.

Is there a trick that is required in order to execute remote plsql packages/procedures/functions over a loopback database link?

Local database (foo):

CREATE DATABASE LINK MATTHEW@INST1 CONNECT TO bar IDENTIFIED BY password USING 'MATTHEW';
-- this works fine, as well as selecting from other tables
SELECT * FROM dual@MATTHEW@INST1;

Remote database (bar schema):

create package test_pkg
is
    PROCEDURE test_proc;
end;
/
create package body test_pkg
is
    procedure test_proc
    is
    begin
        dbms_output.put_line('hello');
    end;
end;
/

create procedure test_proc
is
begin
    dbms_output.put_line('hello');
end;
/


create or replace function ff return number
is
begin
    return 55;
end;
/

Local database (foo)

BEGIN
    test_proc@MATTHEW@INST1;
END;
/

Error report: ORA-06550: line 2, column 5: PLS-00201: identifier 'TEST_PROC@MATTHEW@INST1' must be declared

BEGIN
    test_pkg.test_proc@MATTHEW@INST1;
END;
/

Error report: ORA-06550: line 2, column 5: PLS-00201: identifier 'TEST_PKG.TEST_PROC@MATTHEW@INST1' must be declared

select ff@MATTHEW@INST1 from dual;

ORA-00904: "FF": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

Answer 1

Apparently, packages/procedures/functions created over a loopback database link need to be granted to the caller.

-- As the Bar schema
GRANT EXECUTE on test_proc TO foo;
GRANT EXECUTE on test_pkg TO foo;
GRANT EXECUTE on ff to foo;

Of course, this isn't necessary at all on a true database link across two databases, so why is it necessary here?

Looking at the documenation (Scroll down to "Global Name as a Loopback Database Link"):

You can use the global name of a database as a loopback database link without explicitly creating a database link. When the database link in a SQL statement matches the global name of the current database, the database link is effectively ignored. For example, assume the global name of a database is db1.example.com. You can run the following SQL statement on this database: SELECT * FROM [email protected]; In this case, the @db1.example.com portion of the SQL statement is effectively ignored.

So it appears that Oracle doesn't even use the loopback, thus explaining why grants are required.

Answer 2

Can we see a full top to bottom script? I can't reproduce that error on my db

SQL> create user foo identified by foo;

User created.

SQL> create user bar identified by bar;

User created.

SQL> grant create session, create procedure, create database link to foo;

Grant succeeded.

SQL> grant create session, create procedure, create database link to bar;

Grant succeeded.

SQL>
SQL> conn foo/foo
Connected.
SQL> create database link matthew@inst1 connect to bar identified by bar using 'db122';

Database link created.

SQL> select * from dual@matthew@inst1;

D
-
X

1 row selected.

SQL>
SQL> conn bar/bar
Connected.
SQL> create or replace
  2  procedure my_proc is
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

SQL> conn foo/foo
Connected.
SQL> BEGIN
  2      my_proc@MATTHEW@INST1;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>