1

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?

0

3 Answers 3

3

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;
0
1

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;
/
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.