problem creating database link to self....

From: Bomb Diggy <techguy_chicago_at_yahoo.com>
Date: 1 Jul 2003 13:07:32 -0700
Message-ID: <94599bb3.0307011207.5b44cddb@posting.google.com>

Hi,

We are unable to call a stored function through a database link.

---

  1 declare a varchar(100);
  2 begin
  3 a := f_test_at_dblink;
  4* end;
SQL> /
a := f_test_at_dblink;

     *
ERROR at line 3:

ORA-06550: line 3, column 6:
PLS-00201: identifier 'F_TEST_at_DBLINK' must be declared
ORA-06550: line 3, column 1:

PL/SQL: Statement ignored

---

Unfortunately, I don't have all the details I want. Both databases are Oracle - mine is 9.2.0.1 - i'm guessing the other is too. No idea what the actual syntax was to create the link, but the users can do the following:

SQL> Select sysdate from dual_at_DBLINK;

I granted 'execute' on my test programs and on the package which houses the 'real' programs. I can log-in to the same database instance using their username and execute the following successfully:

SQL> select my_shema.my_package.f_test from dual;

I was able to create a link to my own database/schema and successfully execute a function.

SQL> CREATE DATABASE LINK DBLINK USING 'MY_SERVICE_NAME'; SQL> Select f_test_at_DBLINK from dual;

Also, the following syntax seems to work across the link, but I'm not sure. It does return a result - I just don't know if it's using the link or not:

SQL> Select f_test from dual_at_DBLINK; (is this valid syntax?)

With that, what are the other things I need to be looking for? Permissions? Received on Tue Jul 01 2003 - 15:07:32 CDT