Hi all,
I have a table in which I enlarged a column from varchar2(200) to varchar2(2000). This column is processed in triggers and in those triggers it is passed as an argument to several procedures. I modified all triggers, procedures so that they can work with the new datatype. And it workts. For one day.
Every morning when I insert or update a value into that varchar2(2000) column the first time it gives an ORA-065102: numeric or value error: character string buffer too small.
When I recompile the before insert/update for each row triggers behind the table this column belongs too, the error disappears. The moment I do the recompilation every single object in the database is valid, including the triggers.
Any idea what might be the problem here?
(Oracle Database 10g Enterprise Edition Release 10.2.0.2.0)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
- Everybody is getting that error
- It's a Oracle Forms application
- About the values being too long, this is what I do:
- I open the form where I enter the value of exactly 2000 characters
- I get the error, but leave the screen just as it is, without changing anything
- I recompile the (valid) triggers
- I go back to the application and press the Save button again -> now it works, the value of length 2000 characters is saved
That is strange. Some things you coudl try include:
1. Create a copy of the table (and triggers and procedures) with the column length longer than 2000 bytes, and try inserts and updates. If they succeed, check the actual length of the values that got saved.
2. Add an exception-handler section to your trigger so when this error happens, it writes the :new.column_value to an ASCII log file that you can review to check the length. The trigger must then do a "raise" so the transaction still fails.
3. Change the column length (and the legnth of any dependant variables in triggers and procedures) to something longer, like maybe 2010 bytes, to see if that succeeds. But if you do this, you cannot easily shrink the column back down to 2000.
Business Accounts
Answer for Membership
by: markgeerPosted on 2007-05-25 at 06:46:40ID: 19156789
How is the datatype for the variables for that column defined in the triggers and procedures? Is it hard-coded, like: varchar2(2000), or dynamic, like: [table_name].[column_name]
Who gets the error in the morning, just you, or others too? Are you sure that the values being inserted or updated are less than 2000 bytes?
What kind of application is this: Oracle Forms, VB, .net, something else?