Re: Creating a New User from PL/SQL

I didn't test it, but i think ddl statements will execute during parse. So bind variables will not work. Add the string together and then parse. This will work. Then you can select user_id from dba_users where... Hope this will help, Andreas Prusch

In article <35ACC669.4A64B50A_at_internetivity.com>,   Paul Dumais <pdumais_at_internetivity.com> wrote:
> 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;
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 16 1998 - 04:38:34 CDT