This content has been marked as final. Show 3 replies
How you know which type of user is logging in?
So the cursor will be changed to
1) Create a view with union from 3 tables(administrators, students, and interpreters) 2) change the cursor to check from this query.
Hope this helps.
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;
If my response or the response of another was helpful or Correct, please mark it accordingly
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
hope it helps
BEGIN 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; go_block(blk_name); Exception when no_data_found then message('Invalid User and password'); raise form_trigger_failure; end;
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
WHERE s_dce = :logon_block.dce_username
AND s_pw = :logon_block.dce_password;
CURSOR interpreter_logon_cursor IS
WHERE i_dce = :logon_block.dce_username
AND i_pw = :logon_block.dce_password;
CURSOR administrator_logon_cursor IS
WHERE admin_dce = :logon_block.dce_username
AND admin_pw = :logon_block.dce_password;
MESSAGE('Invalid DCE Username or Password');
:logon_block.dce_username := '';
:logon_block.dce_password := '';
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!