3 Replies Latest reply on May 12, 2010 1:46 AM by 772185

    Oracle 10G - cursors - Login Form

      Hey DBAs! I am hoping you guys could help me with a problem I have. I have an Oracle login form with just two text boxes (username & password) and a login button with PL/SQL code behind it. Three kinds of users will have access to the login screen - administrators, students, and interpreters. I have been able to successfully make ONLY STUDENTS login fine using this:


      CURSOR logon_cursor IS

      SELECT s_dce
      FROM student
      WHERE s_dce = :logon_block.s_dce
      AND s_pw = :logon_block.s_pw;

      student_row logon_cursor%ROWTYPE;


      OPEN logon_cursor;
      FETCH logon_cursor into student_row;
      IF logon_cursor%FOUND
      MESSAGE('Invalid DCE Username or Password');
      :logon_block.s_dce := '';
      :logon_block.s_pw := '';
      END IF;

      But what of interpreters and administrators with different views (different froms)? They are all in separate tables (i.e. administrator table and interpreter table). How should I approach this? Should I use multiple cursors? If so, what is the appropriate syntax? Or is there a way I can have the SELECT statement include THREE tables? Should I use GOTO statements? How do I have a login that all three tables could be checked in order and have them go to a different form individually?

      Any help here would be appreciated!
        • 1. Re: Oracle 10G - cursors - Login Form

          How you know which type of user is logging in?
          1) Create a view with union from 3 tables(administrators, students, and interpreters)
          2) change the cursor to check from this query.
          So the cursor will be changed to
          CURSOR logon_cursor IS SELECT s_dce FROM <union_view_name> WHERE s_dce = :logon_block.s_dce AND s_pw = :logon_block.s_pw;
          Hope this helps.



          If my response or the response of another was helpful or Correct, please mark it accordingly
          • 2. Re: Oracle 10G - cursors - Login Form
            Zeeshan BaiG
            First of all this is very very old school logic to declare a cursor for single return row.....not a good practice as said by PL/SQL guru Steven Feuerstein

            Second i see the structure problem also all login IDs and Passwords should be in same table.......instead of Role wise tables....Change it if possible.

            Third You can use UNION ALL to use 3 tables in a single query for your scenario

            Note: if UnionALL throwing Too_many_rows exception replace it with UNION
             select mycol, 'STUDENT_BLOCK' blk_name
             into val , status
              from student
            WHERE s_dce = :logon_block.s_dce
            AND s_pw = :logon_block.s_pw
            UNION ALL
             select mycol, 'ADMIN_BLOCK' blk_name
              from admins
            WHERE s_dce = :logon_block.s_dce
            AND s_pw = :logon_block.s_pw
            UNION ALL
             select mycol ,'INTER' blk_name
              from interps
            WHERE s_dce = :logon_block.s_dce
            AND s_pw = :logon_block.s_pw;
            when no_data_found then 
             message('Invalid User and password');
            raise form_trigger_failure;
            hope it helps
            • 3. Re: Oracle 10G - cursors - Login Form
              Hey guys,

              I thank you for your help. I found out that it turns out I was able to get this to work by using multiple cursors:


                   CURSOR student_logon_cursor IS
                        SELECT s_dce
                        FROM student
                        WHERE s_dce = :logon_block.dce_username
                        AND s_pw = :logon_block.dce_password;
                   student_row student_logon_cursor%ROWTYPE;
                   CURSOR interpreter_logon_cursor IS
                        SELECT i_dce
                        FROM interpreter
                        WHERE i_dce = :logon_block.dce_username
                        AND i_pw = :logon_block.dce_password;
                   interpreter_row interpreter_logon_cursor%ROWTYPE;
                   CURSOR administrator_logon_cursor IS
                        SELECT admin_dce
                        FROM administrator
                        WHERE admin_dce = :logon_block.dce_username
                        AND admin_pw = :logon_block.dce_password;
                   administrator_row administrator_logon_cursor%ROWTYPE;

                   OPEN student_logon_cursor;
                   FETCH student_logon_cursor
                   INTO student_row;
                   OPEN interpreter_logon_cursor;
                   FETCH interpreter_logon_cursor
                   INTO interpreter_row;

                   OPEN administrator_logon_cursor;
                   FETCH administrator_logon_cursor
                   INTO administrator_row;
                   IF student_logon_cursor%FOUND
                        THEN GO_ITEM('STUDENT_VIEW_EDIT.s_dce');
                   ELSIF interpreter_logon_cursor%FOUND
                        THEN GO_ITEM('VIEW_INTERPRETER.i_dce');
                   ELSIF administrator_logon_cursor%FOUND
                        THEN GO_ITEM('VIEW_INTERPRETER.i_dce');
                        MESSAGE('Invalid DCE Username or Password');
                        :logon_block.dce_username := '';
                        :logon_block.dce_password := '';
                   END IF;
                   CLOSE student_logon_cursor;
                   CLOSE interpreter_logon_cursor;
                   CLOSE administrator_logon_cursor;

              I understand this is old school but given that I will be dealing with a small number of students and interpreters, it isn't a concern. I will consider later putting all three tables into one. But for now this works. Thanks!