Calling a package over a database link

From: Lawie, Duncan <duncan.lawie_at_credit-suisse.com>
Date: Mon, 25 Sep 2006 15:09:11 +0100
Message-ID: <07150B51CE30744CBBB58E696139708DFDDDCA@elon11p32002.csfp.co.uk>

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:

PL/SQL: Statement ignored

Version 3 gives me
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 11

TIA,
Duncan.

Duncan Lawie
DBE - Oracle
020 788 3 9229

"There will always be plenty of things to compute in the detailed affairs of millions of people doing complicated things." -- Vannevar Bush, As We May Think; Atlantic Monthly - July 1945.

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 25 2006 - 09:09:11 CDT