Home » Developer & Programmer » Forms » Forms6i - PRE-INSERT and PRE-UPDATE issue
Forms6i - PRE-INSERT and PRE-UPDATE issue [message #429880] Fri, 06 November 2009 01:33 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello,

Download the form and script inside the form (script trigger in form level)

First run the script in the database, then only you can debug the program.

Steps to follow.

Method I:
1. Run the form. Press F8. Now the cursor is in first record.
2. Press F6 and F4 (inserts and duplicates). In the 2nd record change the Owner seq "86" to "86.01" and change the owner ref " * " to "5.6", then clear the remaining four columns.
3. Follow step 2 and change 86.01 to 86.02 and 5.6 to 5.7, then clear the remaining column.
4. Save it. It works fine.

Method II:
1. Run the form. Press F8. Now the cursor is in first record.
2. Press F6 and F4 (inserts and duplicates). . In the 2nd record change the Owner seq "86" to "86.01" and change the owner ref " * " to "5.6", then clear the remaining four columns.
3. Go to 3rd record and change the owner ref " * " to 5.7.
4. Save it. It shows error as Serial_n column is null.
5. Exit without saving and comment the Pre-update trigger and save the program.
6. Follow the step 1, step 2, step3.
7. Save it. It saves the records.

So the problem is in pre-insert or pre-update. What's the problem?

Hope you can understand. Thanks.

-Karthik
  • Attachment: est_itm.fmb
    (Size: 60.00KB, Downloaded 322 times)
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #429898 is a reply to message #429880] Fri, 06 November 2009 03:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
4. Save it. It shows error as Serial_n column is null.

No, it says "Transaction complete: 2 records applied and saved".
I did that twice so - it wasn't me.

./fa/6971/0/
  • Attachment: frm_at.PNG
    (Size: 22.59KB, Downloaded 2191 times)

[Updated on: Fri, 06 November 2009 03:42]

Report message to a moderator

Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430093 is a reply to message #429898] Sun, 08 November 2009 04:15 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi,

It works in Forms5.0, but not in Forms6i.
I didn't try in Forms9i or 10g.
Can you please try it out in Forms6i?
Refer the attachment.

-Karthik
  • Attachment: Error_6i.jpg
    (Size: 61.88KB, Downloaded 232 times)
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430120 is a reply to message #430093] Sun, 08 November 2009 21:07 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello,

Please try in Forms6i (update patch 18).
Error message attached.

-Karthik
  • Attachment: Error_6i.JPG
    (Size: 115.28KB, Downloaded 223 times)
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430151 is a reply to message #430120] Mon, 09 November 2009 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORA-01400 is result of an attempt to insert NULL value into a column which is declared as NOT NULL.

Is "serial_n" NOT NULL? Yes, it is. Can you insert NULL into it? No, you can not.

PRE-INSERT trigger contains SELECT statement that selects "max + 1" into the "serial_n" item, but there are two facts one could mention:
  1. "max + 1" principle works fine (if correctly set - which you didn't) in a single-user environment. Otherwise, it might cause DUP-VAL-ON-INDEX error (if two or more users try to insert the same "max + 1" value into a column which is designed as unique). For unique values, use Oracle object called sequence
  2. Your "max + 1" doesn't work correctly. Here's why, based on a simple example in Scott's schema (there's no department in the "dept" table whose location is "Zagreb"):
    SQL> select * from dept where loc = 'Zagreb';
    
    no rows selected

    Your query: the result is NULL (this is what you are trying to insert into the "serial_n" column):
    SQL> select max(nvl(deptno, 0)) + 1 from dept where loc = 'Zagreb';
    
    MAX(NVL(DEPTNO,0))+1
    --------------------
    
    
    SQL>

    Another way:
    SQL> select nvl(max(deptno), 0) + 1 from dept where loc = 'Zagreb';
    
    NVL(MAX(DEPTNO),0)+1
    --------------------
                       1
    
    SQL>
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430163 is a reply to message #430151] Mon, 09 November 2009 05:25 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi,

I accept, it's my mistake regarding the number generation.
Now I changed it as you mentioned.
Now the error is in PRE-UPDATE. Kindly refer the attachment.

-karthi
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430168 is a reply to message #429880] Mon, 09 November 2009 06:00 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Max length of CRTE_USERID_N is probably shorter than the value you're assigning to it.

You do realise the that oracle user names can be up to 30 characters in length?
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430272 is a reply to message #430168] Mon, 09 November 2009 20:48 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello,

Both are 6 characters.
Kindly read the thread from the beginning, because the script is also attached.

-Karthik
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430381 is a reply to message #429880] Tue, 10 November 2009 04:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have read it from the begining. Not sure why you think I haven't.

Is the code in the pre-update trigger still this:

  :upd_d := SYSDATE;
  :upd_userid_n := USER;



Becuase if it is then my suggestion before is the only possibility that makes any sense to get that error. The first line can't give it so the second must be.
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430523 is a reply to message #430381] Tue, 10 November 2009 17:46 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi Robert Cookie,

Sorry, I'm unable understand what you are trying to say.
Please explain elaborately.
Whether you tried running this program in forms6i? Whether it works for you?

-Karthik
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430524 is a reply to message #430151] Tue, 10 November 2009 17:50 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi Littlefoot,

Have you tried in Forms6i? Pls repond.

-Karthik
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430572 is a reply to message #430524] Wed, 11 November 2009 01:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I don't have Developer 6i, I first had to find a place to test; however, your form works just fine:

./fa/7004/0/

Although it is localized to my country, I guess that you'll recognize the same FRM-40400 "Transaction complete" message.
  • Attachment: frm_at_6i.PNG
    (Size: 22.00KB, Downloaded 2362 times)
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430624 is a reply to message #429880] Wed, 11 November 2009 04:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I bet it doesn't work if you use a oracle user with a name longer than 6 characters
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430628 is a reply to message #430624] Wed, 11 November 2009 04:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you did bet, you'd won.

I created a dummy user whose name is longer than 6 characters, created tables, ran the form and - yes, PRE-UPDATE failed with ORA-06502.

./fa/6103/0/, Cookiemonster!
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430663 is a reply to message #429880] Wed, 11 November 2009 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why thank you. The new crystal ball is working a treat
./fa/1659/0/

@ap_karthi - without any other information to go on I can only assume that this is your problem - you're using a user with a name longer than 6 characters.
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430884 is a reply to message #430572] Thu, 12 November 2009 20:02 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I installed form6i and patch18.

Can you both e-mail the version details, with which version you are testing it?

My version details as follows...

Forms [32 Bit] Version 6.0.8.27.0 (Production)
Oracle Toolkit Version 6.0.8.25.0 (Production)
PL/SQL Version 8.0.6.3.0 (Production)
Oracle Procedure Builder V6.0.8.21.0 Build #1459 - 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.38.0 (Production)
Oracle Tools GUI Utilities Version 6.0.8.25.0 (Production)
Oracle Multimedia Version 6.0.8.25.0 (Production)
Oracle Tools Integration Version 6.0.8.18.0 (Production)
Oracle Tools Common Area Version 6.0.8.18.0
Oracle CORE Version 4.0.6.0.0 - Production

Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #430900 is a reply to message #430884] Fri, 13 November 2009 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that version doesn't matter. This trigger would fail on ANY Forms + Database (having the same tables and running the form connected as a user whose name is longer than 6 characters).
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #431050 is a reply to message #430900] Fri, 13 November 2009 17:40 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello Mr.Littlefoot / Mr.Robert,

Version matters, because oracle fixes some bugs and releases patches. Hope you know that one.

I tried in a new machine.
Installed Forms6i only (no patch)
The version is shown below,
Forms [32 Bit] Version 6.0.8.11.3 (Production)
Oracle Toolkit Version 6.0.5.35.0 (Production)
PL/SQL Version 8.0.6.0.0 (Production)
Oracle Procedure Builder V6.0.8.11.0 Build #449 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 6.0.7.0.0 - Production
Oracle Virtual Graphics System Version 6.0.5.35.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.10.2 (Production)
Oracle Tools Common Area Version 6.0.5.32.1
Oracle CORE Version 4.0.6.0.0 - Production

There was no issue, I was able to insert as shown by you before.


Then, I installed the patch 18, the version changes as shown below,
Forms [32 Bit] Version 6.0.8.27.0 (Production)
Oracle Toolkit Version 6.0.8.25.0 (Production)
PL/SQL Version 8.0.6.3.0 (Production)
Oracle Procedure Builder V6.0.8.21.0 Build #1459 - 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.38.0 (Production)
Oracle Tools GUI Utilities Version 6.0.8.25.0 (Production)
Oracle Multimedia Version 6.0.8.25.0 (Production)
Oracle Tools Integration Version 6.0.8.18.0 (Production)
Oracle Tools Common Area Version 6.0.8.18.0
Oracle CORE Version 4.0.6.0.0 - Production

Then the issue started, I was unable to save the record.

So I'm 100% sure, it is due to version issue only.

Note: Oracle user contains only 6 character.

Kindly try after updating the patch 18. Thank you.

-Karthik
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #431513 is a reply to message #430900] Tue, 17 November 2009 18:56 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello guys,

Have you tried with new patch? Try it... Smile

Anyhow the issue is solved, by removing patch 18 and installing patch 13. Thanks for your time.

-Karthik
Re: Forms6i - PRE-INSERT and PRE-UPDATE issue [message #431622 is a reply to message #431513] Wed, 18 November 2009 10:18 Go to previous message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
So...when do you plan to move FROM version 6i...it is highly recommended.
Previous Topic: Oracle Forms Timeout
Next Topic: what happens if i dont start OC4J but use form builder
Goto Forum:
  


Current Time: Fri Dec 09 13:49:15 CST 2016

Total time taken to generate the page: 0.22043 seconds