1 2 Previous Next 22 Replies Latest reply: May 28, 2012 7:37 AM by Prabodh RSS

    How to store another value instead of username in application item

    Gurujothi
      Hello Everyone,
      I am using APEX4.1,
      In my application am using the custom authentication with the following package,

      ***Package specification***
      create or replace PACKAGE login_pkg
      AS
      PROCEDURE add_user 
      (
       p_username IN VARCHAR2
      ,p_password IN VARCHAR2
      );
      
      FUNCTION get_hash 
      (
       p_username IN VARCHAR2
      ,p_password IN VARCHAR2
      )
       RETURN VARCHAR2;
      
      FUNCTION customauthentication 
      (
       p_username IN VARCHAR2
      ,p_password IN VARCHAR2
      )
       RETURN BOOLEAN;
      
      END login_pkg;
      ***Package body***
      create or replace PACKAGE BODY login_pkg
      AS
      PROCEDURE add_user 
      (
       p_username IN VARCHAR2
      ,p_password IN VARCHAR2
      )
      AS
      BEGIN
      INSERT INTO app_users (username, PASSWORD)
          VALUES (UPPER (p_username),
              get_hash (TRIM (p_username), p_password));
      
      COMMIT;
      
      EXCEPTION
      WHEN OTHERS
      THEN 
       ROLLBACK; 
       RAISE;
      END add_user;
      
      FUNCTION get_hash (p_username IN VARCHAR2, p_password IN 
      
      VARCHAR2)
      RETURN VARCHAR2
      AS
      BEGIN
      RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
      input_string => UPPER (p_username) 
                      || '/' 
                      || UPPER (p_password));
      END get_hash;
      
      function CUSTOMAUTHENTICATION
      (p_username in VARCHAR2,
      p_password in VARCHAR2)
      return BOOLEAN
      is
      begin
       FOR c1 IN (SELECT 1 
                    FROM app_users
                  WHERE UPPER (username) = UPPER (p_username)
           AND PASSWORD = get_hash (p_username, p_password))
        LOOP
          APEX_UTIL.SET_SESSION_STATE('FUSER',upper(p_username));
          RETURN TRUE;
        END LOOP;
        APEX_UTIL.SET_SESSION_STATE('FUSER');
        RETURN FALSE;
       
      end CUSTOMAUTHENTICATION;
      
      END login_pkg;
      Here my APP_USERS table has 2 fields,
      username
      password
      I have created the 1 application item i.e FUSER,
      whenever the users logging in the username will be stored in the application item i.e FUSER,
      Here what I need is,
      I want to add one more (Emp_name)column with this APP_USERS table,
      so the table will be
      emp_name
      username
      password
      and the FUSER should hold the value of 'Emp_Name' instead of 'Username'

      I tried some ways but I got error,
      Can anyone suggest where to alter the above code.

      Thank you.
      Regards,
      gurujothi
        • 1. Re: How to store another value instead of username in application item
          VC
          How is this related to APEX?

          You are posting a SQL - PL/SQL question in APEX forum.

          Anyways, You did not give details about what error you are getting?
          • 2. Re: How to store another value instead of username in application item
            Prabodh
            function CUSTOMAUTHENTICATION
            (p_username in VARCHAR2,
            p_password in VARCHAR2)
            return BOOLEAN
            is
            begin
             FOR c1 IN (SELECT 1 
                          FROM app_users
                        WHERE UPPER (username) = UPPER (p_username)
                 AND PASSWORD = get_hash (p_username, p_password))
              LOOP
                APEX_UTIL.SET_SESSION_STATE('FUSER',upper(p_username));
                RETURN TRUE;
              END LOOP;
              APEX_UTIL.SET_SESSION_STATE('FUSER');
              RETURN FALSE;
             
            end CUSTOMAUTHENTICATION;
            a. You are using a cursor when you should be using a SELECT .. INTO
            b. APEX_UTIL.SET_SESSION_STATE('FUSER'); need another parameter and hence the error. It should be APEX_UTIL.SET_SESSION_STATE('FUSER','');
            c. Your code need to look like
            function CUSTOMAUTHENTICATION
            (p_username in VARCHAR2,
            p_password in VARCHAR2)
            return BOOLEAN
            is
            begin
            /*
             FOR c1 IN (SELECT 1 
                          FROM app_users
                        WHERE UPPER (username) = UPPER (p_username)
                 AND PASSWORD = get_hash (p_username, p_password))
              LOOP
                APEX_UTIL.SET_SESSION_STATE('FUSER',upper(p_username));
            */
               APEX_UTIL.SET_SESSION_STATE('FUSER','');
               SELECT EMP_NAME INTO :FUSER
                FROM app_users
                        WHERE UPPER (username) = UPPER (p_username)
                 AND PASSWORD = get_hash (p_username, p_password);
                RETURN TRUE;
              --END LOOP;
              --APEX_UTIL.SET_SESSION_STATE('FUSER');
            EXCEPTION
              WHEN NO_DATA_FOUND THEN   RETURN FALSE;
             
            end CUSTOMAUTHENTICATION;
            Invested time in your SQL and PL/SQL skills.

            Regards,
            • 3. Re: How to store another value instead of username in application item
              Gurujothi
              Hi VC,
              Sorry for posted here,
              I modified my package like the following,
              create or replace PACKAGE login_pkg
              AS
              PROCEDURE add_user 
              (
               p_emp_name IN VARCHAR2,
               p_username IN VARCHAR2
              ,p_password IN VARCHAR2
              );
              
              FUNCTION get_hash 
              (
               p_emp_name IN VARCHAR2,
               p_username IN VARCHAR2
              ,p_password IN VARCHAR2
              )
               RETURN VARCHAR2;
              
              FUNCTION customauthentication 
              (
              p_emp_name in VARCHAR2,
               p_username IN VARCHAR2
              ,p_password IN VARCHAR2
              )
               RETURN BOOLEAN;
              
              END login_pkg;
              
              /
              create or replace PACKAGE BODY login_pkg
              AS
              PROCEDURE add_user 
              (
               p_emp_name IN VARCHAR2,
               p_username IN VARCHAR2
              ,p_password IN VARCHAR2
              )
              AS
              BEGIN
              INSERT INTO app_user(emp_name,username, PASSWORD)
                  VALUES (UPPER (p_emp_name),UPPER (p_username),
                      get_hash (TRIM (p_username), p_password));
              
              COMMIT;
              
              EXCEPTION
              WHEN OTHERS
              THEN 
               ROLLBACK; 
               RAISE;
              END add_user;
              
              FUNCTION get_hash (p_emp_name IN  VARCHAR2 ,p_username IN VARCHAR2, p_password IN  VARCHAR2)
              RETURN VARCHAR2
              AS
              BEGIN
              RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
              input_string =>UPPER (p_emp_name) 
                              || '/' 
                              || UPPER (p_username) 
                              || '/' 
                              || UPPER (p_password));
              END get_hash;
              
              function CUSTOMAUTHENTICATION
              (p_emp_name in VARCHAR2,
              p_username in VARCHAR2,
              p_password in VARCHAR2)
              return BOOLEAN
              is
              begin
               FOR c1 IN (SELECT emp_name
                            FROM app_user
                          WHERE UPPER (username) = UPPER (p_username)
                   AND PASSWORD = get_hash (p_username, p_password))
                LOOP
                  APEX_UTIL.SET_SESSION_STATE('FUSER',upper(p_emp_name));
                  RETURN TRUE;
                END LOOP;
                APEX_UTIL.SET_SESSION_STATE('FUSER');
                RETURN FALSE;
               
              end CUSTOMAUTHENTICATION;
              end login_pkg;
              when I logged in, I got the error as
               PLS-00306: wrong number or types of arguments in call to  "CUSTOMAUTHENTICATION"
              Edited by: Gurujothi on May 27, 2012 11:44 PM
              • 4. Re: How to store another value instead of username in application item
                Prabodh
                AND PASSWORD = get_hash (p_username, p_password))
                Where is em_name ? The Get_hash expects 3 inputs, right?

                Regards,
                • 5. Re: How to store another value instead of username in application item
                  AndyH
                  Gurujothi wrote:
                  function CUSTOMAUTHENTICATION
                  (p_emp_name in VARCHAR2,
                  p_username in VARCHAR2,
                  p_password in VARCHAR2)
                  return BOOLEAN
                  APEX expects CustomAuthentication to have two parameters: p_username and p_password.
                  • 6. Re: How to store another value instead of username in application item
                    VC
                    If you are using this function for apex authentication then it must contain only two parameters i.e.
                    So Remove p_emp_name

                    And use p_username parameter for updating/populating emp_name, you don't have to create another parameter to populate a new column with the same value.
                    • 7. Re: How to store another value instead of username in application item
                      Gurujothi
                      Hi Prabodh,
                      I tried the above code,but am getting the error,
                      PLS-00049: bad bind variable 'FUSER'
                      Thank you.
                      • 8. Re: How to store another value instead of username in application item
                        Gurujothi
                        Hi Prabodh,
                        I changed it to,
                        AND PASSWORD = get_hash (p_emp_name,p_username, p_password);
                        but getting
                        PLS-00049: bad bind variable 'FUSER'
                        when compiling.:-(
                        • 9. Re: How to store another value instead of username in application item
                          Prabodh
                          Sorry, you cannot use :FUSER in package. Here is how
                          function CUSTOMAUTHENTICATION
                          (p_username in VARCHAR2,
                          p_password in VARCHAR2)
                          return BOOLEAN
                          is
                           v_emp  varcahr2(100); -- local var declaration
                          begin
                          /*
                           FOR c1 IN (SELECT 1 
                                        FROM app_users
                                      WHERE UPPER (username) = UPPER (p_username)
                               AND PASSWORD = get_hash (p_username, p_password))
                            LOOP
                              APEX_UTIL.SET_SESSION_STATE('FUSER',upper(p_username));
                          */
                             APEX_UTIL.SET_SESSION_STATE('FUSER','');
                             SELECT EMP_NAME INTO v_emp  --modified line
                              FROM app_users
                                      WHERE UPPER (username) = UPPER (p_username)
                               AND PASSWORD = get_hash (p_username, p_password);
                             APEX_UTIL.SET_SESSION_STATE('FUSER',v_emp);   -- line added
                             RETURN TRUE;
                            --END LOOP;
                            --APEX_UTIL.SET_SESSION_STATE('FUSER');
                          EXCEPTION
                            WHEN NO_DATA_FOUND THEN   RETURN FALSE;
                           
                          end CUSTOMAUTHENTICATION;
                          Regards,
                          • 10. Re: How to store another value instead of username in application item
                            Gurujothi
                            Hi Prabodh,
                            Thank you for your reply,
                            Here I have used the APP_USER table and the package,
                            The package has 3 functions/procedures,
                            1.add_user  
                            2.get_hash
                            3.customaunthentication
                            If I use this package for my application,
                            If and only If the data inserted into the (app_user)table through this package's (login_pkg.add_user) procedure,
                            the data will be considered,

                            so I have created one FORM ON STORED PROCEDURE
                            • 11. Re: How to store another value instead of username in application item
                              Gurujothi
                              Hi Prabodh,
                              Thank you for your reply,
                              Here I have used the APP_USER table and the package,
                              The package has 3 functions/procedures,
                              1.add_user  
                              2.get_hash
                              3.customaunthentication
                              If I use this package for my application,
                              If and only If the data inserted into the (app_user)table through this package's (login_pkg.add_user) procedure,
                              the data will be considered,

                              so I have created one page ( FORM ON STORED PROCEDURE) and added the (emp_name,username,password)values into this table.
                              Here,
                              ADD_USER is used to insert the users,
                              GET_HASH is used to encrypt the password and
                              CUSTOMAUTHENTICATION is used to check the table APP_USER during the login time,

                              Actually during the login time USERNAME and PASSWORD is enough to check,
                              should I declare and use EMP_NAME in GET_HASH and CUSTOMAUTHENTICATION?

                              Now exactly I have the following,
                              create or replace PACKAGE login_pkg
                              AS
                              PROCEDURE add_user 
                              (
                               p_emp_name IN VARCHAR2,
                               p_username IN VARCHAR2
                              ,p_password IN VARCHAR2
                              );
                              
                              FUNCTION get_hash 
                              (
                               p_emp_name IN VARCHAR2,
                               p_username IN VARCHAR2
                              ,p_password IN VARCHAR2
                              )
                               RETURN VARCHAR2;
                              
                              FUNCTION customauthentication 
                              (
                              
                               p_username IN VARCHAR2
                              ,p_password IN VARCHAR2
                              )
                               RETURN BOOLEAN;
                              
                              END login_pkg;
                              
                              /
                              create or replace PACKAGE BODY login_pkg
                              AS
                              PROCEDURE add_user 
                              (
                               p_emp_name IN VARCHAR2,
                               p_username IN VARCHAR2
                              ,p_password IN VARCHAR2
                              )
                              AS
                              BEGIN
                              INSERT INTO app_user(emp_name,username, PASSWORD)
                                  VALUES (UPPER (p_emp_name),UPPER (p_username),
                                      get_hash (TRIM (p_emp_name),TRIM (p_username), p_password));
                              
                              COMMIT;
                              
                              EXCEPTION
                              WHEN OTHERS
                              THEN 
                               ROLLBACK; 
                               RAISE;
                              END add_user;
                              
                              FUNCTION get_hash (p_emp_name IN VARCHAR2,p_username IN VARCHAR2, p_password IN  VARCHAR2)
                              RETURN VARCHAR2
                              AS
                              BEGIN
                              RETURN DBMS_OBFUSCATION_TOOLKIT.md5 (
                              input_string =>UPPER (p_emp_name) 
                                              || '/' 
                                              ||
                                  UPPER (p_username) 
                                              || '/' 
                                              || UPPER (p_password));
                              END get_hash;
                              
                              function CUSTOMAUTHENTICATION
                              (
                              p_username in VARCHAR2,
                              p_password in VARCHAR2)
                              return BOOLEAN
                              is
                              v_emp  VARCHAR2(100);
                              begin
                                  
                                 APEX_UTIL.SET_SESSION_STATE('FUSER','');
                                 SELECT EMP_NAME INTO v_emp  
                                  FROM app_user
                                          WHERE UPPER (username) = UPPER (p_username)
                                   AND PASSWORD = get_hash (p_username, p_password);
                                 APEX_UTIL.SET_SESSION_STATE('FUSER',v_emp);   -- line added
                                 RETURN TRUE;
                                
                              EXCEPTION
                                WHEN NO_DATA_FOUND THEN   RETURN FALSE;
                               
                              end CUSTOMAUTHENTICATION;
                              END login_pkg;
                              Still am getting error,
                              Compilation failed,line 50 (14:20:13)
                              PLS-00306: wrong number or types of arguments in call to 'GET_HASH'Compilation failed,line 50 (14:20:13)
                              PL/SQL: ORA-00904: "LOGIN_PKG"."GET_HASH": invalid identifierCompilation failed,line 47 (14:20:13)
                              PL/SQL: SQL Statement ignored
                              whats the problem with above code??
                              Thank you.
                              • 12. Re: How to store another value instead of username in application item
                                Prabodh
                                Hi,
                                Not sure if you are only "telling" what you have done ? Or, you have some query that you missed out writing here.

                                As has been pointed out to you, Apex allows only p_username and p_password parameters in Authentication function. So, the function you are developing will not works as an authentication function with emp_name as an extra parameter. And that has nothing to do with your code compiling successfully.

                                Regards,
                                • 13. Re: How to store another value instead of username in application item
                                  Gurujothi
                                  Hi Prabodh,
                                  Is this possible to fetch the Emp_name and store in the FUSER(application item) from some other table based on the username during the login time,
                                  For example,
                                  "EMP" table(If it has the following columns)
                                  emp_name
                                  username
                                  select emp_name from EMP where username= :p101_username --- and this value should be stored into the Application ITEM, something like this.
                                  Thank you.
                                  • 14. Re: How to store another value instead of username in application item
                                    Prabodh
                                    Yes, that is possible.
                                    That is another table and another select in your Authentication function, and your Authentication function signature is not deviating from the one required by Apex because you are not trying to pass a third parameter to the Auth function.

                                    Regards,
                                    1 2 Previous Next