3 Replies Latest reply: Sep 26, 2011 2:25 AM by 881741 RSS

    execute procedure over database link


      I''m working on a package where you can select the required database, paste some query and with clicking on a button it will execute and create an explain plan for this query.
      Therefore i've create a procedure on each database that put's the explain plan in the PLAN_TABLE.
      To do this i need to execute this prodecure from the package. I use the following code:


      This is working fine as this database link allready exists to the required database. But i want to make the database link name variable. I've allready created the dynamic database link:

      l_link varchar2(20) := 'DBA_LINK';
      l_link_nr number(4) := dbms_random.value(0,1000);
      l_db varchar2(20) := l_link||'_'||l_link_nr;
      l_statement varchar2(4000);


      l_statement := 'create database link '||l_db||' connect to **** identified by ***** using '''||p_database||'.WORLD''';
      execute immediate l_statement ;

      So now i have the dynamic databaselink that is created each time i call the package.
      now i want to use this databaselink to call the procedure. this is where it goes wrong.

      The code i'm using:
      is not acceoted when i build the package.

      The follwoing error message is showed.
      On line: 66
      PLS-00352: Unable to access another database 'L_DB'

      Does someone know how to use this variable to execute the procedure on the remote db?
        • 1. Re: execute procedure over database link
          Brian Bontrager
          Any procedures you call have to be accessible at compile time, so the database link must also exist at compile time.

          You can get around this by putting your procedure call to DB_PCD_EXPLAIN_PLAN inside dynamic SQL, the same as you are doing to create the database link. Dynamic SQL is not checked until runtime.

          Think about the risk if this procedure is exploited. You are creating a way for someone to run arbitrary code in an any database in your environment, with presumably a highly-privileged ID, if it can run an explain plan against any schema in any target database.

          This is exposing some significant security issues. Read up on SQL injection, and have someone else review the code for security issues before deploying this.
          • 2. Re: execute procedure over database link
            You'll need to do something like described in the documentation...


            e.g. use something like...
            DBMS_SQL.PARSE@link_name(crs, 'begin procedure_name(params etc); end;', v7);
            • 3. Re: execute procedure over database link
              Í've resolved this by creating a standard databaselink that will be used. In this way i don't have te create a link every time again.