Re: Changing password via stored procedure

Just remember to close the cursor after parsing (and execute) the statement.

create or replace procedure sp_Change_Password (sUserID IN VARCHAR2,
sPassWord IN VARCHAR2)
AS
cur_handle INTEGER;
sSQL VARCHAR2(200) := '';

BEGIN
  cur_handle := DBMS_SQL.OPEN_CURSOR;
  sSQL := 'ALTER USER ' || sUserID || ' IDENTIFIED BY ' || sPassword ;   DBMS_SQL.PARSE(cur_handle, sSQL, DBMS_SQL.V7);   DBMS_SQL.CLOSE_CURSOR(cur_handle);
  exception when others
  DBMS_SQL.CLOSE_CURSOR(cur_handle);
END
/

Greetings
Tobias.

M. Bhatti wrote:

> Marc Rennhard wrote:
>
> > Hi there,
> >
> > I'm pretty new to ORACLE (7.3) and I'm writing my first stored
> > procedures in
> > PL/SQL. So just a question:
> >
> > How to change the password of a user via stored procedure?
> > (It should correspond to ALTER USER user IDENTIFIED BY pwd)
> >
> > Thanks in advance for any help,
> > Marc
>
> create or replace procedure sp_Change_Password
> (sUserID IN VARCHAR2,
> sPassWord IN VARCHAR2)
> AS
> cur_handle INTEGER;
> sSQL VARCHAR2(200) := '';
>
> BEGIN
> cur_handle := DBMS_SQL.OPEN_CURSOR;
> sSQL := 'ALTER USER ' || sUserID || ' IDENTIFIED BY ' || sPassword ;
> DBMS_SQL.PARSE(cur_handle, sSQL, DBMS_SQL.V7);
> END
> /
>
> That should get you started (note no exception handling, but you should
> add some add).
>
> mkb
Received on Thu Oct 22 1998 - 00:00:00 CDT