PL/SQL Documentation user

This documentation contains info on how to manipulate scheduler users with PL/SQL.
All user procedures and functions are located in the SO_USER package.

Back to top 

Oracle users

Only existing oracle database users can be registered as scheduler users.
If an oracle user does not exist a new one can be created using the SQL*Plus script schuser.sql.
A user created with this script has minimum oracle privileges; just enough for making a connection.
In the next example a new oracle user BACKOFFICE with password BACK2007 is created.

SQL> @schuser.sql

Create an oracle account with enough privileges
to become a plsql scheduler user.
Script must be run as sysdba.

Username                   : backoffice
Password                   :
Reuse existing account [Y] :
Default tablespace [USERS] :

> create user backoffice identified by back2007;
> alter user backoffice default tablespace users;
> alter user backoffice quota unlimited on users;
> grant create session to backoffice;
> grant create procedure to backoffice;

PL/SQL procedure successfully completed.

SQL> 
  

The oracle user can now be registered as a new scheduler user.

Back to top 

Register new scheduler users

A scheduler user can be registered with the procedure SO_USER.REGISTER_USER.

procedure register_user(
    p_user_name in varchar2);
  
Parameter Description
p_user_name The name of the scheduler user.
The name is not case sensitive.
Error Description
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10029
ORACLE USER "%1" DOES NOT EXIST
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES

Example 1

In this example a new scheduler user PLSQL7 is registered.
After registration the new user can only log on and submit public scheduler scripts.

exec so_client.register_user(p_user_name => 'PLSQL7');
  
Back to top 

Delete scheduler users

Scheduler users are deleted with the procedure SO_USER.DELETE_USER.

procedure delete_user(
    p_user_name in varchar2);
  
Parameter Description
p_user_name The name of the scheduler user.
The name is not case sensitive.
Error Description
OSC-10020
SCHEDULER USER "%1" HAS READ ACCESS
OSC-10030
SCHEDULER USER "%1" HAS NO ADMINISTRATOR PRIVILEGES
OSC-10031
SCHEDULER INSTALLATION USER "%1" CANNOT BE DELETED
OSC-10032
SCHEDULER USER "%1" CANNOT BE DELETED; ONE OR MORE SCRIPTS EXIST
Back to top