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

Home » Articles » Misc » Here

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.

Related articles.

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.

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.

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.