Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How do I find records with column lenght over 128 char
coredba
post May 30 2007, 07:52 PM
Post #1


Member
**

Group: Members
Posts: 27
Joined: 27-April 07
Member No.: 8,341



I am doing a large merge of two tables and I need to find the records that are over 128 characters in lenght on the source table - how do I do this? What option do I have?

Sort the table.column where column leght is greater than 128 or something?
It's about 4 million records in the table
I need to identify how many they are and if I can rename them
It failed on inserting a 169 character into a 128 column ....

this is my error
ERROR at line 25:
ORA-12899: value too large for column "VN_DW"."SUBSCRB_DIM"."HANDSET_MODEL"
(actual: 169, maximum: 128)
Go to the top of the page
 
+Quote Post
dave
post May 31 2007, 02:30 AM
Post #2


Advanced Member
***

Group: Members
Posts: 5,207
Joined: 8-October 04
Member No.: 785



well iut tells you what column you are insert into so from your merge command you know which columns you are selecting

so yse the length() function on the column to see how big they are
Go to the top of the page
 
+Quote Post
burleson
post May 31 2007, 05:49 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



Hi,

>> I need to find the records that are over 128 characters in lenght on the source table

Try this:

select mykeyvalue, length(my_large_column)
from mytab
where length(my_large_column) > 128;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
coredba
post May 31 2007, 01:09 PM
Post #4


Member
**

Group: Members
Posts: 27
Joined: 27-April 07
Member No.: 8,341



Hmmm - for some reason it does not trap the column over 128 chars.....
and the numbers doesn't add up if I use < , >= which I would expect it to do...
hmmmmm

SQL> select HANDSET_MODEL , length(HANDSET_MODEL) from SUBSCRB_DIM where length(HANDSET_MODEL) > 128 ;
no rows selected

SQL> select * from SUBSCRB_DIM where length(HANDSET_MODEL) > 128 ;
no rows selected

SQL> select count(*) from SUBSCRB_DIM;
COUNT(*)
----------
3595084

SQL> select count(*) from SUBSCRB_DIM where length(HANDSET_MODEL) < 128 ;
COUNT(*)
----------
1619703

SQL> select count(*) from SUBSCRB_DIM where length(HANDSET_MODEL) >= 128 ;
COUNT(*)
----------
0
Go to the top of the page
 
+Quote Post
burleson
post May 31 2007, 04:02 PM
Post #5


Advanced Member
***

Group: Members
Posts: 11,706
Joined: 26-January 04
Member No.: 13



Hi,

Are there right space paddings?

Maybe try rtrim?

select length(rtrim(handset_model)) . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
coredba
post Jun 1 2007, 06:33 PM
Post #6


Member
**

Group: Members
Posts: 27
Joined: 27-April 07
Member No.: 8,341



U R the man :-)

I found it - there was only one row out of +3 milion that failed my merge and should be easy to fix - 1000 thanks

SQL> select count(*) from SUBSCRB_DIM where length(rtrim(handset_model)) > 128 ;
COUNT(*)
----------
1

SQL> select length(rtrim(handset_model)) from SUBSCRB_DIM where length(rtrim(handset_model)) > 128 ;
LENGTH(RTRIM(HANDSET_MODEL))
----------------------------
169
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd November 2014 - 02:10 AM