Home » Developer & Programmer » Forms » problem of query in a procedure (oracle developer suite 10 g , internet explorer 8 , windows xp)
problem of query in a procedure [message #479372] Sat, 16 October 2010 07:53 Go to next message
narama87
Messages: 8
Registered: July 2010
Junior Member
hello everybody,

i need your help ,

well , i have 2 items , a list ( id_sect) and a text_item (id_dde) ,and i used a trigger : when_list_changed ,
i have a table demand that contains the two values (id_dde a primary_key ) and (id_sect a foreign key) and i my goal is to affect the id_dde to the item id_dde when a value of id_sect is selected
but the query is not working
here is my code :
PROCEDURE pro_list_changed IS
id_dmde number;
BEGIN
	if :DEMANDE.id_sect is not null AND :DEMANDE.id_dde is null then
	select id_dde into id_dmde from demande where id_sect=:DEMANDE.ID_SECT;
	
	:DEMANDE.ID_DDE:=id_dmde;
		else if :DEMANDE.id_sect is not null and :DEMANDE.id_dde is not  null then
			:DEMANDE.id_dde:='';
			:DEMANDE.ID_DDE:=id_dmde;
			end if;
		end if;		  
END;


i hope i had been clear enough ,
i'm using oracle developer suite 10 g ,

please help me with the not working query !!!!!

[mod-edit] color removed

[Updated on: Mon, 18 October 2010 13:02] by Moderator

Report message to a moderator

Re: problem of query in a procedure [message #479376 is a reply to message #479372] Sat, 16 October 2010 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i hope i had been clear enough

No, what does mean:
Quote:
the query is not working


Regards
Michel

Report message to a moderator

Re: problem of query in a procedure [message #479377 is a reply to message #479376] Sat, 16 October 2010 08:39 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member
Dear,

Please first check your below query that return value or not then go ahead.

Select id_dde into id_dmde from demande 
where id_sect=:Demande.Id_Sect;
Message('The Value of Variable is - : '||Id_dmde);
Message('The Value of Variable is - : '||Id_dmde);



Regards,

Irfan

Report message to a moderator

Re: problem of query in a procedure [message #479518 is a reply to message #479377] Mon, 18 October 2010 03:06 Go to previous messageGo to next message
narama87
Messages: 8
Registered: July 2010
Junior Member
hello,
thank you guys for answering ,
@Michel => the query is not working means that it has no output , i mean also that id_dmde is empty
@Irfan => the messages are not showing up , neither any alert , and the execution is stopped near this query
any ideas please ?

Report message to a moderator

Re: problem of query in a procedure [message #479519 is a reply to message #479518] Mon, 18 October 2010 03:18 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Run a form in debug mode; trace its execution and you'll probably find why "the execution stopped near this query".

Report message to a moderator

Re: problem of query in a procedure [message #479550 is a reply to message #479519] Mon, 18 October 2010 05:15 Go to previous messageGo to next message
narama87
Messages: 8
Registered: July 2010
Junior Member
i don't know how to use the debbug mode .
but i changed the code into : 
[color=red]
PROCEDURE pro_list_changed IS
	cursor cur_id_dde is
	       SELECT   d.id_dde
               FROM      demande d
               WHERE     d.id_sect = :DEMANDE.ID_SECT;
                    		
BEGIN
	
if :DEMANDE.ID_DDE is null then 
        for r_id_dde in cur_id_dde loop
			:DEMANDE.ID_DDE:=r_id_dde.id_dde;
	end loop;  
else 
			:DEMANDE.ID_DDE := '';
        for r_id_dde in cur_id_dde loop
			:DEMANDE.ID_DDE:=r_id_dde.id_dde;
	end loop;   

end if;
  
END;
[/color]
and it works now , but i can't understand why the query doesn't work ,
may be because of a configuration thing ,
any way , i hope that this code will be useful for some Wink

Report message to a moderator

Re: problem of query in a procedure [message #479552 is a reply to message #479550] Mon, 18 October 2010 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the point of using a loop to get a single value?

As for why the original wasn't working:
PROCEDURE pro_list_changed IS
id_dmde number;
BEGIN
	if :DEMANDE.id_sect is not null AND :DEMANDE.id_dde is null then
	select id_dde into id_dmde from demande where id_sect=:DEMANDE.ID_SECT;
	
	:DEMANDE.ID_DDE:=id_dmde;
		else if :DEMANDE.id_sect is not null and :DEMANDE.id_dde is not  null then
			:DEMANDE.id_dde:='';
			:DEMANDE.ID_DDE:=id_dmde; ********This variable is null at this point*********
			end if;
		end if;		  
END;

Report message to a moderator

Re: problem of query in a procedure [message #479572 is a reply to message #479552] Mon, 18 October 2010 06:38 Go to previous messageGo to next message
narama87
Messages: 8
Registered: July 2010
Junior Member
i didn't find another way to use a query to get id_dde but the cursor method , it works perfectly.
and id_dmde that you said that is empty , it really is since the if not only the else , and that is the problem that still unresolved

Report message to a moderator

Re: problem of query in a procedure [message #479575 is a reply to message #479572] Mon, 18 October 2010 06:55 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
narama87 wrote on Mon, 18 October 2010 12:38
i didn't find another way to use a query to get id_dde but the cursor method , it works perfectly.

If that cursor returns one row then you should be using select into like you did in the first place. If the cursor returns multiple rows then you're going to get indeterminate results.

narama87 wrote on Mon, 18 October 2010 12:38

it really is since the if not only the else

No idea what you mean there.

Report message to a moderator

Re: problem of query in a procedure [message #479612 is a reply to message #479575] Mon, 18 October 2010 09:28 Go to previous messageGo to next message
narama87
Messages: 8
Registered: July 2010
Junior Member
i meant id_dmde is always empty , even in the block of (if ) the first block of the test condition

Report message to a moderator

Re: problem of query in a procedure [message #479614 is a reply to message #479612] Mon, 18 October 2010 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then either:
1) The value of id_dde in the record you are selecting from the demande table is null. In which case your revised code is unlikely to fix the problem.
2) The original select was giving an error that you masked somehow. Most likely no_data_found or too_many_rows.

Report message to a moderator

Re: problem of query in a procedure [message #479752 is a reply to message #479614] Tue, 19 October 2010 03:23 Go to previous message
narama87
Messages: 8
Registered: July 2010
Junior Member
Hello again ,
yes , that's true , the problem was : too_many_rows
thanks a lot for your help
good work for everyone

Report message to a moderator

Previous Topic: some_design_issues
Next Topic: Avoid the user to select twice the same value from LOV (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Sep 19 12:24:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.