Home » Developer & Programmer » Forms » Oracle Forms Loop Not Quiet There (Oracle Forms)
Oracle Forms Loop Not Quiet There [message #428738] |
Thu, 29 October 2009 14:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Curious_Programmer
Messages: 11 Registered: October 2009
|
Junior Member |
|
|
I am brand spanking new to oracle forms. I created data block that contains a check box and some fields. What I want to do is if a user checks the check boxes and then clicks on a button to copy all the lines that are checked.
This is what I currently have.
DECLARE
var1 varchar2(1);
var2 varchar2(2);
BEGIN
LOOP
IF :data_block.checkbox = 'Y' THEN
var1 := :data_block.field1;
var2 := :data_block.field2;
LOOP
IF :SYSTEM.LAST_RECORD='TRUE' THEN
CREATE_RECORD;
:data_block.field1 := var1;
:data_block.field2 := var2;
NEXT_ITEM;
EXIT;
END IF;
END LOOP;
END IF;
EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
NEXT_RECORD;
END LOOP;
END;
If the code is non-sensical somewhere, it's probably me. THe reason for the second loop is I want to make sure that the record goes all the way to the bottom before being inserted.
Anyway, the issue is it is working as expected, but only for a record that I have highlighted. If I check three records, it will only copy the highlighted one. I am assuming the line var1 := :data_block.field1; is the issue...HELP please.
[EDITED by LF: applied [code] tags]
[Updated on: Thu, 29 October 2009 14:09] by Moderator Report message to a moderator
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #428739 is a reply to message #428738] |
Thu, 29 October 2009 14:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:The reason for the second loop is I want to make sure that the record goes all the way to the bottom before being inserted. The fact is, there is the LAST_RECORD built-in which will do that; you don't need to "loop + next_record" to reach the last record in the block.
Quote:is working as expected, but only for a record that I have highlighted Once you "copy" the record (do they have to be at the bottom of the block? If not, perhaps you could simply CREATE_RECORD + DUPLICATE_RECORD instead), you are supposed to go back to the first checked record and move onwards from that point. Therefore, you'll need to know which record it was. To do that, use :SYSTEM.CURSOR_RECORD and store its value into a variable, then GO_RECORD(the memorized record) and move on, repeating the same until the last record in the block.
|
|
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #428745 is a reply to message #428741] |
Thu, 29 October 2009 15:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Curious_Programmer
Messages: 11 Registered: October 2009
|
Junior Member |
|
|
So now I am wondering if my loop is the issue. What really happens is a person is in another block and they click a button. I then do a go_block and then do the loop...I am wondering if the loop is just going through the one item I have highlighted???
|
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #428901 is a reply to message #428858] |
Fri, 30 October 2009 10:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Curious_Programmer
Messages: 11 Registered: October 2009
|
Junior Member |
|
|
DECLARE
var1 varchar2(1);
var2 varchar2(2);
pos NUMBER;
skip BOOLEAN;
BEGIN
FIRST_RECORD;
LOOP
IF :data_block.checkbox = 'Y' THEN
var1 := :data_block.field1;
var2 := :data_block.field2;
pos := :SYSTEM.CURSOR_RECORD;
IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
skip := TRUE;
END IF;
LAST_RECORD;
CREATE_RECORD;
:data_block.field1 := var1;
:data_block.field2 := var2;
NEXT_ITEM;
END IF;
IF skip != TRUE THEN
GO_RECORD(pos);
END IF;
EXIT WHEN :SYSTEM.LAST_RECORD='TRUE';
NEXT_RECORD;
END LOOP;
END;
This is the latest rendition although I've been messing with different variations and just can't get it working. The reason I have the if statement for the skip is to make sure that it's not an infinite loop.
[EDITED by LF: applied [code] tags]
[Updated on: Fri, 30 October 2009 14:34] by Moderator Report message to a moderator
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #428922 is a reply to message #428901] |
Fri, 30 October 2009 15:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I'd say that you never check more than one record because you GO_RECORD(pos) which returns you to the first record whose checkbox is checked and repeat what you've already done. What you should do is to move to the next record (i.e. go to record number "pos + 1").
Here's how I'd do that; attached form is based on Scott's DEPT table and created with Forms Developer 10g. In a case you can't run it, here's the push button code:declare
number_of_records number;
where_am_i number;
l_deptno dept.deptno%type;
l_dname dept.dname%type;
l_loc dept.loc%type;
begin
go_block('dept');
-- calculate last record number (to know the exit point)
last_record;
number_of_records := :system.cursor_record;
-- now duplicate checked records
first_record;
loop
exit when :system.cursor_record = number_of_records + 1;
if checkbox_checked('dept.cb') then
-- return to where_am_i + 1 record
where_am_i := :system.cursor_record;
-- memorize items in a checked record
l_deptno := :dept.deptno;
l_dname := :dept.dname;
l_loc := :dept.loc;
-- create a new record at the bottom
last_record;
create_record;
-- I'll make new values slightly different from the original ones
:dept.deptno := l_deptno + 1;
:dept.dname := 'New ' || l_dname;
:dept.loc := 'New ' || l_loc;
-- go back and check another record
go_record (where_am_i + 1);
else
next_record;
end if;
end loop;
end;
Forgot to mention: please, enclose code into the [code] tags in order to preserve formatting. I've done that for you twice. If you are uncertain of how to do that, read OraFAQ Forum Guide - it's described in "How to format your post?" section.
[Updated on: Fri, 30 October 2009 15:11] Report message to a moderator
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #437299 is a reply to message #428738] |
Sun, 03 January 2010 08:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kojot
Messages: 27 Registered: December 2009 Location: Poland
|
Junior Member |
|
|
It's my first post, so I want to say Hi to everyone ![Smile](images/smiley_icons/icon_smile.gif)
I am beginner in forms and I decidied to refresh this topic, becouse I used the code above ![Wink](images/smiley_icons/icon_wink.gif)
I've got two tables: T1(id_prod,name) and T2(id_prod,price,price2,price3). Id_prod is a primary key in first table, and a foreign key in the second. I've put the check box item (which isn't a database item) and the push button on forms in T1 block. Now, I want to see the prices in T2 of all checked records in T1. This is 'my' code of when-button-pressed trigger:
DECLARE
number_of_records NUMBER;
where_am_i NUMBER;
t1_id_prod_v t1.id_prod%TYPE;
BEGIN
GO_BLOCK('T2');
CLEAR_BLOCK;
GO_BLOCK('T1');
last_record;
number_of_records := :system.cursor_record;
first_record;
LOOP
EXIT WHEN :system.cursor_record = number_of_records + 1;
IF checkbox_checked('t1.cb') THEN
where_am_i := :system.cursor_record;
t1_id_prod_v := :t1.id_prod;
GO_BLOCK('T2');
last_record;
create_record;
SELECT t2.id_prod, price, price2, price3
INTO :t2.id_prod, :t2.price, :t2.price2, :t2.price3
FROM t2
WHERE t2.id_prod = t1_id_prod_v;
GO_BLOCK('T1');
GO_RECORD(where_am_i + 1);
ELSE
next_record;
END IF;
END LOOP;
previous_record; --stop the cursor in the last record
--not in the new one
END;
I've changed the T2 property to 'From clause query' and it works, almost, fine.
The first problem is when I checked the last record and press the button my Forms 'hangs up' (dont know how to say it in english ) and behave like making the endless loop. Then I have to close them, press ctr+alt+del and kill the frmweb.exe process.
The second problem shows up when in T2 table is more than one row with the same id_prod (ORA-01422). I know that case doesnt make sense, but I just want to know how to insert more than one row.
My database version is 10.2.0.1.0 and Forms version 10.1.2.0.2.
Regards,
Wojtek
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #437300 is a reply to message #437299] |
Sun, 03 January 2010 08:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
The first problem: the easiest way to see what is going on is to enable debugger so that you could follow code execution. If the form "hangs", it is most probably stuck within the loop (i.e. EXIT condition is never met).
The second problem: ORA-01422 is TOO-MANY-ROWS. SELECT statement returned more than a single record. You can either write EXCEPTION handler unit (in that case, your code should have additional BEGIN-EXCEPTION-END block within the LOOP) or, if possible and if it is OK, use one of aggregate functions (such as MAX) which will certainly return only a single record.
Something like this:LOOP
<some code here>
BEGIN
SELECT t2.id_prod, price, price2, price3
INTO :t2.id_prod, :t2.price, :t2.price2, :t2.price3
FROM t2
WHERE t2.id_prod = t1_id_prod_v;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
-- do *something*; for example, set these values to 0 (zero)
:t2.id_prod := 0;
:t2.price := 0;
<etc.<
END;
<some code here>
END LOOP;
or this:
SELECT MAX(t2.id_prod), MAX(price), MAX(price2), MAX(price3)
INTO :t2.id_prod, :t2.price, :t2.price2, :t2.price3
FROM t2
WHERE t2.id_prod = t1_id_prod_v;
|
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #437428 is a reply to message #428738] |
Mon, 04 January 2010 06:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To exit the loop I'd use:
EXIT WHEN :system.last_record = 'TRUE';
That'll need to go at the end of the loop instead of the begining or the last row won't get processed.
If you want to create multiple rows in your data block from that select then you'd need to change it to a FOR LOOP and create a record in the detail block inside that loop.
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #437462 is a reply to message #437423] |
Mon, 04 January 2010 09:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
kojot wrote on Mon, 04 January 2010 13:22what should I do if I want to insert all of the records which SELECT statement returned ?
If you are inserting records returned by some SELECT statement, you don't need loops - a simple INSERT statement would do. For example, if this is the SELECT that returns records you are interested in:
select empno, ename, sal
from emp
where deptno = 10
and job = 'MANAGER' then - why would you need a loop? Useinsert into some_other_table (empno, ename, sal)
select empno, ename, sal
from emp
where deptno = 10
and job = 'MANAGER'
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #438160 is a reply to message #428738] |
Thu, 07 January 2010 05:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kojot
Messages: 27 Registered: December 2009 Location: Poland
|
Junior Member |
|
|
Thanks a lot cookiemonser and Littlefoot for your reply, you really helped me.
I've got another problem. I was searching the forum, but I couldn't find a clear answer. As you know, I'm selecting data in T2 block using SQL statement which depends on check box item of another block. That's why I can't make master-detail relation. Now, if I modify record in T2 block and make commit form, it's not updating table in database, but adding a new record. How can I avoid this problem, and make form to update records, which are returned into block by SQL statement ?
I tried to make a pre-insert trigger. I put there cursor, which reads the whole table, then compares record with this in T2 block, and if there are any difference it makes update statement. It even works, but after that it's still adding a new record, and I'm not sure the solution of this problem should be so complicated.
Wojtek
|
|
|
Re: Oracle Forms Loop Not Quiet There [message #438177 is a reply to message #438160] |
Thu, 07 January 2010 06:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Such problems often occur when people try to reinvent the wheel. Forms has its logic, certain default behaviour which works perfectly well.
If you follow the rules, everything is OK. For example:
UPDATING AN EXISTING RECORD
Steps:
- <Enter query>
- enter search criteria
- <Execute query>
- overtype old values with new ones
- commit changes
The result: record in the database was updated.
INSERTING A NEW RECORD
Steps:
- do not enter query mode
- go to an empty record (or create it),
- type new values into items
- commit
The result: new record was added into the database
YOU
Using SQL statements, you didn't retrieve existing records into the data block, but "simulated" the INSERT operation. You may have overtyped certain values, but it was just a modified new record, not updated existing record.
Commit created new records in the database.
What should / could you do: get rid of such a design. "Checkbox item in another block" can probably be implemented into the T2 block's DEFAULT WHERE clause. Therefore, after checking the checkbox, write a trigger (PRE-QUERY on T2 block might do the job) that will modify T2 block's property (SET_BLOCK_PROPERTY (default_where)).
That's all. Nothing else, simply <Execute query> on the T2 block.
It seems that deeper you dig to fix the problem, deeper hole you make and you won't get out that soon.
|
|
|
Goto Forum:
Current Time: Mon Feb 10 05:35:39 CST 2025
|