5 Replies Latest reply on Jan 14, 2014 4:21 PM by n_shah18

    Notify end user there account is going to expire



      I am trying to build a nightly job that will email end user that there account is going to expire in 10 days reset your password

      If they dont do so then I want to lock their account on 11th day. eventually purge there apex account in 30 days


      How do I head start on this?

      I know there is utility APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT but I am not able to access this outside apex env.


      set serveroutput on


          l_days_left NUMBER;


          FOR c1 IN (SELECT user_name from apex_workspace_apex_users) LOOP

              l_days_left := APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT(p_user_name => c1.user_name);

             -- htp.p('End User Account:'||c1.user_name||' will expire in '||l_days_left||' days.');


          END LOOP;



      It gives this errors

      ORA-06502: PL/SQL: numeric or value error

      ORA-06512: at "SYS.OWA_UTIL", line 356

      ORA-06512: at "SYS.HTP", line 1368

      ORA-06512: at "SYS.HTP", line 1443

      ORA-06512: at "SYS.HTP", line 1735

      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 324

      ORA-20987: Unauthorized access (security group package variable not set).

      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 743

      ORA-06512: at "APEX_040100.WWV_FLOW_ERROR", line 1064

      ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 24

      ORA-06512: at "APEX_040100.WWV_FLOW_FND_USER_API", line 1890

      ORA-06512: at "APEX_040100.HTMLDB_UTIL", line 1756

      ORA-06512: at line 5

      06502. 00000 -  "PL/SQL: numeric or value error%s"




      Can somebody guide me pls.


      Many thanks


        • 1. Re: Notify end user there account is going to expire
          Mike Kutz

          APEX_MAIL might give you some insights on how to setup an APEX session within DBMS_JOB/DBMS_SCHEDULER.


          Also, I recommend you use APEX_PLSQL_JOB with gobs of UPDATE_JOB_STATUS sprinkled throughout your code.

          This is just a wrapper for DBMS_JOB.  That means all of the tricks used for DBMS_JOB will work with APEX_PLSQL_JOB .. including NEXTDATE.


          You can than easily create an application to specifically to manage that job (start/stop/break/unbreak/etc.) and any other job you desire.

          Ah!... the fun of NOT using sql*plus for mundane tasks.



          1 person found this helpful
          • 3. Re: Notify end user there account is going to expire

            Thanks Mike and stewed


            I was able to send email to end user telling them to when there password is expiring by calling

            apex_util.set_security_group_id & APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT.


            Now the challenge is how do I Redirect them to Inbuilt apex password reset.

            For that I generate a new session within  PL/SQL code and pass the url in Email which look like this.



                   l_sess := APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID;

                  C1 user in the loop to whose password needs to be rested.


            URL that came in my email is like this



            well then I get error below.



            Contact your application administrator.


            Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum. If you are unsure what caused this error, please contact the application administrator for assistance.
            Return to application.


            Any idea what I am missing here.

            • 4. Re: Notify end user there account is going to expire

              My other work around try was to get user logged in first and then redirect it to the reset page



              sec_id number;

              l_sess number;


              select workspace_id into sec_id

                   from apex_applications

                      where application_id = :APP_ID  ;


                   apex_util.set_security_group_id(p_security_group_id => sec_id);   

                    l_sess := APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID;




              P_UNAME => :P102_USERNAME,

              P_PASSWORD => :P102_PASSWORD,

              P_SESSION_ID => l_sess,

              P_FLOW_PAGE => '4350'||':58'





              But this also did not worked gave error


              Application ID and current security group ID are not consistent.

              Contact your application administrator.

              Any other thoughts Guru's

              • 5. Re: Notify end user there account is going to expire

                step I used to make this work.

                1.Created a button on login page, when then click on it navigates to other page

                2. Placed instruction to next page in html and check box to acknowledge it.

                3.On acknowledgement dynamic action will fire and that will navigate user to this url 


                WWWWWWWWW is your workspace.


                4.USER will get an email with temp password to reset password.

                this way I will leverage the apex inbuilt reset mechanism .