RE: Calling a package over a database link

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 25 Sep 2006 11:07:52 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9A8B@ALVMBXW05.prod.quest.corp>

I have successfully used your approach 2. (see example below). One problem is that your dynamic sql statement has to be a PL/SQL block if you are calling a procedure, but I wouldn't expect the error that you are getting. All I can do is tell you that it should work. In my example, P is a procedure created in the remote database.

SQL> select db_link from user_db_links ; DB_LINK

SDU3X10A.QUEST.COM
SDU4817A.QUEST.COM
SDU4APP.QUEST.COM SQL> execute p_at_sdu3x10a.quest.com
Procédure PL/SQL terminée avec succès.

SQL> declare
2 link_name constant varchar2 (128) := 'sdu3x10a.quest.com' ; 3 begin
4 execute immediate 'begin p@' || link_name || ' ; end ;' ; 5 end ;
Procédure PL/SQL terminée avec succès.

SQL>

De la part de Lawie, Duncan

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

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 25 2006 - 13:07:52 CDT