Home » Developer & Programmer » Forms » Login page problem
Login page problem [message #405683] Fri, 29 May 2009 00:59 Go to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Got a PM from aiznu

hi,
if u don't mind,can u help me to check my login page using form 9i.
i was 
create user nur identified by nur;
then i 
create role admin;
grant dba to admin;
grant admin to nur;

and i also 
create user aiznu identified by aiznu;
create role r_staff;
grant r_staff to aiznu;

in my form:

DECLARE
   v_alert NUMBER;
BEGIN
   if :USERNAME is null then
      message('Username must be entered');
      go_item('LOGON.USER');
      return;
   end if;

   if :PASSWORD is null then
      MESSAGE('Password must be entered');
      go_item('LOGON.PASSWORD');
      return;
   end if;

    IF (:counter < 3 ) THEN
       :counter := :counter + 1;

       LOGON(:USERNAME, :PASSWORD || '@'||'DBA', TRUE);

       :GLOBAL.USER := :USERNAME;
       :GLOBAL.PASSWORD := :PASSWORD;

       BEGIN
          SELECT ROLE
          INTO :GLOBAL.v_role
          FROM SESSION_ROLES
          WHERE ROLE IN ('ADMIN','R_STAFF','R_MEMBER');

          IF (:GLOBAL.v_role='R_STAFF' ) THEN
             NEW_FORM(:global.Form_Path ||'main.fmx');

          ELSIF (:GLOBAL.v_role='ADMIN') THEN
             NEW_FORM(:global.Form_Path ||'admin.fmx');

          ELSIF (:GLOBAL.v_role='R_MEMBER' ) THEN
             NEW_FORM(:global.Form_Path ||'membermain.fmx');
          END IF;

       EXCEPTION
          WHEN OTHERS THEN
             v_alert:=Show_Alert('LOGON_FAILURE');
             :USERNAME :='';
             :PASSWORD :='';
       END;
    ELSE
       LOGON(:USERNAME, :PASSWORD || '@'||'DBA', FALSE);

       v_alert:=Show_Alert('ATTEMPT_FAILURE');
       :USERNAME :='';
       :PASSWORD :='';

       IF v_alert=ALERT_BUTTON1 THEN
          --Exit_Form;
       NEW_FORM(:global.Form_Path ||'logon.fmx');

       END IF;
   END IF;
END;
to login using user aiznu..it success.
but using nur.. it can't go to admin.fmx. can't error but can't display the admin.fmx.
can u help me?
Re: Login page problem [message #405695 is a reply to message #405683] Fri, 29 May 2009 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Remove WHEN OTHERS and try again.

"SELECT ROLE FROM SESSION_ROLES statement" might end up with TOO MANY ROWS exception.

Also, you've created role named "r_staff", but nothing is granted to it. Then it is granted to user "aiznu"; is that what you meant to do?
Re: Login page problem [message #405707 is a reply to message #405695] Fri, 29 May 2009 02:53 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
Quote:
Remove WHEN OTHERS and try again.

when remove that.has error frm-40735:when-button-pressed trigger raised unhandled exception ORA-01422

Quote:
"SELECT ROLE FROM SESSION_ROLES statement" might end up with TOO MANY ROWS exception.

i have using where clause

Quote:
Also, you've created role named "r_staff", but nothing is granted to it. Then it is granted to user "aiznu"; is that what you meant to do?

role r_staff was grant to user aiznu..

n the questions is.. when i login using this syntax.. as user aiznu.. the form go to main.fmx but why when i login as user nur..
it's go to this error
EXCEPTION
          WHEN OTHERS THEN
             v_alert:=Show_Alert('LOGON_FAILURE');
             :USERNAME :='';
             :PASSWORD :='';
       END;
Re: Login page problem [message #405716 is a reply to message #405707] Fri, 29 May 2009 04:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-01422 is TOO MANY ROWS. WHERE clause itself doesn't protect you from that error - it must return a single record. Unless you do that, you'll keep on running into the same error all over again.

Quote:
role r_staff was grant to user aiznu.

I know; but nothing was granted to role "r_staff".
Re: Login page problem [message #405783 is a reply to message #405716] Fri, 29 May 2009 07:27 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
yes i know..the WHERE clause is return single record. but when use WHERE <column> IN <name in column> -> its definite to admin,r_staff and r_member.
SELECT ROLE
      INTO :GLOBAL.v_role
      FROM SESSION_ROLES
      WHERE ROLE IN ('ADMIN','R_STAFF','R_MEMBER');


r_staff and r_member have a privileged. for example i put GRANT SELECT, INSERT, UPDATE ON SUPPLIER TO R_STAFF;
so the role to log in using role r_staff and r_member is success. but role using admin is not success to log in.i don't because the coding is same.
Re: Login page problem [message #405831 is a reply to message #405783] Fri, 29 May 2009 14:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you copy/paste result of the above query (the one you posted in your previous message) when run in SQL*Plus? Something like this:
SQL> select role
  2  from session_roles
  3  where role in ('ADMIN', 'R_STAFF', 'R_MEMBER');

no rows selected

SQL>
Re: Login page problem [message #405837 is a reply to message #405831] Fri, 29 May 2009 21:28 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
I get this result:
SQL> conn nurul/nurul@dba
Connected.
SQL> select role
  2  from session_roles
  3  where role in ('ADMIN', 'R_STAFF', 'R_MEMBER');

ROLE
------------------------------
R_MEMBER
R_STAFF
ADMIN
Re: Login page problem [message #405917 is a reply to message #405837] Sun, 31 May 2009 15:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Good.

Now transfer this SELECT statement into a PL/SQL procedure:
 declare
   l_role session_roles.role%type;
 begin
   select role
     into l_role
     from session_roles
     where role in ('ADMIN', 'R_STAFAF', 'R_MEMBER');
 end;
 /

Copy/paste result of this PL/SQL block.
Re: Login page problem [message #406036 is a reply to message #405917] Mon, 01 June 2009 20:13 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
can u explain me step by step where i want to put the PL/SQL statement? i don't understand n i not skillful in PL/SQL statement.its in when-button-press that i put the login statement or in PL/SQL libraries?
Re: Login page problem [message #406047 is a reply to message #406036] Mon, 01 June 2009 22:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
SELECT ROLE
INTO :GLOBAL.v_role
FROM SESSION_ROLES
WHERE ROLE IN ('ADMIN','R_STAFF','R_MEMBER');
instead of the above you should use your :USERNAME to get the ROLE.

By
Vamsi
Re: Login page problem [message #406049 is a reply to message #406047] Mon, 01 June 2009 23:37 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
Quote:
Now transfer this SELECT statement into a PL/SQL procedure:
declare
l_role session_roles.role%type;
begin
select role
into l_role
from session_roles
where role in ('ADMIN', 'R_STAFAF', 'R_MEMBER');
end;
/
Copy/paste result of this PL/SQL block.


i have done this syntax but the output is show the message in message.v_alert:=Show_Alert('ATTEMPT_FAILURE'); for all user that i log in.
Sad
Re: Login page problem [message #406058 is a reply to message #406036] Tue, 02 June 2009 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
aiznu wrote on Tue, 02 June 2009 03:13
can u explain me step by step where i want to put the PL/SQL statement?
In SQL*Plus.
Re: Login page problem [message #406061 is a reply to message #406058] Tue, 02 June 2009 00:18 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
do you mean like this?
SQL> declare
  2     l_role session_roles.role%type;
  3   begin
  4     select role
  5       into l_role
  6       from session_roles
  7       where role in ('ADMIN', 'R_STAFAF', 'R_MEMBER');
  8   end;
  9   /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

or
SQL> CREATE PROCEDURE ROLE_PROC
  2  declare
  3     l_role session_roles.role%type;
  4   begin
  5     select role
  6       into l_role
  7       from session_roles
  8       where role in ('ADMIN', 'R_STAFAF', 'R_MEMBER');
  9   end;
 10   /

Warning: Procedure created with compilation errors.

Re: Login page problem [message #406062 is a reply to message #406061] Tue, 02 June 2009 00:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I meant the first one.

Now you have seen the output:
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
Your query does return too many values. You must add another condition into the WHERE clause (or do something else, perhaps consider Vamsi's suggestion) in order to make this SELECT statement return just a single value. As long as you don't do that, you won't be able to successfully run the query (including the whole Form procedure).
Re: Login page problem [message #406092 is a reply to message #406062] Tue, 02 June 2009 03:26 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
Thank you for helping me.. i think this my mistake about the user that i had grant. Embarassed thank you especially to Littlefoot and vamsi kasina.now i success to login any user.

but i have another question.. when user login such as nur and she want to see the staff information.the form will be display her information only.not another user information.how can i apply?

[Updated on: Tue, 02 June 2009 03:57]

Report message to a moderator

Re: Login page problem [message #406102 is a reply to message #406092] Tue, 02 June 2009 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
the form will be display her information only.not another user information
What "information" are you talking about?
Re: Login page problem [message #406104 is a reply to message #406102] Tue, 02 June 2009 04:29 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
The information that i mean is in staff table have name,address,phone,hiredate. So if user nur login, the form appear the detail about her only.
Re: Login page problem [message #406106 is a reply to message #406104] Tue, 02 June 2009 04:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What makes this restriction? Is it the data block's WHERE clause?

Did you write this form?
Re: Login page problem [message #406107 is a reply to message #406106] Tue, 02 June 2009 04:51 Go to previous messageGo to next message
aiznu
Messages: 24
Registered: May 2009
Junior Member
i don't write the form because i can not imagine what query to use and in where that i put the syntax.can give any example?
Re: Login page problem [message #406111 is a reply to message #406107] Tue, 02 June 2009 05:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Now this is turning into a nonsense ... if you didn't write this form, find the one who did. If he/she is not available, find someone who knows more about Forms than you do. If there's noone there, then you are in deep trouble.

It is almost impossible to lead you through all possible aspects of Forms programming, if you can't even imagine how something is to be done.
Re: Login page problem [message #406115 is a reply to message #406111] Tue, 02 June 2009 05:19 Go to previous message
aiznu
Messages: 24
Registered: May 2009
Junior Member
ok i will try to do something.. anyway thanks to help me
Previous Topic: Time Storing
Next Topic: Moving to the next row in tabular form
Goto Forum:
  


Current Time: Fri Dec 09 23:14:34 CST 2016

Total time taken to generate the page: 0.06567 seconds