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
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 messageGo to next message
Littlefoot
Messages: 20901
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 #428740 is a reply to message #428739] Thu, 29 October 2009 14:24 Go to previous messageGo to next message
Curious_Programmer
Messages: 11
Registered: October 2009
Junior Member
Yes, it does have to be at the bottom of the block, but I was not familiar with the GO_RECORD command. Let me see if I can play with that and get it working.
Re: Oracle Forms Loop Not Quiet There [message #428741 is a reply to message #428739] Thu, 29 October 2009 14:54 Go to previous messageGo to next message
Curious_Programmer
Messages: 11
Registered: October 2009
Junior Member
So I store the record right after storign the field variables, then go back after i break out of the inside loop...no luck though. Still only copying one record.
Re: Oracle Forms Loop Not Quiet There [message #428745 is a reply to message #428741] Thu, 29 October 2009 15:22 Go to previous messageGo to next message
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 #428858 is a reply to message #428745] Fri, 30 October 2009 06:12 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does the newest code look like? It seems that you have used some new built-ins (such as LAST_RECORD) (I hope) so ... would you mind to post it?
Re: Oracle Forms Loop Not Quiet There [message #428901 is a reply to message #428858] Fri, 30 October 2009 10:48 Go to previous messageGo to next message
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 messageGo to next message
Littlefoot
Messages: 20901
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 messageGo to next message
kojot
Messages: 27
Registered: December 2009
Location: Poland
Junior Member
It's my first post, so I want to say Hi to everyone Smile
I am beginner in forms and I decidied to refresh this topic, becouse I used the code above Wink

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' Razz 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 Smile) 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 Smile
Re: Oracle Forms Loop Not Quiet There [message #437300 is a reply to message #437299] Sun, 03 January 2010 08:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 #437423 is a reply to message #428738] Mon, 04 January 2010 06:22 Go to previous messageGo to next message
kojot
Messages: 27
Registered: December 2009
Location: Poland
Junior Member
Thanks Littlefoot for your reply Smile According to your advice I used the debugger and I found out, that, if the last record is checked, this command: GO_RECORD(where_am_i + 1); cannot be execute well, because next record doesn't exist. Nothing's change, so the loop doesn't end. I modified the code like this:

GO_RECORD(where_am_i + 1);
 IF :system.cursor_record = where_am_i THEN
   EXIT;
 END IF;


I'm not sure this is the best solution, but it works.

About the second problem, could you tell me what should I do if I want to insert all of the records which SELECT statement returned ?
Re: Oracle Forms Loop Not Quiet There [message #437428 is a reply to message #428738] Mon, 04 January 2010 06:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
kojot wrote on Mon, 04 January 2010 13:22
what 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? Use
insert 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 messageGo to next message
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 Smile
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
Littlefoot
Messages: 20901
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.
Previous Topic: Forms 6i compatibility with 10g database
Next Topic: Need to get the Current Cursor postion - on What record (merged)
Goto Forum:
  


Current Time: Sun Dec 11 06:28:44 CST 2016

Total time taken to generate the page: 0.17952 seconds