Re: problem creating database link to self....

On 1 Jul 2003 13:07:32 -0700, techguy_chicago_at_yahoo.com (Bomb Diggy) wrote:

>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?

The answer is as usual (and it has been provided over and over and over and over and over again)
that roles are ignored during compilation of stored procedures and functions. Please setup the function as authid current_user (look up the exact syntax in your pl/sql manual)

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jul 01 2003 - 15:46:36 CDT