Home » Developer & Programmer » Forms » How to commit NON-DATABASE Blocks in forms
| How to commit NON-DATABASE Blocks in forms [message #85761] |
Wed, 28 July 2004 02:21  |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Hi
I have a Database block based on a table. However, I have not associated the block with the table. So its a non-database block with all the items in the table. Now i have used explicit cursor on push button trigger which selects the records from the table and displays values in the form.
i.e
cursor c1 is select ...
open c1
loop
fetch c1 into etc
end loop
etc
Now once the values are populatedon the screen . the user can update the values on the screen for one column. Now on changing the value I need to update the database with that value when user clicks on commit.
The Problem is that in a Database Block , we can write , something like If :SYSTEM.FORM_STATUS or BLOCK_STATUS = 'CHANGED' then
commit;
How can we do this in a "non database block". Currently it updates all the values in the table everytime the user presses commit.
Thanks in advance
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85765 is a reply to message #85761] |
Wed, 28 July 2004 03:39  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Make one more Control item in your block similar to your updeatable feild and populate it also with your Cursor.
Add following code in your Key-commit trigger:
If :SYSTEM.FORM_STATUS='CHANGED' or BLOCK_STATUS = 'CHANGED' or :hidden_Field<>:Displayed_field then
Commit;
End If;
HTH
Regards
Himanshu
|
|
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85771 is a reply to message #85765] |
Wed, 28 July 2004 04:46 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Hi,
Thanks a lot for the response. Actually, I just wanted to confirm, as I had mentioned I dont have any DATABASE-TABLE BLOCKS in my form. My Form has only two blocks , one control and one NON_BASETABLE block which has all the columns in a table but I have NOT assosicated it with the table. I manually on a click of a button open a cursor and populate all the form flds. Then I have to manually insert or update the table using sql statements. Now Does SYSTEM.BLOCK_STATUS,FORM_STATUS, RECORD_STATUS etc all work on non Basetable blocks ?, i dont think so. Not sure though.
Thanks
|
|
|
|
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85785 is a reply to message #85772] |
Wed, 28 July 2004 22:03 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Thanks for the help Himanshu.
I am now facing another problem. I used a hidden fld as mentioned by you. However, what is happening is that now my form is in tabular form. So there are about 20 rows displayed. On pressing a button I have written the update table sql like
update table
where col1 = :block.fldname
and :block.fldname <> :hiddenfld
There are two issues i am facing with this
1. Only the last changed value is being commited.
i.e, if i update 5 rows, Only the last value is updated. and commited.
2. When I press the button which has the above mentioned code and COMMIT; written, I get pop up alerts saying 'NO_CHANGES TO COMMIT'; Why do i get this alert?. I have not use any :FORM_STATUS, BLOCK_STATUS etc variables. And i have ust written the SQL update statement with COMMIT. There is no alerts created by me.
Thanks
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85787 is a reply to message #85785] |
Wed, 28 July 2004 22:51 |
sachin aggarwal
Messages: 9
Registered: June 2004
|
Junior Member |
|
|
hai,
related to your first problem
you have to use a loop in your when_button_pressed trigger like
go_block(<blck_name>)
first_record;
while system.current_record <> system.last_record loop
then yor code
end loop
then check for last record
and same code if last record
i hope this will solve your problem
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85789 is a reply to message #85785] |
Wed, 28 July 2004 22:59 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Rita,
The method suggested by Sachin is fine but make sure that you have added the Control item in the same block in which you have the other Updetable item.
Other then this there is another method if you do not wish to make use of Hidden item.
In this case you may add a When-validate-item trigger on the updatable item and add the following code in it:
Set_Record_Property(:system.current_record, 'BLOCK_NAME', STATUS, CHANGED_STATUS);
And then in your Key-commit trigger do the loop of the block and check
if :system.record_status='CHANGED' then
insert....
or
update...
commit;
end if;
HTH
Regards
Himanshu
|
|
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85815 is a reply to message #85771] |
Sun, 01 August 2004 21:13 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Hi,
I have another problem. I have a non database tabular form which displays values in the following manner.
co1 col2 col3 col4
1 1 10 24
1 1 10 232
1 1 10 242
2 2 10 25
2 2 10 26
2 3 10 653
Now what I require is COL3 if the value ius changed then for the combination of col1,2 and 3, it should be updated. i.e say in row 2 i update col 3 to 20 then in row 1,2 and 3, COL3 should be updated to 20 , so o/p would be
co1 col2 col3 col4
1 1 20 24
1 1 20 232
1 1 20 242
2 2 10 25
2 2 10 26
2 3 10 653
I tried putting it in a Post text trigger, where in i go to first records, loop till last record search for all records where col1, 2,3 match and update the col3 for that alue. But it gives me ILLEGAL reference to FIRST_RECORD and when i use it in a WHEN _VALIDATE trigger it gives be illegal next_record reference while executing qry. Is there any workaround for this
Any help would be appreciated.
Thanks
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85816 is a reply to message #85800] |
Sun, 01 August 2004 21:14 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Hi,
I have another problem. I have a non database tabular form which displays values in the following manner.
co1 col2 col3 col4
1 1 10 24
1 1 10 232
1 1 10 242
2 2 10 25
2 2 10 26
2 3 10 653
Now what I require is COL3 if the value ius changed then for the combination of col1,2 and 3, it should be updated. i.e say in row 2 i update col 3 to 20 then in row 1,2 and 3, COL3 should be updated to 20 , so o/p would be
co1 col2 col3 col4
1 1 20 24
1 1 20 232
1 1 20 242
2 2 10 25
2 2 10 26
2 3 10 653
I tried putting it in a Post text trigger, where in i go to first records, loop till last record search for all records where col1, 2,3 match and update the col3 for that alue. But it gives me ILLEGAL reference to FIRST_RECORD and when i use it in a WHEN _VALIDATE trigger it gives be illegal next_record reference while executing qry. Is there any workaround for this
Any help would be appreciated.
Thanks
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85826 is a reply to message #85816] |
Mon, 02 August 2004 03:45 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Write a Procedure & call it from Key Commit.
Note that Go_item,Go_block are restricted built-ins and cannot be used in most of Pre & Post triggers.
Otherwise add following code to On-update trigger:
Update Table_name
set Col3= Current col 3 value
where col1= Current col 1 value
and col2= current col 2 value;
HTH
Regards
Himanshu
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85827 is a reply to message #85826] |
Mon, 02 August 2004 04:22 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Himanshu,
My problem is that the form is
1. tabular form
2. NON Database block.
Now when I change the value of col3, I need to update all the col3 values "IN" the form wherein col1, col2 col3 are same
So when the ITEM is changed, i need to look in the block where all the rows match for col1,2,3 and update the col3 of all those rows
Also,
I have another problem in this, say the col3 value is changed, I also need to get the sum of COL3 values where col1, col2, col3 are same
i.e
COL1 COL2 COL3 COL4
1 1 1 5
1 1 1 4
1 1 1 99
2 2 2 3
2 2 2 5
imagine the top o/p as a form(tabular)
now if i change the row3, col3 value from 1 to 10
then ,
1. I need to get the sum of COL3 of row1, row2, row3
2. I need to update row1,row2,row3 col3, value to 10.
Hope you understood.
Thanks
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85830 is a reply to message #85827] |
Mon, 02 August 2004 21:06 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Rita,
Your requirment is not clear and also it would require lot of other Programming restrictions on your block.
In #1 you wrote that you need to have the Sum of Col3 of row1,2 & 3, this makes the value as 12 if col3 is chnaged to 10.
Q? Do you want to set the value of all the Col3 in row 1,2 & 3 to 12?
If yes then #2 is confusing.
Second problem which you will run into is that say user changed Col3 of row 1 to 10 and row 3 to 20 then which value do you intend to be final becuase when row 1 value is processed , it will overwrite any changed value with row1's value.
I am not sure why you have made a Control block, can you tell me all your requirments in detail.
Regards
Himanshu
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #85834 is a reply to message #85830] |
Mon, 02 August 2004 22:41 |
rita
Messages: 41
Registered: April 1998
|
Member |
|
|
Hi Himanshu,
1. The reason why the no DB Block is created is that as per requirements, the user wants the for in a Excel(TABULAR) like format. Now in my case although I have given you the example of one table, There are actually three tables involved. Each in a way having master- detail kinda relationship. Now i could have created three db blocks(in fact thats what is existing, where in the user selects in the master block and then the child records are displayed, then he selects a child record and that in turn displays records from the third record. Hopwever, thats not how the user wants it anymore. He needs it to be shown in a tabular format. So say TABLE1 has 1 record which has one child records in table 2. In table 2 for the first child record there are three child records in table 3. So all ion all he wants the records to be fetched in a tabular format where in now he would see three rows of data. So i created a non DB block , on key execqry, i wrote the explicit cursor with joins, which fetches data into the form items. When he changes any value i have written commit logic in the key-commit trigger. If the row does not exists, it is inserted. Basically only table three is inserted/ updated, table two is only updated and no operation on table 1.
2 As far as myrequirements is concerned. I am done with most of it, only the sum validation is problematic.
Once all records are fetched and the user tries to update column 4 value. I need to check what the total (SUM)col 4 value is for that combination .If it is greater that say col2 - col3, then i should raise error.
e.g
col1 col2 col3 col4
1 1 0 1
1 1 0 0
1 1 0 0
2 2 0 1
2 2 0 1
In the above example you will see that in row 2 if the user puts 1 in col4 , it should raise error as the sum of col4 with new value would be 1 + 1 + 0 = 2 while col1 - col2 = 1. So max the user is allowed is 1 . Similary if you see row 4 and 5, you will see there are 2, 1's in col4 as col2- col3 for row4,5 is 2, so sum(col4) for row4,5 is 2. now if the user tries to put a higer values in any of these rows, error should be raised, however, he can put a vlaue lesser than that. like say 0.
3. Col1, col2 etc are not table columns but actually form items(tabular). I have ust tried to simulate it here. Also, as you can see, col1, col2 values will always be same for the combination, as in the example.
You can forget the previous messages.
Hope i am clear.
Thanks
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #86315 is a reply to message #85773] |
Tue, 21 September 2004 01:47 |
Suvransu Brahma
Messages: 1
Registered: September 2004
|
Junior Member |
|
|
|
Since you have not associated the block with the database table ....one thing that you can do is that....if suppose you have 10 text items....use some technique to find out with text items have been changed by the user(like a check box associated with each text item or something better can also be used!!... think abt it) and then issue a UPDATE statement to the database to make the appropiate changes
|
|
|
|
| Re: How to commit NON-DATABASE Blocks in forms [message #87826 is a reply to message #85789] |
Tue, 15 February 2005 07:47 |
Swathi
Messages: 5
Registered: March 2001
|
Junior Member |
|
|
Hi all,
Sub :Problem on Oracle forms 6i
I have a similar but a bit more complicated problem
actually what i am to do is matrix type form
I have a table_1's columns on x-axis and table_2's columns on y-axis both as multi-record blocks and i have created a grid type non-database block.
I have written some procedures to handle the population of data
It works fine(queries,updates,inserts) as long as I am not trying to assing the value of the non-db block into one of the column of table_2's column in the when-validate-item of the non-database block's block-level trigger which has multiple records oriented towards x-axis.
Since I am handling this when-validate-time, what would be the next possible trigger where i can issue commit preferably before when-validate-record or wise post-block
Thanks in advance
Swathi .P
|
|
|
|
Goto Forum:
Current Time: Mon Sep 16 16:39:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
