Oracle. Replace or remove data from one column

Oracle 9i
What sql command can i use to drop or replace the data in one column of a table for one of the entrys in that table.
When the data in this column is split across two segments and if i use something like
update *TABLE*  set  *COLUMN* = NULL where name = *ROW NAME*
When i look at my application that uses this database it complains about 'nullnull' entry being invalid. This error message does not occur if data does not extend across 2 segments

What i am trying to acheive is to replace everything in the COLUMN field with new data (but not delete the row)
stopmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
can you update it  with new value like this:

update *TABLE*  set  *COLUMN* = "new_value"  where name = *ROW NAME*
0
shru_0409Commented:
update orders set bin = ( select binfrom from zones where orders.BIN = zones.BINTO )

may be this help u or pls explain with example..
0
awking00Information Technology SpecialistCommented:
Can you post the relevant table structure with some sample data and what you expect the end result to look like?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

stopmAuthor Commented:
To explain a bit better as suggeted by 'awking00' i have attached a sample of  'TABLE1'
The ABC name entry has 3 rows.
Line 4 in spread sheet (Type 7 , Segment  0) has in the DATA column multiple entrys. When this column has more entrys then its max characters a second line/ segement (line 2 in spreadsheet) is created and additional data is placed here. Segment 2 being the extension of segment 1

So what i need to do is update all the values in the DATA column (where name = 'ABC123' and 'TYPE' = 7) on a regular basis via sql (rather then using the application).  
The data may only require 1 segment rather then 2 segments. If  i leave the 2nd segment  blank (NULL) then the application complains.
If i only have 1 segment  eg.  i delete the segment 2 row but then try and do an 'update table' but i have more data then can fit in segment 1 row i will get error message saying too much data.
How do i get it to extend across 2 or even 3 segments if required or remove them if not required.
Hope this is clearer

Table-example.XLS
0
stopmAuthor Commented:
Another comment.
I would prefer not to have to alter tablespace size for the data column (unless i can do it for just this row of data) as i dont want to impact the way the application currently works.
0
stopmAuthor Commented:
Here is the error message i get if text in  DATA column is tool large 'ORA-01401: inserted value too large for column"
0
mrjoltcolaCommented:
Why are you limiting the VARCHAR to 254?

This seems like a self-imposed application design problem. A VARCHAR can be 4000 characters.

The other other error you are discussing: 'ORA-01401: inserted value too large for column

is simple, you are trying to append more of those data elements in a column that is already full.

If there is no application requirement for a 254 character line, I would alter the table. Would 1000 characters be sufficient?

alter table t modify data varchar2(1000);

Where t is your table name.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stopmAuthor Commented:
Hi mrjoltcola
Thanks for the reply. I was trying to avoid altering column size if possible as the java application splits the data over segments when it updates this table. So i wanted to replicate this same action when i updated this table directly with my script.  eg split the data into 254 bit segements and do multiple updates as required for how ever many segments i need. Is it possible to do this?
If not then i will have to look at increasing colmn size.
0
mrjoltcolaCommented:
The alter is such a minor thing, I am not sure why you want to avoid it.

The error you are getting is simply telling you you tried to append or insert more data into a column than could fit.

Looking at your sample data, I am not sure why the design was chosen to split across multiple records in the first place. I would have used a VARCHAR2(4000) or I would have used a child table to hold the attributes, instead of packing them into a single field.

I note that your sample data has spaces in it, is there a reason for that?

U || VWM || XYZ

Trim the spaces out and you get:

U||VWM||XYZ

which gives you more space. Also, why are you using double bars ||, is it possible to use a single?

Thats all I can suggest. Good luck.

0
anand_2000vCommented:
Dont't leave it blank. Enter a Space or other character which your application can recognize and ignore.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.