Calling a package over a database link
- From: "Lawie, Duncan" <duncan.lawie@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 25 Sep 2006 15:09:11 +0100
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@FRED('DCL_ARC', 'CCDATA' );
-- 2
execute immediate v_sql;
--3
manage_remote_partitions.grant_tablespace_access@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@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
==============================================================================
Other related posts:
