OraFAQ Forum: Forms » Commit after insert into

Home » Developer & Programmer » Forms » Commit after insert into (Forms 6, XP)

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

Commit after insert into [message #589437]

Fri, 05 July 2013 13:31

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Hi

I am just making a audit table as well.

i have learnt the basics from here http://www.orafaq.com/forum/mv/msg/139165/381806/0/#msg_381806

My problem is that after inserting into audit table if i issue commit command then the table + unsaved data present on the form is also saved.

What i want is that i issue a command which save only inserted record in audit table, and should NOT save data present on the data entry form. (which will be saved later by other method/button).

Please guide.

Thanks

Report message to a moderator

Re: Commit after insert into [message #589438 is a reply to message #589437]

Fri, 05 July 2013 13:43

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

Senior Member

Use pragma autonomous_transaction

Report message to a moderator

Re: Commit after insert into [message #589439 is a reply to message #589438]

Fri, 05 July 2013 14:43

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Respected cookiemonster.

I will search how to use pragma autonomous_transaction in forms triggers.

Meanwhile i have seen FORMS_DDL('COMMIT') is accomplishing my objective.

Report message to a moderator

Re: Commit after insert into [message #589572 is a reply to message #589439]

Mon, 08 July 2013 15:49

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

I made a procedure in forms 6 with the following code:

PROCEDURE Delete_audit IS
 pragma autonomous_transaction ;
begin
	
	 
insert into myaudit_invs (source, invdate) values ('Deleting Record', :inv0s.invdate);
delete_record;
  
END;

But it is giving me compilation error that "pragma autonomous_transaction is not a supported pragma".

Kindly guide me what i am missing.

I am using Oracle 10.2.0.1.0

Report message to a moderator

Re: Commit after insert into [message #589574 is a reply to message #589572]

Mon, 08 July 2013 16:07

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

Senior Member
Account Moderator

I suppose that Cookiemonster meant that you should do auditing on a database level (i.e. using a stored procedure or a database trigger), not in a form.

Report message to a moderator

Re: Commit after insert into [message #589576 is a reply to message #589574]

Mon, 08 July 2013 16:15

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Respected Littlefoot

it is sad that i am failed to give true understanding to Cookiemonster.
I respect his response and would like to deviate from his suggestion at the same time.

So, i want to use FORMS_DDL('COMMIT'). Do you think it will create any problem? (for me, it seems to be ok)

Report message to a moderator

Re: Commit after insert into [message #589577 is a reply to message #589576]

Mon, 08 July 2013 16:50

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

Senior Member

FORMS_DDL('COMMIT') issues a commit to the db without kicking off standard forms save processing, so it won't apply changes in datablocks to the db. However, if the form has already made uncommitted changes to the DB (via the post command for example, or a dml statement in a trigger) then this will commit them.

pragma autonomous_transaction will only commit the changes made by the procedure using the pragma and nothing else.

My answer remains - use pragma autonomous_transaction. It's designed for this purpose.

Report message to a moderator

Re: Commit after insert into [message #589596 is a reply to message #589577]

Tue, 09 July 2013 01:49

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

This is what i need exactly. I dont need standard forms saving process while using FORMS_DDL('COMMIT'). I am not using POST command and no other DML statement in pipeline. Hence a perfect solution for me.

cookiemonster wrote on Tue, 09 July 2013 02:50

FORMS_DDL('COMMIT') issues a commit to the db without kicking off standard forms save processing, so it won't apply changes in datablocks to the db. However, if the form has already made uncommitted changes to the DB (via the post command for example, or a dml statement in a trigger) then this will commit them.

I want to learn, how to use pragma autonomous_transaction. The form is not compiling the procedure. This mean that i have to make DB level procedure. Kindly guide me that then i have to pass "forms current value" to DB procedure to accomplish my objective ? am i tight in my thinking?

Quote:

pragma autonomous_transaction will only commit the changes made by the procedure using the pragma and nothing else.

My answer remains - use pragma autonomous_transaction. It's designed for this purpose.

Report message to a moderator

Re: Commit after insert into [message #589597 is a reply to message #589596]

Tue, 09 July 2013 01:54

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

Senior Member
Account Moderator

Here is an example I created some time ago, which shows how to use pragma autonomous_transaction. Have a look.

You'd pass :inv0s.invdate as a parameter to a procedure.

Report message to a moderator

Re: Commit after insert into [message #589670 is a reply to message #589597]

Tue, 09 July 2013 16:09

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

Littlefoot, cookiemonster.. Thank you very much. You are really shinning star of orafaq (along with djmartin and barbara). Plus many others too obveiously Smile

Report message to a moderator

Previous Topic:	Time in List Item
Next Topic:	How to change image resolution in oracle forms.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 01:16:39 CST 2025