Oracle PL/SQL does not work -- bind variable not allowed

Question

Oracle 12.2 PL/SQL

When running:

declare
 s_sql varchar2(200);
begin
 s_sql := 'dbms_hs_passthrough.execute_immediate@mylink' || '(''exec sp_get_names'')';
 execute immediate 'begin :x; end;' using in s_sql;
end;
/

Fails with:

PLS-00110: bind variable 'X' not allowed in this context

The procedure runs fine as

begin
 dbms_hs_passthrough.execute_immediate@mylink('exec sp_get_names'); 
end;
/

But I want to keep it dynamic. So how (if at all) can the procedure dbms_hs_passthrough.execute_immediate@mylink() be executed in a dynamice fashion as in the first example? NOTE: It is not of any significance to understand what dbms_hs_passthrough.execute_immediate@mylink() does, just how to make it (or any other procedure that is called) dynamic. Any suggestions?

Answer 1

You can't pass a procedure as a bind variable.

You should be able to dynamically build the entire anonymous PL/SQL block and execute that. Something like this

declare
 l_plsql varchar2(1000);
begin
 l_plsql := 'begin 
 dbms_hs_passthrough.execute_immediate@mylink ... 
 end;';
 execute immediate l_plsql;
end;

Answer 2

You don't need to declare the return-code, you can even get it:

declare 
 s_sql varchar2(200);
 retcode nubmer;
begin
 s_sql := 'begin :retcode := dbms_hs_passthrough.execute_immediate@mylink(''exec sp_get_names'') end;';
 execute immediate s_sql using out retcode; 
end;
/

Answer 3

This worked (which is what I believe Justin Cave was referring to above):

declare 
s_sql varchar2(200);
begin
s_sql := 'declare retcode integer; begin retcode := dbms_hs_passthrough.execute_immediate@mydblink' || '(''exec sp_get_names''); end;';
execute immediate s_sql; 
end;
/

I was getting (misleading) errors at first (when trying Justin Cave's suggestion) because I forgot to handle the return code.