Changing Password for an Oracle User in R12 Oracle Apps using API

This article helps in changing the password for Oracle user using API FND_USER_PKG.CHANGEPASSWORD.

This API is tested in R12.1.3

DECLARE
   v_user_name          VARCHAR2 (100) := 'TEST_USER';
   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status                   BOOLEAN              := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);

  COMMIT;
   DBMS_OUTPUT.put_line (   'Password is changed successfully for the user '
                         || v_user_name
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while setting new password to the user and the error is '
          || SQLERRM
         );
END;

Autonomous Transactions

The easiest way to understand autonomous transactions is to see them in action.

CREATE TABLE at_test (
      id               NUMBER            NOT NULL,
     description  VARCHAR2(50)  NOT NULL
   );

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.


Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.


As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. 

The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session.