Home » Developer & Programmer » Forms » How to run a query in Oracle form? (renamed by LF)
How to run a query in Oracle form? (renamed by LF) [message #262536] Mon, 27 August 2007 07:32 Go to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
Dear All,

I am making my final year project using oracle 9i and developer 9i. i am stuck with a query which i need to run on my form. the query is :

SELECT TICKETID,TITLE,USERID,INPUTDATE,DESCRIPTION,PRIORITYID,ticket.STATUSID
FROM TICKET, Status
WHERE ticket.statusid = status.statusid
and status.status = 'New'

This query is running fine in SQL. Now i need to run it on form. can anyone help me how can i run this query on form and which trigger shall i use for it.

looking forward for a prompt response.

[Updated on: Mon, 27 August 2007 07:47] by Moderator

Report message to a moderator

Re: Need Urgent Help!!!! [message #262538 is a reply to message #262536] Mon, 27 August 2007 07:45 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you want to do with the result of this query in a form? Use it as block source? Do some computing in a trigger/procedure? Something different?
Re: Need Urgent Help!!!! [message #262546 is a reply to message #262538] Mon, 27 August 2007 07:56 Go to previous messageGo to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
i am making a help desk system as my final year project. a have a form name as inbox in which i need to display some specific records for that i need to run this query. i tried do it with the when new form instance trigger but the data is not being displayed. i m not sure if i m using a correct trigger that is y i need some to help me with form triggers.

thanks and regards.
Re: Need Urgent Help!!!! [message #262582 is a reply to message #262546] Mon, 27 August 2007 11:24 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that this is not the easiest way to do that.

Could you create a block based on the 'status' table? I guess you could. Use a default WHERE "status = 'NEW'". Display 'ticket' table columns as 'displayed items', and that should do it.

If you wish to populate this block as you enter the form, put EXECUTE_QUERY into the WHEN-NEW-FORM-INSTANCE trigger. Populate 'ticket' columns in the POST-QUERY trigger (you could use query from your initial post, just modify it a little bit - don't forget to use INTO clause!).
Re: Need Urgent Help!!!! [message #262612 is a reply to message #262546] Mon, 27 August 2007 21:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you downloaded and worked through any of the sample Forms that are available from the Oracle website? If not, then do so.

David
Re: Need Urgent Help!!!! [message #263830 is a reply to message #262582] Fri, 31 August 2007 02:29 Go to previous messageGo to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
Dear Littlefoot,

Thanks for your advice it worked and my problem has been resolved now ....

now i am running this query on WHEN-BUTTON-PRESSED trigger:

begin
exit_form;
call_form('E:\FinalProject\FinalProject\login.fmx', hide, do_replace);
end;

exit form is working but call_From is not working can you please tell me where am i making the mistake.

waiting for your prompt response.

Thank you.
Re: Need Urgent Help!!!! [message #263843 is a reply to message #263830] Fri, 31 August 2007 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what you are doing while in the car:
- open the door and get out of the car
- want to start the engine, but can not (as you are outside, while key is INSIDE the car)

So, why are you EXITING the form and still want to do something with it? Get rid of EXIT_FORM!
Re: Need Urgent Help!!!! [message #264298 is a reply to message #263843] Sun, 02 September 2007 23:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Perhaps he was in 'enter-query' mode and needed to get out of it before changing form.

David
Re: Need Urgent Help!!!! [message #265108 is a reply to message #263843] Wed, 05 September 2007 07:56 Go to previous messageGo to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
Dear LittleFoot,

Need a little help. When i run this code the Alert message for invalid password shows up which is in the else statement:
Declare
	v_uname varchar(100);
	v_pwd varchar(100);
	v_usertype varchar(100);
	alert_id alert;
	choice number;
	alert_id1 alert;
	choice1 number;

begin
  begin
    select  username, password, usertypeid into v_uname, v_pwd, v_usertype
	from user_master
	where username = :login.username
	and user_master.active = 'Yes';
		
  exception 
	when others then
	alert_id := Find_Alert ('Username');
	choice := Show_ALert(alert_id);
  end;

  if v_pwd = :login.password then
     call_form ('E:\FinalProject\FinalProject\CUSTOMER.fmx', hide, do_replace);
  else
     alert_id1 := Find_Alert ('pwd');
     choice1 := Show_ALert(alert_id1);
  end if;
end;


But when i run the code below the invalid password alert does not shows up which is in the last else statement. Can you please tell me where am i making the mistake.

Declare
	v_uname varchar(100);
	v_pwd varchar(100);
	v_usertype varchar(100);
	alert_id alert;
	choice number;
	alert_id1 alert;
	choice1 number;

begin
  begin
	select  username, password, usertypeid into v_uname, v_pwd, v_usertype
	from user_master
	where username = :login.username
	and user_master.active = 'Yes';
		
  exception 
	when others then
	alert_id := Find_Alert ('Username');
	choice := Show_ALert(alert_id);
	
  end;

  if v_pwd = :login.password then
     if v_usertype = 1 then
	call_form ('E:\FinalProject\FinalProject\CUSTOMER.fmx', hide, do_replace);
     else if v_usertype = 2 then
	     call_form ('E:\FinalProject\FinalProject\Support.fmx', hide, do_replace);
          else if v_usertype = 3 then
	          call_form ('E:\FinalProject\FinalProject\adminpage.fmx', hide, do_replace);
	       else
		  alert_id1 := Find_Alert ('pwd');
		  choice1 := Show_ALert(alert_id1);
	       end if;
	   end if;
     end if;
  end if;
end;



[EDITED by LF - added [code] tags and formatted code. Next time, please, do it yourself.]

[Updated on: Wed, 05 September 2007 12:42] by Moderator

Report message to a moderator

Re: Need Urgent Help!!!! [message #265189 is a reply to message #265108] Wed, 05 September 2007 12:48 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Alert would show up only if
- v_pwd = :login.password
- v_usertype not in (1, 2, 3)

Are those conditions true?

You might rewrite that IF construct as follows (looks prettier):
if v_pwd = :login.password 
then
   if v_usertype = 1 
   then
      call_form ('E:\FinalProject\FinalProject\CUSTOMER.fmx', hide, do_replace);
   elsif v_usertype = 2 
   then
      call_form ('E:\FinalProject\FinalProject\Support.fmx', hide, do_replace);
   elsif v_usertype = 3 
   then
      call_form ('E:\FinalProject\FinalProject\adminpage.fmx', hide, do_replace);
   else
      alert_id1 := Find_Alert ('pwd');
      choice1 := Show_ALert(alert_id1);
   end if;
end if;
Re: Need Urgent Help!!!! [message #265303 is a reply to message #265189] Thu, 06 September 2007 01:56 Go to previous messageGo to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
The condtions are if username is equal to datbase username then check for password if password is equal then check for usertype if usertype is matched then call form.

But if username is not correct then show invalid username alert.

If username is correct and password is incorrect then show invalid password alert.

This is the scenario, now the alert for incorrect username is working but the incorrect password alert in not showing up. I tried the code you corrected but that is also not working.

Please advice.

Thank you in advance.
Re: Need Urgent Help!!!! [message #266183 is a reply to message #265303] Mon, 10 September 2007 02:07 Go to previous messageGo to next message
janaq
Messages: 22
Registered: August 2007
Junior Member
Dear All,

Can anyone help me on this please.
Re: Need Urgent Help!!!! [message #266910 is a reply to message #266183] Wed, 12 September 2007 00:06 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please search this forum for 'login screen'. Many others have asked a similar question.

David
Previous Topic: Dynamic Lov Creation need step by step
Next Topic: Format mask problem at Item Level . Intabular Form.
Goto Forum:
  


Current Time: Mon Dec 05 10:38:50 CST 2016

Total time taken to generate the page: 0.16359 seconds