8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Running Commands in Different Schemas and Pluggable Databases

Certain tasks, like creating private database links, require commands to be run as a specific schema owner. This article explains how to run commands in a different schema using the DBMS_SCHEDULER and DBMS_SQL packages. You may also want to consider using a Proxy User.

DBMS_SCHEDULER Approach
DBMS_SQL Approach

DBMS_SCHEDULER Approach

The DBMS_SCHEDULER package can be used to run jobs as different users. In the example below we use the CREATE_JOB procedure to create a job owned by the SCOTT schema to drop a database link called MY_LINK.

DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
BEGIN
  DBMS_SCHEDULER.create_job(
    job_name   => l_user || '.' || DBMS_SCHEDULER.generate_job_name,
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN
                     EXECUTE IMMEDIATE ''DROP DATABASE LINK ' || l_db_link || ''';
                   END;',
    enabled    => TRUE,
    auto_drop  => TRUE
  );
END;
/

The parameters to pay attention to are as follows.

JOB_NAME : Set to a fully qualified name made up as a combination of the username and a random name generated by the GENERATE_JOB_NAME function.
JOB_ACTION : We then put the command that needs to run as the SCOTT user in a PL/SQL block. In this case we use an EXECUTE IMMEDIATE to perform the DDL statement.
ENABLED : We want the job to run.
AUTO_DROP : The job will be dropped immediately on completion. This saves us clearing up lots of one-off jobs.

DBMS_SQL Approach

The PARSE procedure of DBMS_SQL package can be used to run commands as other users. In the example below we use the Certain tasks, like creating private database links procedure to create a job owned by the SCOTT schema to drop a database link called MY_LINK.

DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
  
  l_user_id  NUMBER;
  l_cursor   PLS_INTEGER;
BEGIN
  SELECT user_id
  INTO   l_user_id
  FROM   all_users
  WHERE  username = l_user;

  l_cursor := sys.DBMS_SQL.open_cursor(security_level => 2);

  sys.DBMS_SQL.parse(c             => l_cursor,
                     statement     => 'DROP DATABASE LINK ' || l_db_link,
                     language_flag => sys.DBMS_SQL.native,
                     schema        => l_user_id);
                     
  sys.DBMS_SQL.close_cursor(c => l_cursor);
END;
/

The parameters to pay attention to are as follows.

STATEMENT : We then put the command that needs to run as the SCOTT user in a PL/SQL block.
SCHEMA : The name of the user we want to run this command as.

The PARSE procedure can also accepts a CONTAINER parameter to allow a statement to run in the specified container.

DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
  
  l_user_id  NUMBER;
  l_cursor   PLS_INTEGER;
BEGIN
  SELECT user_id
  INTO   l_user_id
  FROM   all_users
  WHERE  username = l_user;

  l_cursor := sys.DBMS_SQL.open_cursor(security_level => 2);
  
  sys.DBMS_SQL.parse(c             => l_cursor,
                     statement     => 'DROP DATABASE LINK ' || l_db_link,
                     language_flag => sys.DBMS_SQL.native,
                     schema        => l_user_id,
                     container     => 'PDB1');
                     
  sys.DBMS_SQL.close_cursor(c => l_cursor);
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.