Home » Developer & Programmer » Forms » Greetings and a Question! (Form Hangs after Update)
Greetings and a Question! (Form Hangs after Update) [message #265236] Wed, 05 September 2007 19:52 Go to next message
Alvarado
Messages: 6
Registered: September 2007
Junior Member
Hello everyone, this is my first post in this forum, so I hope I don't make a fool of myself, I'll try to be brief and clear on my problem.

I've recently started dabbling with Oracle Forms here at work.
I'm using Oracle Forms Developer 6i on a windows XP professional machine, and according to the "about" menu these are the versions for the developer (copy/pasted)

Forms [32 Bit] Version 6.0.8.13.0 (Production)
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Oracle Toolkit Version 6.0.8.12.1 (Production)
PL/SQL Version 8.0.6.0.0 (Production)
Oracle Procedure Builder V6.0.8.12.1 Build #520 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 6.0.7.1.0 - Production
Oracle Virtual Graphics System Version 6.0.5.37.0 (Production)
Oracle Tools GUI Utilities Version 6.0.5.35.0 (Production)
Oracle Multimedia Version 6.0.5.34.0 (Production)
Oracle Tools Integration Version 6.0.8.13.0 (Production)
Oracle Tools Common Area Version 6.0.5.32.1
Oracle CORE Version 4.0.6.0.0 - Production

As reference I'm using the book "Oracle Developer, Advanced Forms and Reports" by Peter Koletzke, and I followed the instructions in the book to make a Data Block based on a Stored Procedure, with a little bit of my own modifications.

I have setup the Data Block using the wizard, and I set up canvas layout, I link to the appropriate procedures in my PL/SQL package (Select, Insert, Update, Lock) and then I test the form.

The main problem is this, I can query the initial data, I can modify it and then save it, but if I query the data again, the Form Runtime hangs and I get the "Not Responding" message, in which point I have to end the task. This is not a fluke, I can reproduce this event any time, and it always crashes the form.

The way my form is setup is like this:
Select.- This procedure queries a view I created earlier, and in addition I include a column called Save_Flag which is not linked to any column in a table like this:
Select A.*, 'N' Save_Flag from A
.
I use this column as a check box, so that the user can mark which records he wants to save.

Lock.- The lock procedure checks the save_flag, if it's set to 'Y' then it will execute the query
Select row_id from A where row_id = ref_cursor.row_id for update


Update.- The update procedure also has an IF clause that checks the Save_Flag, if it's set to 'Y' then it executes the Update query, if it's set to no, then nothing happens.

Insert.- The form doesn't allow inserts, but the procedure is basically a dummy that does nothing.

I know the updates are working, because I can query the data directly with PL/SQL developer to see the changes.

I'm guessing that there's a problem with the Lock procedure tying up the records and it's not permitting the second query to go through (maybe it hangs because it's waiting for the rows to get unlocked?).

I hope somebody can help me with this issue, I've spent 2 days already going through Google, Metalink and now OraFAQ with no solution.

Thanks a lot for your time!

[Updated on: Wed, 05 September 2007 19:57]

Report message to a moderator

Re: Greetings and a Question! (Form Hangs after Update) [message #265486 is a reply to message #265236] Thu, 06 September 2007 07:26 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

This is only a stab in the dark - but are you using the where current of statement for the update? Only we had a problem with this statement and apparently (so we were told) there are a number of know issues with it in the latest version of forms (our version is newer than yours but we migrated directly from forms 4.5).

We have replaced these with a cursor select of the rowid and then an update using that.



Re: Greetings and a Question! (Form Hangs after Update) [message #265531 is a reply to message #265486] Thu, 06 September 2007 10:33 Go to previous messageGo to next message
Alvarado
Messages: 6
Registered: September 2007
Junior Member
hi scorpio, thanks for the reply.

Actualy, I'm not using the where current statement, my update procedure looks like this


procedure Update_Proc(p_record in out my_table) is
begin
     for v_ct in 1 .. p_record.count loop
     
          if p_record(v_ct).save_flag = 'Y' then

               update A
               set x = p_record(v_ct).x
               where y = p_record(v_ct).y;

          end if;               

     end loop;

end;


my_table is a data type I defined as a collection of records that I also defined for the form (I followed the examples in Koletzke's book)

I'm going to try using the where current statement, see if I can fit it in. I'll post an update after I try it out.


Re: Greetings and a Question! (Form Hangs after Update) [message #265608 is a reply to message #265236] Thu, 06 September 2007 13:03 Go to previous messageGo to next message
Alvarado
Messages: 6
Registered: September 2007
Junior Member
Minor update. I couldn't get the Where Current Of thing to work because the update procedure uses a table and not a cursor Sad Also, after some testing I found out that I don't actually have to save the changes to the data to make the form crash, all I have to do is modify the data in the form and that's enough to make the next query hang.

After fumbling a bit with the debugger I realized that the Lock Trigger is executed as soon as I change any value in the form, so I'm guessing that either the records get locked by the form but they don't get released by the form, or the locking procedure is wrong, although I've tested with a dummy lock procedure that doesn't do anything and it still hangs the form, so I'm leaning more to the form not releasing the records.

[Updated on: Thu, 06 September 2007 13:04]

Report message to a moderator

Re: Greetings and a Question! (Form Hangs after Update) [message #265723 is a reply to message #265236] Fri, 07 September 2007 03:05 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I think you misunderstood me - I said not to use the Where Current Of as it causes problems.

Anyway - I had a quick look at the forms help topic Locking and it may well be that you have a conflict between the forms default locking process and the lock in your PL/SQL package.

It might be worth having a read, as it explains about forms use of implicit locking, block locking mode and when locks are released (I'm assuming your help file is the same as mine here).

I would go with the idea that forms is not releasing the lock it has. If you are locking and commiting outside of the form (via your PL/SQL package) then forms may still have it's lock in place, hence the reason you can't query.

I'd probably start by looking at one of two methods
1. Allow forms to handle the locking or
2. After you've completed the processing and saved the data do a clear_block(no_validate) which will flush the block and should let you do the query.

The second one might be worth a go as a test?


Re: Greetings and a Question! (Form Hangs after Update) [message #265845 is a reply to message #265236] Fri, 07 September 2007 10:18 Go to previous message
Alvarado
Messages: 6
Registered: September 2007
Junior Member
Thank you, I'll give that a shot!
Previous Topic: Print Data from forms..
Next Topic: WHEN_VALIDATE_ITEM gets triggered two times
Goto Forum:
  


Current Time: Thu Dec 08 18:21:21 CST 2016

Total time taken to generate the page: 0.22909 seconds