SEO

Hi,

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

-----------------------------------------------------
1 declare a varchar(100);
2 begin
3 a := f_test@dblink;
4* end;
SQL> /
a := f_test@dblink;
*
ERROR at line 3:
ORA-06550: line 3, column 6:
PLS-00201: identifier 'F_TEST@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@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@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@DBLINK; (is this valid syntax?)

With that, what are the other things I need to be looking for?
Permissions?

declare a varchar(100);
begin
select f_test@dblink into a from dual;
end;


On 1 Jul 2003 13:07:32 -0700, techguy_chicago@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@dblink;
> 4* end;
>SQL> /
>a := f_test@dblink;
> *
>ERROR at line 3:
>ORA-06550: line 3, column 6:
>PLS-00201: identifier 'F_TEST@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@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@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@DBLINK; (is this valid syntax?)
>
>With that, what are the other things I need to be looking for?
>Permissions?

........
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email

On 1 Jul 2003 13:07:32 -0700, techguy_chicago@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@dblink;
> 4* end;
>SQL> /
>a := f_test@dblink;
> *
>ERROR at line 3:
>ORA-06550: line 3, column 6:
>PLS-00201: identifier 'F_TEST@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@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@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@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