Creating a New User from PL/SQL

From: Paul Dumais <pdumais_at_internetivity.com>
Date: Wed, 15 Jul 1998 15:12:04 GMT
Message-ID: <35ACC669.4A64B50A@internetivity.com>

Hi everyone,

I was wondering if I could get a little help with this PL/SQL Procedure.

I want to create a New User using a Function which takes as input a username and password and return the new user's id. Am I doing it the right way??? My main problem right now is getting the user# from sys.user$
Here is what I got so far:

CREATE FUNCTION CreateNewUser (username varchar2, password varchar2) RETURN NUMBER IS
userID NUMBER;
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name, 'CREATE USER :u IDENTIFIED BY :p',

                   dbms_sql.v7);

dbms_sql.bind_variable(cursor_name, ':u', username); dbms_sql.bind_variable(cursor_name, ':p', password);

rows_processed := dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name);

SELECT user# into userID FROM sys.user$ WHERE name=username;

RETURN userID;

END; Received on Wed Jul 15 1998 - 10:12:04 CDT