hi all,

i have this program that needs to create a new user once the
administrator clicks on the 'create new user', could someone out there
help me.

thanx a lot in advance.
randy

Sent via Deja.com http://www.deja.com/
Before you buy.

dha...@my-deja.com skrev i artiklen <88bskt$i3...@nnrp1.deja.com>...

I would suggest you write a PL/SQL procedure that does the job, and calls
this procedure with parameters username , userpassword etc
The procedure could look something like this on Oracle 8.0 (not tested)!

create or replace procedure CreateUser(USERNAME IN varchar2, USERPWD IN
varchar2) as
  cid: integer;
  ddl: varchar2(2000);
begin
-- procedure creates a new user and sets default tablespaces and then
grants a role to the new user.
-- DDL statements are executed by the parse call, which performs the
implied commit

  ddl := 'create user '||USERNAME|| ' identified by '||USERPWD||
  ' DEFAULT TABLESPACE tablespace '||
  'TEMPORARY TABLESPACE temptablespace '||
  'QUOTA UNLIMITED ON tablespace';

  cid := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cid, ddl, DBMS_SQL.Native);

  ddl := 'GRANT userrole TO '||USERNAME;
  DBMS_SQL.PARSE(cursor_name, ddl, DBMS_SQL.Native);

  DBMS_SQL.CLOSE_CURSOR(cid);
end;

Peter Laursen