| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Calling a package over a database link
Duncan
Did you get errors 2 and 3 the right way round?
Version 3 is 'obviously' wrong - as it refers to database link @remote_database which you haven't got; this seems to be the message you've given as #2. PL/SQL can't dereference a variable to be a DB link name (just the same as it can't dereference a variable to be a table name, except by building it into the dynamic SQL string).
Version 2 looks identical to version 1 (except for the use of dynamic SQL). I'm guessing that PL/SQL buggers up the (valid) SQL in some way. I can't try it here right now - with luck someone else will solve that for you ...
Cheers
Nigel
www.preferisco.blogspot.com
List,
I have a piece of code which works fine if I hard code my remote database name, but which fails badly if I try to make the remote database a variable. Is there a variation on this concept which works, or do I need to find another approach altogether.
Example:
declare
remote_database varchar2(100);
v_sql varchar2(1000);
begin
remote_database := 'FRED';
v_sql := 'manage_remote_partitions.grant_tablespace_access@' || remote_database ||' ( ''DCL_ARC'', ''CCDATA'' )';
-- 1
manage_remote_partitions.grant_tablespace_access_at_FRED('DCL_ARC', 'CCDATA' );
-- 2
execute immediate v_sql;
--3
manage_remote_partitions.grant_tablespace_access_at_remote_database('DCL_ARC', 'CCDATA' );
end;
/
Version 1 works.
Version 2 gives me
ERROR at line 14:
ORA-06550: line 14, column 1: PLS-00352: Unable to access another database 'REMOTE_DATABASE' ORA-06550: line 14, column 1: PLS-00201: identifier 'OGB_MANAGE_REMOTE_PARTITIONS_at_REMOTE_DATABASE' must be declared ORA-06550: line 14, column 1:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
 |
 |