Re: Host command

Chris,

The way you are changing your password may well work when everything goes ok but you have no way of returning useful error messages.

May I suggest you skip the HOST method and use Dynamic SQL on the server side to alter the password. I have supplied a procedure to do the job. If there are any errors, it will be passed back to your form via the OUT parameters.

The following should be a database procedure.

   PROCEDURE Change_User_Password(

   			 in_login_id     IN  VARCHAR2,
   			 in_password     IN  VARCHAR2,
	   		 out_error_code  OUT NUMBER,
			 out_error_msg   OUT VARCHAR2) IS

      v_sql_statement	VARCHAR2(200);

      v_cursor number;
      v_numrows number;

   BEGIN

      v_sql_statement := ' ALTER USER ' || in_login_id;
      v_sql_statement := v_sql_statement || ' IDENTIFIED BY ' ||

      --
      -- Create SQL statement to alter the users password
      --

      v_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_cursor, p_statement, DBMS_SQL.V7);
      v_numrows := DBMS_SQL.EXECUTE(v_cursor);
      DBMS_SQL.CLOSE_CURSOR(v_cursor);

      --
      -- Return OK status if no errors
      --

      out_error_code := 0;
      out_error_msg := 'Ok';

   EXCEPTION WHEN OTHERS THEN

      DBMS_SQL.CLOSE_CURSOR(v_cursor);
      out_error_code := SQLCODE;
      out_error_msg := SQLERRM;

   END Change_User_Password;

HTH, Mark

tcny2k_at_yahoo.com (tcny2k) wrote in message news:<3fa1b346.0204220945.406bb717_at_posting.google.com>...
> Hi,
> This is a piece of code that I use for updating the Oracle password
> using Oracle Forms.
>
> "......
> ID := :PASSWD.User_Name;
> NewPW := :PASSWD.New_PW;
>
> Command := 'plus80w '||id||'/'||CurPW||'_at_'||dest||' @changepw '||id
> ||' '||newpw ;
> HOST(Command,NO_SCREEN);
>
> IF FORM_SUCCESS THEN
> Message('Request Processed.');
> ELSE
> Message(TO_CHAR(Message_Code) || ':' || MsgTxt;
> RAISE Form_Trigger_Failure;
> END IF;
> ..."
>
> It works fine except for some cases like the password has been used
> previously, there is no way for me to identify the problem or recieve
> the error message. FORM_SUCCESS is still set to TRUE because nothing
> wrong with Command.
>
> Can someone help me with this?
>
> Thanks in advance.
> Chris
Received on Tue Apr 23 2002 - 09:53:05 CEST