When I try to update certain tables in Database Explorer, I get the following error:
ORA-12899: value too large for column “SCHEMA”.“TABLE_NAME”.“COLUMN_NAME” (actual: 2, maximum: 1)
The column indicated in the error isn’t even the column I am updating. This doesn’t occur on every table, and I can’t see anything in similarities in the tables it does occur on.
This is an urgent issue as several of our data processing team members cannot update data as necessary.
Any input would be appreciated.
What is the column’s type? What is the value in that column?
Aleksey,
The column is a CHAR(1) and the current value is 'Y'.
In this scenario, if we attempt to update any other field in this table we get this error. In all cases, we are making no changes to the field we are getting the error for.
Thanks,
Jon King
On Tue, Dec 10, 2013 at 2:01 PM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
What is the column's type? What is the value in that column?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Hi Jon,
Unfortunately I cannot reproduce the issue.
I created a table and edited values in Sql Editor and in Object Explorer. I attached a script to create the table and populate it. I did not see any issue with editing the column_3.
The only case when I get the same error as you is when I explicitly added a space to the Column_2.
Could you please create the table and try to reproduce the error by editing this table?
Thanks
Aleksey
Test.sql (665 Bytes)
Aleksey,
I was able to resolve this by completely removing all traces of DataPoint and reinstalling.
Jon
On Wed, Dec 11, 2013 at 10:33 AM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Hi Jon,
Unfortunately I cannot reproduce the issue.
I created a table and edited values in Sql Editor and in Object Explorer. I attached a script to create the table and populate it. I did not see any issue with editing the column_3.
The only case when I get the same error as you is when I explicitly added a space to the Column_2.
Could you please create the table and try to reproduce the error by editing this table?
Thanks
Aleksey
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Aleksey,
This problem has resurfaced. It does not happen on every table, so trying to reproduce it by following your steps in your previous post and hoping it happens is pointless. This only occurs on version 3.4, so it is clearly a regression in Data Point. I would just install a previous version, but this user is on Windows 8.1 and versions prior to 3.4 do not work. Data Point is somehow adding an additional character to the field before sending it to oracle to update causing this problem. If necessary, I can send you a screenshot of the ddl for the table, the data I am trying to update and the error screen.
Let me know what is the next step, this is occurring on a key employees workstation making it very hard to complete her duties. Please advise.
Jon
Hi Jon,
Could you please send the table DDL and tell us what Oracle client you use?
In the meantime you could try the following
Open Define Custom Statement to Edit dialog
and exclude the column from the list of updatable columns.
Aleksey,
Attached is the DDL for the table. The column that seems to be causing the problem in this table is the HPSI_SALES_REP column. The problem is not limited to this table, but in each instance, the column is a char(1).
We are using the same Oracle client across all of our installations, and it is the 11g R2 (32-bit).
I made the change to the updatable columns as you suggested and it resolves the issue for that table, but obviously prevents updating that column if needed.
Thanks for your quick response to this issue.
Jon
On Tue, Dec 17, 2013 at 11:26 AM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Hi Jon,
Could you please send the table DDL and tell us what Oracle client you use?
In the meantime you could try the following
Open Define Custom Statement to Edit dialog
and exclude the column from the list of updatable columns.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Hi Jon,
Unfortunately I was not able to reproduce it. We’ll continue looking at the issue but before we spend more time I’d like to ask couple questions
SET SALES_REP_ID = :f1, NAME = :f2, MES_FLAG = :f3, HPSI_SALES_REP = :f4, RVP = :f5, PHONE = :f6, EMAIL = :f7, START_DATE = :f8, HAS_COMMISSION_BASE = :f9, IN_PRUDENT_BUYER = :f10
WHERE SALES_REP_ID = :f11
Aleksey
Aleksey,
In response to your questions:
Could you please verify that you use 32 bit Oracle client? We introduce 64 bit support in TDP 3.4 so it could cause the issue. To do it right click on the Oracle connection and select Property. What is the name of Oracle’s Current home?
It is the 32 bit client, I installed it myself. the path is C:\Oracle\Product\11.2.0\client_1 and the Home name is OraClient11g_Home1
Do you have other triggers on the table or only SALES_REP_IN_PRUDENT_BUYER_TRG?
No, that is the only one. (Recall that this works on every machine without TDP 3.4, so it is not Oracle related, this table has existed in it's current form for 10+ years)
Could you execute the following update statement from the SQL Editor? You will have to provide values for all bind variables.
Updating tables from the sql editor works perfectly
UPDATE ORA_USER.SALES_REP
SET SALES_REP_ID = :f1, NAME = :f2, MES_FLAG = :f3, HPSI_SALES_REP = :f4, RVP = :f5, PHONE = :f6, EMAIL = :f7, START_DATE = :f8, HAS_COMMISSION_BASE = :f9, IN_PRUDENT_BUYER = :f10
WHERE SALES_REP_ID = :f11
How critical is the ability to update the column from the grid?
This is very critical, the user with this issue updates table data in several tables daily and we don't want to create DML for every update she needs to make, this is a small table with only a few fields, other tables with this issue are much larger and would require creation of multiple update statements. The Data Processing team in general prefers to use the grids because it is easier to filter down to the records you want to update and then update dynamically and then update as necessary
Would it be possible to install additional program on your PC to trace the issue? Will you have time to do it?
That would be no problem, send me the details and we will get it going.
Thanks,
Jon
On Tue, Dec 17, 2013 at 2:20 PM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Hi Jon,
Unfortunately I was not able to reproduce it. We’ll continue looking at the issue but before we spend more time I’d like to ask couple questions
- Could you please verify that you use 32 bit Oracle client? We introduce 64 bit support in TDP 3.4 so it could cause the issue. To do it right click on the Oracle connection and select Property. What is the name of Oracle’s Current home?
- Do you have other triggers on the table or only SALES_REP_IN_PRUDENT_BUYER_TRG
- Could you execute the following update statement from the SQL Editor? You will have to provide values for all bind variables.
UPDATE ORA_USER.SALES_REP
SET SALES_REP_ID = :f1, NAME = :f2, MES_FLAG = :f3, HPSI_SALES_REP = :f4, RVP = :f5, PHONE = :f6, EMAIL = :f7, START_DATE = :f8, HAS_COMMISSION_BASE = :f9, IN_PRUDENT_BUYER = :f10
WHERE SALES_REP_ID = :f11
- How critical is the ability to update the column from the grid?
- Would it be possible to install additional program on your PC to trace the issue? Will you have time to do it?
Aleksey
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Thank Jon for response.
Let’s install Toad for Oracle Trial to trace the update statement that causes the issue.
Please download Toad for Oracle from this link http://www.quest.com/toad-for-oracle/software-downloads.aspx . Select the second row as Application Development.
After you downloaded and installed the Toad for Oracle run it.
Now launch TDP, connect to Oracle, go to Data grid for a table that causes error, and include the column in the list of the Updatable columns.
Switch back to Toad for Oracle and go to Database| Monitor | SQL Tracker. In the opened form select TDP and start monitoring by pressing the Start Monitoring button or using Process| Start Monitoring menu item. Now we will get all SQL statements that TDP sends to Oracle.
Switch back to TDP and try to reproduce the error. At the same time you should get in Monitoring window something similar to the following screenshot
Could you post the screenshot with the statement that causes the error?
Thanks
Aleksey
Aleksey,
We are still having this issue, I thought it was related to the columns being defined using byte rather than char, but I have since changed all of the problematic tables to use char and I am still getting the errors. Attached is a screenshot of sqltracker.
If I copy the query from sql tracker and run it, it runs fine in sql editor, and if I edit the table using Toad for Oracle rather than Toad Data Point it also works.
Jon
On Wed, Dec 18, 2013 at 9:10 AM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Thank Jon for response.
Let’s install Toad for Oracle Trial to trace the update statement that causes the issue.
Please download Toad for Oracle from this link http://www.quest.com/toad-for-oracle/software-downloads.aspx . Select the second row as Application Development.
After you downloaded and installed the Toad for Oracle run it.
Now launch TDP, connect to Oracle, go to Data grid for a table that causes error, and include the column in the list of the Updatable columns.
Switch back to Toad for Oracle and go to Database| Monitor | SQL Tracker. In the opened form select TDP and start monitoring by pressing the Start Monitoring button or using Process| Start Monitoring menu item. Now we will get all SQL statements that TDP sends to Oracle.
Switch back to TDP and try to reproduce the error. At the same time you should get in Monitoring window something similar to the following screenshot
Could you post the screenshot with the statement that causes the error?
Thanks
Aleksey
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Could you please repost the screenshot?
It’s too small and I cannot read it.
Thanks
Aleksey
Aleksey,
I have attached it this time. Download and open it so you can see it full size.
Jon
On Fri, Jan 17, 2014 at 11:09 AM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Could you please repost the screenshot?
It’s too small and I cannot read it.
Thanks
Aleksey
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
Thanks Joe,
I’m sorry but I still cannot see well.
For me it looks like you got the error for a different field – INVOICE_ (I cannot see full name) and the size is 13-14 characters.
Also when we update from a grid we always use bind variables and statement should look like on my screenshot from 18 Dec 2013 at 5:09pm. Here you get something else.
Aleksey
Aleksey,
As I indicated in a previous email, this error occurs on several tables, not just the table I sent in the previous sample. In regards to always using bind variables, I am not sure why mine would look different, but I am updating from a grid in Data Point, and that is what is showing up in SqlTracker, so I am not sure what your point is unless there is a setting you are referring to. All I know is that I update the grid and get the error that is show both in sqltracker and in Data Point.
I followed your steps exactly and that is the result.
Attached is a clearer image I split it so you can see the entire text.
Jon
On Fri, Jan 17, 2014 at 12:17 PM, Aleksey Bazhenov bounce-AlekseyBazhenov@toadworld.com wrote:
Reply by Aleksey Bazhenov
Thanks Joe,
I'm sorry but I still cannot see well.
For me it looks like you got the error for a different field – INVOICE_ (I cannot see full name) and the size is 13-14 characters.
Also when we update from a grid we always use bind variables and statement should look like on my screenshot from 18 Dec 2013 at 5:09pm. Here you get something else.
Aleksey
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
I tried this using the original DDL provded and the maximum values for all varchars and cannot reproduce this issue. What is the encoding of this database? UTF-8, unicode, etc? And what is your OCI client set to?
Debbie,
The database is UTF16 (NLS_NCHAR_CHARACTERSET = AL16UTF16 NS_LANG = WE8MSWIN1252).
The pc's NS_LANG is WE8MSWIN1252.
We use the same version of the Oracle client on all of our 12 Data Point installations with the exact same settings. This table has existed in our database for 8+ years and worked perfectly until we updated to Data Point 3.4 and now several of our tables cannot be updated using Data Point. I installed Toad for Oracle beside Data Point on the same computer and it has no problem updating data in the schema browser similar to how it is done in Data Point.
I believe the only way to change the charset is when a call is made to OCIEnvCreate() and indicating a different charset than that which the computer uses as it's default and that would have to be done by calling OCI directly (I am sure Data Point is not doing this as it uses DevArt's Oracle client)
Thanks for your assistance with this issue.
Jon King
On Fri, Jan 17, 2014 at 2:06 PM, Debbie Peabody bounce-Debbie_Peabody@toadworld.com wrote:
Reply by Debbie Peabody
I tried this using the original DDL provded and the maximum values for all varchars and cannot reproduce this issue. What is the encoding of this database? UTF-8, unicode, etc? And what is your OCI client set to?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum
Flag this post as spam/abuse.
--
Jon King
V. P. Enterprise Systems Development
HPSI
Cell: (801) 885-3301
Office: (949) 250-4774 ext. 106
