1 2 Previous Next 19 Replies Latest reply: Jan 30, 2009 10:56 AM by Charles A RSS

    Javascript Alert in APEX

    Charles A
      I need to create an alert based on if a fields data exists in database when the user enters text in the text field. Has anyone ever created an alert message if the value entered exists in database already. I only see Javascript examples to prompt the user what data was entered or to disable a property based on a value = '80' or any value. But I need every value checked.

      <code>
      var lCheck = html_SelectedValue('P2_DEPARTMENT_ID')!='80;
      <code>

      I can check any specific value if its harded code but, I need to check every value if the user types in the text field then tabs off the field and if that value exists in the database then the alert message pops up and lets the user know this value entered exists in database. I can not hard code hundreds of values based on the example above. I hope someone can help me with this matter, thanks ahead of time.
        • 1. Re: Javascript Alert in APEX
          dmcghan
          Hello,

          What is your name?

          What your describing is typically implemented with an ApEx validation which is run after the page is submitted. If you need something that does the check on the fly then you need to use Ajax. Check out Denes's example here to get an idea of what you'll need to do then ask questions if you have any.

          http://htmldb.oracle.com/pls/otn/f?p=31517:217

          Regards,
          Dan

          http://danielmcghan.us
          http://sourceforge.net/projects/tapigen
          • 2. Re: Javascript Alert in APEX
            Charles A
            My name is Charles. This is my first post so I have not changed my User Id. I will today and thanks for responding. That example is great but, it checks values entered. I need to check the values in the database already. I have a validation already that will not allow a duplicate field, its a Social Security Number. But I also need an alert for if the value exists in the database when the user types in the text field to display an alert message that the value exists in database. The example only checks the format which is great but, not for what I need at the moment. I can not hard code every Social Security Number in Java in the HTML Header for my Page, not feasible.
            • 3. Re: Javascript Alert in APEX
              dmcghan
              Charles,

              No, you would not want to even attempt to "hard code" the entries. Denes's example was only supposed to get you thinking about Ajax because that's what you need. The difference is your actual check. Notice that he has an application process that is called validate_number. You will create one called validate_social.

              His process didn't need to do a DB call, but yours will. All he did was make it so that if a value was returned from the process, the message was displayed. Take a closer look at his implementation and try to imagine how you could adapt it for your use.

              Regards,
              Dan

              http://danielmcghan.us
              http://sourceforge.net/projects/tapigen
              • 4. Re: Javascript Alert in APEX
                Charles A
                I definitely appreciate your insight but, I can not find any sample code that checks a database value using Ajax or JavaScript for APEX. I have alot of samples also the one you provided but there all for entered values in text fields. I need to know what syntax is used to check entered values already stored in a table then pop an alert message. Have been researching this for one week and no sample code.
                • 5. Re: Javascript Alert in APEX
                  dmcghan
                  Charles,

                  I see. The following is a sample of what you might create for your process (may be syntax errors, this is just an example):
                  DECLARE
                  
                     l_ss_exists BOOLEAN;
                     
                     CURSOR ss_check_cur
                     IS
                        SELECT *
                        FROM table
                        WHERE social_security_number = wwv_flow.g_x01;
                  
                     l_ss_check_rec SS_CHECK_CUR%ROWTYPE;      
                  
                  BEGIN
                  
                     OPEN ss_check_cur;
                     FETCH ss_check_cur INTO l_ss_check_rec;
                     l_ss_exists := ss_check_cur%FOUND;
                     CLOSE ss_check_cur;
                     
                     IF l_ss_exists
                     THEN
                        htp.p('This social security number already exists in the system');
                     END IF;
                     
                  EXCEPTION
                  
                     WHEN OTHERS
                     THEN
                        htp.p(SQLERRM);
                  
                  END;
                  Notice the reference to wwv_flow.g_x01. Denes references T_VALUE after he sets that application item with the line:
                  get.add('T_VALUE',pThis.value);
                  To set the value of wwv_flow.g_x01, you would need to use this line:
                  get.addParam('x01',pThis.value);
                  This approach allows you to run the code without creating a global variable in the application.

                  Regards,
                  Dan
                  • 6. Re: Javascript Alert in APEX
                    Charles A
                    Hey Don, I see that this is a PL/SQL Process. I do not understand how this will throw an alert to the user when they tab off the SSN text field. I am able to validate alot of fields with Javascript but, I have not located my match for the alert popup based on the data in the table. If you have any more knowledge on this matter please let me know, thanks again for your time also.

                    Edited by: user10864615 on Jan 29, 2009 12:47 PM
                    • 7. Re: Javascript Alert in APEX
                      M Tajuddin
                      Hello Chris,
                      you can create a item level validation to accomplish your task. Follow the following instruction to do that -

                      go to EDIT page >> click on image to create a validation under validation section >>Select item level validation then next >>select the ITEM you want to validate >> select SQL then next >> select EXISTS then next >> in validation name type RECORD CHECK then next >>type the following SQL statement in the validation field -
                      select field_1 from table_name where field_1=:Px_ITEM_NAME
                      Change the attributes according to column name and item name in your page.

                      Type the following error message in the ERROR MESSAGE section -
                      Record exist please try different number
                      When user click on the submit button it will check for any existing records in the database and throw error message if any records exist.


                      Hope this helps,

                      Regards,

                      M Tajuddin
                      web: http://tajuddin.whitepagesbd.com
                      www.chittagong-it.com
                      • 8. Re: Javascript Alert in APEX
                        Charles A
                        Thanks for the reply. I have that standard validation but, the user wants an alert box. The standard validation error message does not pop up an alert box. So, this has to have an AJAX/JavaScript function.
                        <code>
                        var lCheck = html_SelectValue('P2....')!='80';
                        html_disableItem('P2....'),lCheck);
                        <code>
                        pass --- onchange="commission_Allowed()" ---in the HTML Form Element Attributes for that 'P2....' field.
                        .... or I can pop an alert box but, I do not want to hard code values 80, 90, 100, 110......in my Javascript Function. I need some code to check the data for a field in a table. If any of these values exsit once the user tabs off the text field then an alert box pops up because the value exists in table. Your example is great using SQL when a button is clicked and I have that validation implemented but, I have to go one step further with the alert box popup. I hope you can assist me with this issue, thanks.
                        • 9. Re: Javascript Alert in APEX
                          dmcghan
                          Charles,

                          If you post an application on apex.oracle.com with a mock up of your page I'd be happy to help you there.

                          Regards,
                          Dan

                          http://danielmcghan.us
                          http://sourceforge.net/projects/tapigen
                          • 10. Re: Javascript Alert in APEX
                            Charles A
                            Thanks Dan, I have just signed up for an account and I am waiting for an email so I can log into my account. Could you give me a little information on this process? Like how will you be able to view my page etc... I will have to give you my log in etc... or just the workspace name?
                            • 11. Re: Javascript Alert in APEX
                              dmcghan
                              Charles,

                              When you login you'll need to create another developer account with a generic username/password. You can show those credentials here and lock the account when the question is answered. I will also need the workspace name.

                              Regards,
                              Dan

                              http://danielmcghan.us
                              http://sourceforge.net/projects/tapigen
                              • 12. Re: Javascript Alert in APEX
                                Tyler-Oracle
                                Charles,

                                Please confirm that you are NOT storing SSNs in clear text. You need to encrypt or hash data that's as sensitive as this. Along those same lines, since you'll be sending SSNs to your App Server from your web browser, you need to run in SSL mode so they are not transmitted in the clear.

                                Tyler
                                • 13. Re: Javascript Alert in APEX
                                  Charles A
                                  Hey Tyler, the text entered for the SSN number is parsed with the 333-33-3333 for viewing purposes. There are only two users for now that are gonna use this application. So they can enter in 333333333 but, I separate the data with dashes - when they view the data. I am sure I will expand on this in the near future.
                                  • 14. Re: Javascript Alert in APEX
                                    Tyler-Oracle
                                    I'm not concerned about the formatting, I'm concerned that you are not encrypting highly sensitive data. There have been SOOOOOO many cases of identity theft that at this point it is not a responsible decision as a developer to store them in the clear. In fact, if you live in MA, NY, NV, or CA it's potentially illegal not to encrypt SSNs, and I'm sure we'll see many states follow with their own legislation.

                                    You don't want your organization to end up on this list:
                                    http://www.privacyrights.org/ar/ChronDataBreaches.htm

                                    You can use DBMS_CRYPT to do the encryption or hashing. If you need help on this, please ask.

                                    Tyler
                                    1 2 Previous Next