OraFAQ Forum: Forms » Detecting item field change

Home » Developer & Programmer » Forms » Detecting item field change (oracle forms 10g)

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

Detecting item field change [message #517548]

Tue, 26 July 2011 10:02

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Hello

I have a form that i need to be able to detect when a field is has been updated. I will then do the appropriate update to the table.

I can't use the standard oracle forms data block for handling updates because my data selection is controlled by me via the when-new-block-instance.

The problem is when i select a value from an LOV and place the new data in the field. I don't know how to detect if the field is updated or not. I am currently using a loop to cycle through all the records, I have an if/else in the key_commit trigger that basically determines if there is an update or insert to be done to the table. The problem is that it seems if i select a value for one record that record goes through the INSERT procedure, (which is correct), but all the other records(not touched/changed) are going through the UPDATE procedure.

Is there a way in forms to check the status of a field? I know of system.form_status but that does not work for me.

Thanks

Report message to a moderator

Re: Detecting item field change [message #517554 is a reply to message #517548]

Tue, 26 July 2011 10:16

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

Senior Member

Changing any item will cause it's when-validate-item trigger to fire. Other methods only work with DB blocks.

However I'd recommend basing the block on a procedure instead - it'll be easier in the long run as It'll allow you to use custom code to populate the block while still allowing oracles default update/insert processing to work.

Report message to a moderator

Re: Detecting item field change [message #517709 is a reply to message #517554]

Wed, 27 July 2011 07:51

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Thanks for the advice.

If i move the selection criteria from the when-new-block-instance to a procedure, and base the block on this procedure, does this enable the rowid processing that oracle forms usually uses to update/insert data when the block is based on a table?

As of this moment i thought i had to loop through the entire form checking the field and somehow detect if the field was changed and then make the change to the corresponding record on the table via insert/update procedures.

Report message to a moderator

Re: Detecting item field change [message #517710 is a reply to message #517709]

Wed, 27 July 2011 07:57

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

Senior Member

It's been a long time since I did a block based on a procedure, but from memory:
Oracle can track which rows have been inserted/updated. It'll pass the rows that need to inserted/updated to the appropriate insert/update procedure.
I can't remember whether or not it uses rowid. I suggest you read up on it in the online help / documentation.
You certainly won't need to code a loop in the form to check.

Report message to a moderator

Re: Detecting item field change [message #517854 is a reply to message #517548]

Thu, 28 July 2011 14:26

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Hey cookiemonster

I did as you suggested. I created a package with a query procedure in it. then used the block wizard to create the block, but i get no data.

The properties fields:

query data source type = Procedure
query data source name = majadv_data.majadv_query.

I tried using the form but nothing happened.

I then created a when_new_block_instance trigger and did execute_query then i got the message (query caused no records to be retrieved).

Not sure what to do next.

Report message to a moderator

Re: Detecting item field change [message #517855 is a reply to message #517854]

Thu, 28 July 2011 15:06

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

Senior Member
Account Moderator

Here is an example of how you should do that. Compare it with your solution. Also, read Forms Online Help for more information about basing data block on a procedure (as Cookiemonster already suggested).

Report message to a moderator

Re: Detecting item field change [message #517922 is a reply to message #517855]

Fri, 29 July 2011 08:35

msdtjw
Messages: 25
Registered: March 2011

Junior Member

I checked my program. I have the properties similarly to the ones in the example, but no query trigger was created.

Report message to a moderator

Re: Detecting item field change [message #517934 is a reply to message #517855]

Fri, 29 July 2011 09:54

msdtjw
Messages: 25
Registered: March 2011

Junior Member

In the query data source arguments i have:

Argument
Name type MODE value

P_MAJADV_QRY refcursor IN/OUT
RUN_TERM varchar2 IN :KEY_BLOCK.TERM
RUN_DEPT varchar2 IN :KEY_BLOCK.DEPT_CODE

The query data columns are what are on my canvas.

Report message to a moderator

Re: Detecting item field change [message #520430 is a reply to message #517548]

Mon, 22 August 2011 10:40

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Hi All

I am still having problems getting the block based on a procedure to work. The procedure package spec/body compile fine in the form, but there is no query trigger created.

the Query data source type = Procedure
query data source name = swkmadv.swkmadv_select

query data source columns has all the columns.

query data source arguments =

Argument name type Type Name mode Value
p_majadv_qry TABLE swkmadv.swkmadv_table IN/OUT
run_term varchar2 IN
run_dept varchar2 IN

I have attached the package spec and body.

Any help would be appreciated.

thanks

Attachment: PACKAGE SPEC.pdf
(Size: 120.31KB, Downloaded 1950 times)

Report message to a moderator

Re: Detecting item field change [message #520431 is a reply to message #520430]

Mon, 22 August 2011 10:47

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

Senior Member

msdtjw wrote on Mon, 22 August 2011 16:40

The procedure package spec/body compile fine in the form

?
You don't compile DB packages in forms. Are you creating this package in the form?

Report message to a moderator

Re: Detecting item field change [message #520432 is a reply to message #520431]

Mon, 22 August 2011 10:50

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Yes, in the program units area.

I just recently recompiled the form and the query trigger was created but displays no data. No errors are reported.

From your question, i take it that this is the incorrect area to do this?

Report message to a moderator

Re: Detecting item field change [message #520433 is a reply to message #520431]

Mon, 22 August 2011 10:52

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

Senior Member

And don't post code in pdfs, a lot of people can't/won't download them.
Post the code in the thread, in code tags, as described here:
How to use [code] tags and make your code easier to read?

Report message to a moderator

Re: Detecting item field change [message #520434 is a reply to message #520433]

Mon, 22 August 2011 10:52

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

Senior Member

The package needs to be created in the DB.

Report message to a moderator

Re: Detecting item field change [message #520437 is a reply to message #520434]

Mon, 22 August 2011 11:07

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Thanks cookiemonster. Could you explain why the package needs to be created in the DB as opposed to the program units? I work on oracle 10g unix. Just in case this matters.

I tried to create a block based on a procedure that i already know is on the DB, but the block coud not find the package. This may be because the forms developer is on a separate VM setup. I don't know, i'll have to ask my DBA.

Thanks

Report message to a moderator

Re: Detecting item field change [message #520439 is a reply to message #520437]

Mon, 22 August 2011 11:11

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

Senior Member

Ask Oracle, as far as I'm aware that's just the way it's designed. Though obviously every other potential block source has to be in the DB, why should packages be any different?

Report message to a moderator

Re: Detecting item field change [message #520448 is a reply to message #520439]

Mon, 22 August 2011 13:49

msdtjw
Messages: 25
Registered: March 2011

Junior Member

Okay. I created the package in the DB. The query trigger was created. I tested the form, but no data is being read. I think it is because the parameters that i need to send are not populated.

-- Automatically generated trigger for procedure data source.
-- Do not edit.
-- If this trigger fails to compile, verify the block procedure data source is correct.
DECLARE
bk_data SWKMADV.SWKMADV_TABLE;
BEGIN
BANINST1.swkmadv.swkmadv_query(bk_data, '', '');
PLSQL_TABLE.POPULATE_BLOCK(bk_data, 'SWKMADV');
END;

How do I make the last 2 parameters = to my key_block.term and key_block.dept_code respectively? Is there a way to pass them?

Report message to a moderator

Re: Detecting item field change [message #520518 is a reply to message #520448]

Tue, 23 August 2011 04:16

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

Senior Member

Assuming you have a my oracle support account have a look at note 66887.1 and see if it helps.

Report message to a moderator

Previous Topic:	java.lang.IndexOutOfBoundsException: Invalid index
Next Topic:	how to store report output in clinet machine through oracle forms 11g [merged 3]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 21 15:52:28 CDT 2024