OraFAQ Forum: Forms » Forms 10g

Home » Developer & Programmer » Forms » Forms 10g

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

Forms 10g [message #163856]

Mon, 20 March 2006 07:22

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Hi,

I am having a form which is having non-database block to which i query the values from a multi-table join view. I am using on-insert, on-update triggers to insert and update the base table values.

The problem now i am facing is when a user after querying the records, makes a change in any one of the record (say the 2nd record) and moves to the next record (without saving the changes),i am unable to get the record status here (as this is a non-database block), the system should ask him to save the changes (as it is by default in any database-blocks) , but here i cannot simulate this feature.

Any idea how to accomplish this ?

Thanks and Regards,

Nirmal Narayanan.

Report message to a moderator

Re: Forms 10g [message #163863 is a reply to message #163856]

Mon, 20 March 2006 08:32

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Perhaps you could use the when-validate-item trigger to set the record status. Just a wild guess...

MHE

Report message to a moderator

Re: Forms 10g [message #163960 is a reply to message #163863]

Tue, 21 March 2006 00:00

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Yes ... use the When-Validate-Item to set another non-database field on this row and store the record_status in it. This status can then be checked, on a record by record basis, in your later processing.

David

Report message to a moderator

Re: Forms 10g [message #163978 is a reply to message #163960]

Tue, 21 March 2006 01:18

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

using GET_BLOCK_PROPERTY(block_id,STATUS) will not help me as it will always return 'QUERY' in the status as this is a NON-DATABASE Block, i am querying the values from a cursor, so the record status always will be 'QUERY',

i am getting the same status while using

GET_BLOCK_PROPERTY(block_id,STATUS) in side when-validate-item,
and also when-validate-item cannot validate items on a non-database block right ?

nirmal

Report message to a moderator

Re: Forms 10g [message #163985 is a reply to message #163978]

Tue, 21 March 2006 02:07

Maaher
Messages: 7065
Registered: December 2001

Senior Member

nirmalnarayan wrote on Tue, 21 March 2006 08:18

using GET_BLOCK_PROPERTY(block_id,STATUS) will not help me

No, I said

Maaher wrote on Mon, 20 March 2006 15:32

Perhaps you could use the when-validate-item trigger to set the record status

Use SET_RECORD_PROPERTY:

Set_Record_Property( the_record_number, 'YOUR_BLOCK_NAME', STATUS, CHANGED_STATUS);

MHE

Report message to a moderator

Re: Forms 10g [message #163991 is a reply to message #163985]

Tue, 21 March 2006 02:55

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Hi,

Thank you for your reply.

But That is where exactly i am stuck up.

I can set the STATUS to CHANGED only when the value in the text field is changed, and being a non-database item in a non-database block, into which records are queried from a cursor, how can check whether the value is changed or is as is when the value was queried ? since when-validate-item will work only for database-items here is where i am really stuck .

There is no way to check the old value (queried one) and new value (changed by the user) in a non-database item in a non-database block ?

Thanks,

Nirmal

Report message to a moderator

Re: Forms 10g [message #164108 is a reply to message #163991]

Tue, 21 March 2006 18:31

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Set it in the When_Validate_Item trigger, or are you saying that the WVI is not firing for a non-database field?

David

Report message to a moderator

Re: Forms 10g [message #164129 is a reply to message #164108]

Tue, 21 March 2006 23:18

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Yes exactly, the WVI is not firing for the non-database items.

Is there any other way to check the old value (queried one) and new value (changed by the user) in a non-database item in a non-database block ?

Any idea?

Thanks,

Nirmal

Report message to a moderator

Re: Forms 10g [message #164147 is a reply to message #164129]

Wed, 22 March 2006 00:17

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Well, it fires for me. I suggest that you start with a new form, create a new empty block, add a couple of fields, and add a WVI trigger containing

begin
  message('WVI ID');
  pause;
end;

Do a Ctrl-Shft-K, Ctrl-T, then Ctrl-R. Enter some data, press the TAB key, and you should get a "Please acknowledge." popup with "WVI ID" on the message line.

David

Report message to a moderator

Re: Forms 10g [message #164153 is a reply to message #164147]

Wed, 22 March 2006 00:50

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

The WVI trigger fires on pressing the TAB key.

But my main issue remains without a solution.

Is there any other way to check the old value (queried one) and new value (changed by the user) in a non-database item in a non-database block ?

This should only happen when there is a queried value in the field and the user tries to change this value .

Thanks

Nirmal

Report message to a moderator

Re: Forms 10g [message #164154 is a reply to message #164153]

Wed, 22 March 2006 00:53

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

If the WVI fires then the user has changed something. Try it. TAB through a field that you haven't changed and nothing happens. If you want to know if the new information is different to the old then use a Pre-Text-Item trigger to store the value as you go into it, and test it in the WVI.

David

Report message to a moderator

Re: Forms 10g [message #164158 is a reply to message #164154]

Wed, 22 March 2006 01:19

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Thank you Man,i will try it

Nirmal

Report message to a moderator

Re: Forms 10g [message #164219 is a reply to message #164158]

Wed, 22 March 2006 06:39

Maaher
Messages: 7065
Registered: December 2001

Senior Member

David, the root problem is (at least that's what I think as I cannot verify) that a non DB block will not do anything with changed/new records. The ON-<dml_operation> triggers won't fire.

[edit]
Now that I think of it, why don't you base the block on DUAL (or any table) and create ON-DELETE, ON-UPDATE, ON-INSERT and ON-LOCK triggers yourself to prevent the base table from being manipulated?

MHE

[Updated on: Wed, 22 March 2006 06:41]

Report message to a moderator

Re: Forms 10g [message #164343 is a reply to message #164219]

Thu, 23 March 2006 01:22

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Agreed if i make the datablock based on a table, then any one of the items should be made database item, to track the changes, and i am using ON-INSERT trigger to insert records, and COMMIT is used in this trigger to save the non-database items in the non-database block.

So if i make any of the item database item and make the block based on table, then where will i use COMMIT after ON-INSERT to commit the changes made in non-database items of the block as using COMMIT in ON-INSERT for a datablock will result in a error and this error will not happen while using COMMIT in ON-INSERT for non-database blocks ?

Thanks
Nirmal

Report message to a moderator

Re: Forms 10g [message #164418 is a reply to message #164343]

Thu, 23 March 2006 06:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I'd probably put something like this in a when-validate-item trigger:

Begin
  If get_record_property(:SYSTEM.CURSOR_RECORD, 'DUAL', STATUS) = 'INSERT'
  Then
    :dual.dummy := 'x';
  Else
    set_record_property(:SYSTEM.CURSOR_RECORD, 'DUAL', STATUS, CHANGED_STATUS);
  End if;
End;

But even more likely I'd base the block on a view.

MHE

Report message to a moderator

Re: Forms 10g [message #164430 is a reply to message #164418]

Thu, 23 March 2006 06:57

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Even then i will not be able to commit the values of non-database into the tables after doing ON-INSERT.

I cannot base the block on a view since there are two records which according to some criteria i need to check that two records are entered by the user before commiting the form, and i cannot use a GO_RECORD(recno) statement in ON-INSERT to check for particular value in the second record.

Thanks

Nirmal

Report message to a moderator

Re: Forms 10g [message #164436 is a reply to message #164430]

Thu, 23 March 2006 07:44

Maaher
Messages: 7065
Registered: December 2001

Senior Member

nirmalnarayan wrote on Thu, 23 March 2006 13:57

Even then i will not be able to commit the values of non-database into the tables after doing ON-INSERT.

Strange, I've tested it here. See a small form in attach. It just demonstrates what I meant. I'm on 6i (client/server).

The idea is that I've got 3 non database fields and if 1 of them changes Forms tracks the changes and on commit it will execute the necessary DML trigger.

If this doesn't work for you then we'll need to find another way.

MHE

Attachment: ORAFAQ.fmb
(Size: 56.00KB, Downloaded 1134 times)

Report message to a moderator

Re: Forms 10g [message #164439 is a reply to message #164436]

Thu, 23 March 2006 08:14

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

/forum/fa/914/0/

Let me once more explain the scenario, Consider that the fields item1,item2,item3 in the figure is been
(this is for explaining the scenario only, i have a complex view to query from and insert values into different values in exact scenario ) queried with values from a cursor, and this thing has been written in KEY-EXEQRY.

After querying in the values if the user may change the values in the field in the form, and then they may save the form, then i need to update or insert the values. since this is non-database items after inserting or updating the values as shown in the ON-INSERT trigger below, i need to do a commit as these are non-database items, since the COMMIT is a restricted procedure as shown in the screenshot above(this will happen if any of the items is a database item) if all the items are non-database items the same error will not happen and we can use COMMIT inside ON-INSERT, ON-UPDATE trigger.

ON-INSERT Trigger
------------------------
Declare
v_retval PLS_INTEGER;
Begin
set_alert_property('INFO_ALERT', TITLE, 'Insert');
set_alert_property('INFO_ALERT', ALERT_MESSAGE_TEXT, 'There has been a new record.');
v_retval := show_alert('INFO_ALERT');
Insert into table1(item1,item2,item3) values (:item1,:item2,:item3);
COMMIT;
End;

AS explained in my earlier reply,

"I cannot base the block on a view since there are two records which according to some criteria i need to check that two records are entered by the user before commiting the form, and i cannot use a GO_RECORD(recno) statement in ON-INSERT to check for particular value in the second record. "

This is the scenario, here , i hope you would have got the exact picture. Can you suggest any other solution.

Thanks

Nirmal

Attachment: ORAFAQ.bmp
(Size: 863.34KB, Downloaded 1931 times)

Report message to a moderator

Re: Forms 10g [message #164445 is a reply to message #164439]

Thu, 23 March 2006 08:50

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Sorry, my forms knowledge is kind of rusty.

I don't see the need for the commit. I created a table mhe_foo and added an insert statement like yours but I didn't explicitly commit (I kept the Forms default processing). Afterwards I saw my record in SQL*Plus.

I have been thinking for another solution:
Create a package (either on the database or in Forms) with

an array to hold the records in the table and a field to indicate the sort of operation (insert/update/delete)
a procedure to add a record to your array
a procedure to remove a record from your array
a procedure to process the records

For each record you insert/update you create a row in your array and in the ON-COMMIT you can process the array.

MHE

Report message to a moderator

Re: Forms 10g [message #164505 is a reply to message #163856]

Thu, 23 March 2006 13:20

M0nst3r
Messages: 38
Registered: February 2006
Location: Wherever the Money Is

Member

The solution to this problem is simple. When using views, your block's Database Data Block property should be set to "Yes".

Then, all you need to do is write table handlers for the ON-INSERT, ON-DELETE, ON-LOCK, and ON-UPDATE block triggers.

See the Oracle Application Developer's Guide for more information.

Report message to a moderator

Re: Forms 10g [message #164552 is a reply to message #164505]

Thu, 23 March 2006 23:47

nirmalnarayan
Messages: 261
Registered: April 2005
Location: India

Senior Member

Boss,
I cannot base the block on the view, if i base it on view, i am having two rows which point to same fields in table, for a certain criteria i need to check whether the user has input data into two rows in the block, since i cannot use a GO_RECORD in ON-INSERT before the user saves the form i cannot base the block on view or on table, it should be non-database block and if you are writing insert query in ON-INSERT , you need to specify COMMIT explicitly , since this is a non-database block. Form will implicitly commit a transaction only if the block is a database block

Thanks

Nirmal

Report message to a moderator

Re: Forms 10g [message #164691 is a reply to message #163856]

Fri, 24 March 2006 14:28

M0nst3r
Messages: 38
Registered: February 2006
Location: Wherever the Money Is

Member

Nirmal,

Before you take the action on the suggestion below, make sure it's really impossible to source the data from a view.

When the rare scenario occurs where the data source can't be a view, you should use a stored procedure as the data source. This change will impact performance a bit; however, you'll still be able to set your block's Database Data Block property to "Yes". As I said in my last message, use table handlers to manage the DML statements.

Good luck.

Report message to a moderator

Previous Topic:	connecting forms and oracle
Next Topic:	Dynamic Push of a Button.....Help?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 12 17:43:54 CST 2025