| multi values for one single record (student_id) [message #473990] |
Wed, 01 September 2010 19:23  |
ramboil3arab
Messages: 4
Registered: July 2010
Location: Palestine
|
Junior Member |
|
|
Hello All,
I really need your help in this form issue...
I am using forms 10g...
I have 2 blocks,in one i insert student_id and when i click on the button i should get all his courses in the other block...
BUT.. when the student has more than 1 course I get this:
when button pressed trigger raised unhandled exception ORA-01422
Here is the button code:
begin
select TEST_STUDENT_INFO.stud_name,TEST_STUD_CRSE_DETAIL.crse_no,
TEST_COURSES.crse_name,TEST_STUD_CRSE_DETAIL.crse_type,TEST_STUD_CRSE_DETAIL.crse_type_desc,
TEST_COURSES.crse_time
INTO :stud_name,:crse_no1,:crse_name,:crse_type1,:crse_type_desc1,:crse_time
from TEST_STUDENT_INFO,TEST_COURSES,TEST_STUD_CRSE_DETAIL
where (TEST_STUD_CRSE_DETAIL.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_STUDENT_INFO.STUD_ID =:TEST_STUD_CRSE_REG.STUD_ID)
and (TEST_COURSES.CRSE_NO = TEST_STUD_CRSE_DETAIL.CRSE_NO)
and (TEST_STUD_CRSE_DETAIL.CRSE_TYPE = TEST_COURSES.CRSE_TYPE);
end;
but if the student has ONLY ONE course it WORKS FINE!!!
please help guys, i really appreciate your help in advance
|
|
|
|
|
|
|
|
| Re: multi values for one single record (student_id) [message #474039 is a reply to message #474031] |
Thu, 02 September 2010 03:51  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ramboil3arab wrote on Thu, 02 September 2010 08:30Thank you for your response... can you please clarify number 1? I am quite a fresher to oracle and forms
Base the block on a table or view (view in your case) and use execute_query to populate.
ramboil3arab wrote on Thu, 02 September 2010 08:30
I don't think I need a loop!
Of course you do. Unless you use execute query.
SELECT INTO is designed to get one row - if it finds more than one matching row you get that error.
Even if it didn't do that you're assigning the values to a single record in the detail block.
To do it this way you need to do something like this:
FOR rec IN cursor LOOP
:stud_name := rec.stud_name;
:crse_no1 := rec.crse_no;
........
next_record;
END LOOP;
next_record makes sure that each record retrieved from the query goes in a different record in the datablock.
It's easier to use execute_query.
ramboil3arab wrote on Thu, 02 September 2010 08:30
is there anyway I can show my form to everyone for their help?!
Rack up a few more posts and you'll be able to attach it. I'm not sure there's any point mind. I think you need to spend some time reading the docs.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
