Dynamic Query Error in PLSQL

Question

I am trying to execute this procedure:

CREATE OR REPLACE PROCEDURE SP_DYNAMIC 
AS
  tbl_list VARCHAR2(2000);
  DBLINK   VARCHAR2(100);
  V_SQL    VARCHAR2(1000);
BEGIN
  DBLINK := 'SOME_LINK';
  V_SQL := 'SELECT table_name,table_owner FROM dba_tab_modifications@:DB_LINK';

  EXECUTE IMMEDIATE V_SQL USING DBLINK;

  COMMIT;

  Dbms_Output.PUT_LINE (TBL_LIST);
END;

But When I execute the stored procedure I get the error:

ORA-01729: database link name expected
ORA-06512: at "SYSTEM.SP_DYNAMIC"
ORA-06512: at line 2

Can somebody help me with what I am doing wrong here?

Boneist · Accepted Answer · 2015-07-22 09:56:28Z

2

The reason it doesn't work is because you can't use a bind variable as the dblink. You get the same error when running the following:

select * from dual@:dblink;

If you absolutely must use dynamic sql, you'd have to concatenate the dblink name into the statement - but you'll have to be aware that you're now open to SQL Injection:

V_SQL := 'SELECT table_name,table_owner FROM dba_tab_modifications@'||DB_LINK;

edited Jul 22, 2015 at 9:56

answered Jul 22, 2015 at 8:11

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Over a year ago

Fuether to this answer... you can use DMBS_ASSERT.QUALIFIED_SQL_NAME on the result of dba_tab_modifications@'||DB_LINK; " part of the string in order to minimise the sql injection vulnerability

Boneist Over a year ago

Thanks, @James - that's something I was unaware of.

Collectives™ on Stack Overflow

Dynamic Query Error in PLSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related