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 ----- Original Message ---- From: "Lawie, Duncan" <duncan.lawie@xxxxxxxxxxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx Sent: Monday, September 25, 2006 3:09:11 PM Subject: Calling a package over a database link 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 ==============================================================================