Oracle PL/SQL - Login & Cursors

[GeForceX]

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:

DECLARE
  
  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;

BEGIN

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

But what of interpreters and administrators? 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 order? Or is there a way I can have the SELECT statement include THREE tables?

Any help here would be appreciated!

 

[anujinka]

If three tables have different field name u cannot select single statement.
Also if your above code is running correct why don't you use just multiple cursor and check with IF ELSIF

Didn't checked below code.

DECLARE
  
  CURSOR logon_s_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;

  CURSOR logon_a_cursor IS

    SELECT a_dce
    FROM administrator
    WHERE a_dce = :logon_block.s_dce
    AND a_pw = :logon_block.s_pw;

  admin_row logon_cursor%ROWTYPE;

  CURSOR logon_i_cursor IS

    SELECT i_dce
    FROM interpreter
    WHERE i_dce = :logon_block.s_dce
    AND i_pw = :logon_block.s_pw;

  interp_row logon_cursor%ROWTYPE;

BEGIN

  OPEN logon_s_cursor;
  FETCH logon_s_cursor into student_row;

  OPEN logon_a_cursor;
  FETCH logon_a_cursor into admin_row;

  OPEN logon_i_cursor;
  FETCH logon_i_cursor into interp_row;

  IF logon_s_cursor%FOUND
	THEN GO_ITEM('VIEW_STUDENT.s_dce');
  ELSIF logon_a_cursor%FOUND
	THEN GO_ITEM('VIEW_ADMIN.s_dce');
  ELSIF logon_i_cursor%FOUND
	THEN GO_ITEM('VIEW_INTERP.s_dce');
  ELSE
	MESSAGE('Invalid DCE Username or Password');
    :logon_block.s_dce := '';
    :logon_block.s_pw := '';
    GO_ITEM('logon_block.s_dce');
  END IF;
/* comment You can close cursor here */
END;

 

[ameoba]

It seems kind of odd that you have 3 separate tables for authentication. That this is a problem might indicate that you should redesign your schema.

 

[GeForceX]

Actually, it is not a problem. I am just inexperienced with PL/SQL programming (as well as all kinds of programming). It is odd to have three different tables. It can be alleviated by having a PERSONS table where all three tables are connected to one with a username and password. But worry not, I have this logon form working using the suggestion by anujinka.

DECLARE
	
	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;

BEGIN

	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');
	ELSE MESSAGE('Invalid DCE Username or Password');
		:logon_block.dce_username := '';
		:logon_block.dce_password := '';
		GO_ITEM('logon_block.dce_username');
		:logon_block.loop_count := :logon_block.loop_count + 1;
		IF :logon_block.loop_count = 3
			THEN MESSAGE('Hey, stop hacking!');
		:logon_block.loop_count := 0;
		
	END IF;

	CLOSE student_logon_cursor;
	CLOSE interpreter_logon_cursor;
	CLOSE administrator_logon_cursor;

END;

 

[Methos]

A bit cliche but as Antoine de Saint-Exupéry wrote "In anything at all, perfection is finally attained not when there is no longer anything to add, but when there is no longer anything to take away." Words to live by when designing your db; more so than any other part of your application. If you find yourself programming around database oddities, you should stop and fix the database. My 2 cents, I don't even work in the IT industry anymore.