This discussion is archived
1 2 Previous Next 21 Replies Latest reply: May 10, 2012 8:09 AM by Haisa.M RSS

Procedure and Security Log

Haisa.M Newbie
Currently Being Moderated
Hello All,

I am stock with a problem that I don't know where I should start. I have a form page in apex that user logs in and select questions and he/she will answers the questions and then clicks the button to rest his/her e-business password, if the answers are correct the password will be changed and if not it gives him/her an error to try again. Until here everything works fine. Now what I want is, I want when a user tries 3 times and the answers are wrong, the user's name, the number of attempts and the date be inserted to another table. I have no idea how I can achieve this. Please help me on that.

Thanks,

Atousa
  • 1. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    a) Create an Hidden item on the page
    b) In the Onsubmit side increment the item by 1. Use NVL(:Pnn_ITEM) so that is does not fail the first time.
    c) Write a process that will insert into a table when the value of the Hidden item exceed your limit of failed attempts.

    Regards,
  • 2. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    Thanks for the reply. I am a little confuse here. in the b section, I need to increment the item in the process that I have now which I am using for resting the password?
  • 3. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    It should preferably be a separate process with a sequence number after the process the process where you attempt to reset the password.

    If the password is successfully reset you do not have to bother about the count. Count to be incremented only when reset attempt fails. I guess you already have branch on the page that reloads the page if the reset attempt fails?

    Regards,
  • 4. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    I have no branch in my page ! but I can create one but I don't know according to what I have to increment the hidden item.
  • 5. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    Well, you can increment the hidden item in the Exception section of the PL/SQL block where you perform the reset?

    Make the branch that loop backs conditional using the condition P1_HIDDEN_COUNTER(in Expression 1) and ITEM IN EXPRESSION 1 IS NOT NULL (in the Condition type)

    Regards,
  • 6. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    Sorry for asking a lot of questions. I created the branch with the type of item in Expression 1 is not null and I wrote P2_COUNT in Expression1 (which is my hidden column), now I don't know were I should put my increment code!I don't have any exception section. Here is my procedure:

    declare

    USER_NAME_1 VARCHAR2(30);

    begin

    SELECT USER_NAME into USER_NAME_1 FROM APPLMGR.FND_USER
    WHERE USER_ID=:P2_USER_NAME;


    APPS_APPLMGR.USER_PSWRD_RESET (USER_NAME_1, 'CUST', '12345', to_date('2','J') ,' 90' );

    end;
  • 7. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    You need to add the EXCEPTION in the PL/SQL block that will result in an error condition if the user provides incorrect data.

    Assuming the SELECT resulting in NO_DATA_FOUND is your error condition, your code should look like
    declare
    
    USER_NAME_1 VARCHAR2(30);
    
    begin
    
    SELECT USER_NAME into USER_NAME_1 FROM APPLMGR.FND_USER
    WHERE USER_ID=:P2_USER_NAME;
    
    APPS_APPLMGR.USER_PSWRD_RESET (USER_NAME_1, 'CUST', '12345', to_date('2','J') ,' 90' );
    
    EXCEPTION
      WHEN NO_DATA_FOUND THEN :P2_COUNT := NVL(:P2_COUNT ,0) +1;
      WHEN OTHERS THEN  :P2_COUNT := NVL(:P2_COUNT ,0) +1; -- Actually if you specify OTHERS then NO_DATA_FOUND is superfluous. Showing here for example sake
         
    end;
  • 8. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    I have a validation for the user if they provide incorrect data and the validation does not let them to reset unless the information is correct. My validation is as follow:

    SELECT 1 FROM PASSWORD_QUEST_RESPS WHERE
    upper(USER_NAME)=upper(:P2_USER_NAME)
    and upper(Q_NBR_1)=upper(:P2_QUEST_1)
    and upper(Q_NBR_2)=upper(:P2_QUEST_2)
    and upper(Q_NBR_3)=upper(:P2_QUEST_3)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_1,'AL32UTF8'))= upper(:P2_Q_RESP_1)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_2,'AL32UTF8'))= upper(:P2_Q_RESP_2)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_3,'AL32UTF8'))= upper(:P2_Q_RESP_3)

    I did follow your code but it did not work:(
  • 9. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    It does not increment.
  • 10. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    The NO_DATA_FOUND error is from which part! It is from validation or the process error message?

    The problem is there is no number in hidden item.

    Edited by: Atousa.M on May 7, 2012 3:06 PM
  • 11. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    Hi,
    I gave you a code snippet to show you where the EXCEPTION goes, using your own code that you had posted. The purpose was to show you where EXCEPTION goes.
    The process is not firing, and the counter not getting incremented , perhaps because the Validation fails before the process is fired.

    The NO_DATA_FOUND Exception is thrown by SELECT query when it fails to find a row that matches the given criteria.

    As you have a Validation it would be a better place to increment the count. However, you need to use PL/SQL block to set the value, so you will need to change your validation type to PL/SQL as Exists type does not allow you to specify bind variable assignments. Based on the validation code you have given, here is what would be the PL/SQL code for Validation
    DECLARE
      V_CNT PLS_INTEGER;
    
    BEGIN
    SELECT 1 INTO V_CNT    /* ADDED INTO */
    FROM PASSWORD_QUEST_RESPS WHERE
    upper(USER_NAME)=upper(:P2_USER_NAME)
    and upper(Q_NBR_1)=upper(:P2_QUEST_1)
    and upper(Q_NBR_2)=upper(:P2_QUEST_2)
    and upper(Q_NBR_3)=upper(:P2_QUEST_3)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_1,'AL32UTF8'))= upper(:P2_Q_RESP_1)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_2,'AL32UTF8'))= upper(:P2_Q_RESP_2)
    and upper(UTL_I18N.RAW_TO_CHAR(Q_RESP_3,'AL32UTF8'))= upper(:P2_Q_RESP_3);
    -- ABOVE COMMAND TERMINATED WITH SEMI COLON NOW.
    
    EXCEPTION
    
    WHEN OTHERS THEN :P2_COUNT := NVL(:P2_COUNT ,0) +1;
    
    END;
    You do not now have to increment the couter any other place, so remove the other code pieces to increment counter.

    Regards,
  • 12. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    For the PL/SQL there is no EXISTS type. The EXISTS type goes for SQL only. When i run it with Sql and type exists it gives me error:

    ORA-20001: Query must begin with SELECT or WITH

    I know that is because it should in pl/sql but in pl/sql I can not set it as exists.
  • 13. Re: Procedure and Security Log
    Prabodh Guru
    Currently Being Moderated
    That is right, EXISTS is SQL. You do not need to make the Validation condition if it is type PL/SQL. You handle the PASS/FAILED in the PL/SQL Block.

    You need to create validation of type PL/SQL.
    If you choose Returns Boolean type of PL/SQL Validation then you code will look as the modified code below. Note, you can also use Error Text type, where return null signifies TRUE and not null means FALSE (or FAILED).
    -- ABOVE COMMAND TERMINATED WITH SEMI COLON NOW.
       RETURN TRUE; -- TYPE BOOLEAN "PASSED"
    EXCEPTION
     
    WHEN OTHERS THEN :P2_COUNT := NVL(:P2_COUNT ,0) +1;
       RETURN FALSE; -- TYPE BOOLEAN "FAILED"
    END;
    Regards,

    Or, for Type Returns Error Text
    -- ABOVE COMMAND TERMINATED WITH SEMI COLON NOW.
       RETURN NULL; -- "PASSED"
    EXCEPTION
     
    WHEN OTHERS THEN :P2_COUNT := NVL(:P2_COUNT ,0) +1;
       RETURN 'Validate failed'; --  "FAILED" with whatever message you want to show
    END;
    Edited by: Prabodh on May 8, 2012 8:12 PM
  • 14. Re: Procedure and Security Log
    Haisa.M Newbie
    Currently Being Moderated
    Thank you :) It worked. Now I am trying to create an insert process to insert the user name and the count to the new table with this code:

    BEGIN
    INSERT INTO RTA.RTA_PASS_QUEST_LOG
    (USER_NAME, LOG_COUNT, DATE_CREATED)
    VALUES (:P2_USERNAME, :P2_LOG_COUNT, SYSDATE)
    WHERE :P2_LOG_COUNT>=3
    END;

    but it does not work:(
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points