Dynamic Procedure Execution

Question

Hi I have to execute a remote call ...

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO@DB_LINK()

... from a stored procedure.

As the value of DB_LINK is to be obtained during runtime. It should be executed dynamically. I tried to use

 EXECUTE IMMEDIATE ' DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO@'||DB_LINK||'()';

But I get the following error

ORA-00900: invalid SQL statement

Can anyone advise me on how to execute the Stored Procedure dynamically?

Can you try: EXECUTE IMMEDIATE ' DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO@'||DB_LINK — Giova, Jul 23 '15 at 09:09
well i am not sure if it will work but try do it like this '||DB_LINK() — Moudiz, Jul 23 '15 at 09:10
using '||DB_LINK(). It takes DB_LINK() as a function. And throws DB_LINK() function not found. — Saroj, Jul 23 '15 at 09:18
Your syntax seems to be correct: http://stackoverflow.com/q/240788/272735 Are you sure the value of `DB_LINK` is correct and the database link has privileges to execute `DBMS_STATS` package ? — user272735, Jul 23 '15 at 10:03

score 2 · Accepted Answer · answered Jul 24 '15 at 06:09

The clue lies in the precise wording of the error message: invalid SQL statement. As the PL/SQL manual says, EXECUTE IMMEDIATE is for executing dynamic SQL statements.

You are executing a procedure call i.e. PL/SQL not SQL. So you need to pass an anonymous block to EXECUTE IMMEDIATE:

EXECUTE IMMEDIATE 
    'begin  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO@'||DB_LINK||'() ;  end;' 
    ;

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO takes no parameters, so the empty brackets are optional. By all means include them if they make you feel happier.

score -1 · Answer 2 · answered Jul 23 '15 at 10:19

-1

EXECUTE IMMEDIATE 'DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO@'||DB_LINK();

should work if DB_LINK is a global function returning a VARCHAR2 string.

answered Jul 23 '15 at 10:19

doberkofler

9,511
18
74
126

DB_LINK appears to be a variable. The `()` belong to the call to FLUSH_DATABASE_MONITORING_INFO . – APC Jul 24 '15 at 06:37

Dynamic Procedure Execution

2 Answers2