OraFAQ Forum: Forms » show all records.

Home » Developer & Programmer » Forms » show all records. (form 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

show all records. [message #605033]

Tue, 07 January 2014 01:44

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

Master Table Structure: Student (Class, Section, stuid, name, f_name,mob)
Detail Table Structure: Student (class,section,stuid,name,f_name,mob)
please see the attached file.i want to show all records based on the combo value. if the class value is prep then it shows me the only record of "Prep". please advised. i have used cusor.but show nothing.in this picture the combo values or query only. and in detail table is display items.
please advised. /forum/fa/11574/0/

Report message to a moderator

Re: show all records. [message #605048 is a reply to message #605033]

Tue, 07 January 2014 03:18

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why are you using a cursor at all?
Why not use the standard execute query functionality?

Report message to a moderator

Re: show all records. [message #605054 is a reply to message #605048]

Tue, 07 January 2014 03:56

mughals_king
Messages: 392
Registered: January 2012
Location: pakistan

Senior Member

Yes @cookiemonster is right go through standard execute_query instead o cursor b'coz u have already done master_detail relation so no need for any cursor.

Thanks
Mughal

Report message to a moderator

Re: show all records. [message #605056 is a reply to message #605054]

Tue, 07 January 2014 04:07

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

pls advised me the trigger how i can write...

Report message to a moderator

Re: show all records. [message #605066 is a reply to message #605056]

Tue, 07 January 2014 04:30

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

The reason you got nothing in your form is the error caused in code you wrote. It raises (unhandled) TOO-MANY-ROWS error (see the status line at the bottom of the screenshot, it says ORA-01422). Therefore, you should first fix that. How? Copy that code into SQL*Plus, debug it, fix the error. Then copy/paste it back into a form trigger.

Besides, what kind of a data model is that? The first message you posted suggests that both master and detail tables are exactly the same. What's the purpose of such a decision? Sounds VERY strange to me.

Finally, I'd also suggest you to use standard, built-in Forms functionality which enables you to easily perform query operations - in that case, you need only one block and no code at all.

Report message to a moderator

Re: show all records. [message #605070 is a reply to message #605056]

Tue, 07 January 2014 04:39

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

shahzad-ul-hasan wrote on Tue, 07 January 2014 10:07

pls advised me the trigger how i can write...

You don't need any triggers for standard forms query functionality

Report message to a moderator

Re: show all records. [message #605071 is a reply to message #605066]

Tue, 07 January 2014 04:41

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

Littlefoot wrote on Tue, 07 January 2014 10:30

The reason you got nothing in your form is the error caused in code you wrote. It raises (unhandled) TOO-MANY-ROWS error (see the status line at the bottom of the screenshot, it says ORA-01422). Therefore, you should first fix that. How? Copy that code into SQL*Plus, debug it, fix the error. Then copy/paste it back into a form trigger.

I suspect the select will work just fine in sqlplus.
I suspect its a select to get all rows data and fetchs into a single set of datablock items.

Report message to a moderator

Re: show all records. [message #605073 is a reply to message #605066]

Tue, 07 January 2014 04:48

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

DECLARE
	A VARCHAR2(200);
	B VARCHAR2(22);
BEGIN
	SELECT DISTINCT CLASS,SECTION INTO A,B FROM STUDENT;
	IF :CL=A AND :SEC=B THEN
		EXECUTE_QUERY;
	ELSE
		GO_BLOCK('STUDENT');
	END IF;
	END;

Please see the attached code..

select distinct stuid,mob from student
where class='PREP' and Section='A';
....
15 Rows Selected.

[Updated on: Tue, 07 January 2014 04:50]

Report message to a moderator

Re: show all records. [message #605079 is a reply to message #605073]

Tue, 07 January 2014 04:56

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

You're selecting multiple values into scaler variables - that'll always result in ORA-1422.
Again - why are you not using default query functionality?

Report message to a moderator

Re: show all records. [message #605081 is a reply to message #605079]

Tue, 07 January 2014 04:57

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

advised me the default query..

Report message to a moderator

Re: show all records. [message #605084 is a reply to message #605071]

Tue, 07 January 2014 05:01

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

cookiemonster wrote on Tue, 07 January 2014 11:41

I suspect the select will work just fine in sqlplus.
I suspect its a select to get all rows data and fetchs into a single set of datablock items.

I was having on mind a subquery which causes the error, such as

select ...
from ...
where id = (select id        --> this!
            from ...
           )

Now it is obvious; you were right.

"Default query": you don't have to do anything. Create a new form, using Data Block wizard. Run the form. Enter query mode. Enter search criteria. Execute query.

That's all.

Report message to a moderator

Re: show all records. [message #605130 is a reply to message #605084]

Tue, 07 January 2014 08:55

shahzad-ul-hasan
Messages: 615
Registered: August 2002

Senior Member

thanks for your help....

Report message to a moderator

Previous Topic:	clear_block
Next Topic:	running error in form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 22:27:10 CDT 2024