Re: Changing Logon Password

In <324AF09B.1C3D_at_erols.com>, Jim Schimmoeller <schim_at_erols.com> writes:
>is there a way to create a stored procedure or function that will allow
>users to change logon password.
>
>I need this for a powerbuilder application. PB can call stored
>procedures or functions but everything that I look at covers nothing
>reference to changing passwords and the ALTER statement errors everytime
>I put in a body of a Stored Procedure.
>
>Jim
>schim_at_erols.com

You cannot use an ALTER statement in a stored procedure directly. You will have to use package DBMS_SQL. For example:

Create or replace procedure Change_PW (UserID_In in varchar2,

                                       PW_In      in  varchar2) AS
  c1            integer;
  rc            integer;
  AltString     varchar2(80);

  BEGIN

        c1:=dbms_sql.open_cursor;
        AltString:='alter user '||UserID_in||' identified by '||Pw_in;
        dbms_sql.parse(c1, AltString, dbms_sql.v7);
        rc:=dbms_sql.execute(c1);
        dbms_sql.close_cursor(c1);

END Change_PW;
/

M.Landa Received on Fri Sep 27 1996 - 00:00:00 CEST