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?
Best Answer
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