Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Pl/SQl procedure
A copy of this was sent to "Joet Singh" <jsingh_at_bigfoot.com>
(if that email address didn't require changing)
On Mon, 11 Oct 1999 21:48:06 -0500, you wrote:
>Anybody out there already have a procedure for changing user password?
>Where the user gives there old password and new password. I am using PL/SQL
>8.0 and the main problem I am having is phrasing "alter" statement with in
>the procedure. Any help is appreciated.
>
The procedure would be:
create or replace procedure change_pw( p_pw in varchar2 ) as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor,
'alter user ' || user || ' identified by ' || p_pw, dbms_sql.native );
the OWNER of the procedure must have been granted the "alter user" privelege DIRECTLY (see the URL in my signature and read "Why do I get an ORA-1031 or PL-201 with stored procedures" to find out why).
You do not need the OLD password as the user running the procedure is already logged into the database.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 12 1999 - 08:29:29 CDT