Alter the size of aVARCHAR2 column in a VIEW

[yodaa]

I have created a View in Oracle 10g where one of the columns is a VARCHAR2(4000). I want to change that VARCHAR2 column to a VARCHAR2(255) because I'm having problems with using the field in a formula in Crystal Reports 8 since CR8 can't use fields > 255 characters in formulas.

Is it possible to select the size of the column when you create the view or alter it after creation.

Thanks,
yodaa

 

[hoinz]

Hello,

Let's assume you want to restrict column long_col to the first 255 characters.
Let's assume in your current view definition you have this:
select long_col, other_column from ...

Change it to:
select substr(long_col,1,255) as long_col, other_column from ...

hope this helps

 

[yodaa]

hoinz, thanks for the quick help..

I've tried the below query to create the view and I still get a VARCHAR(4000). I'm in a catch-22 situation and don't know where to go from here.

select "Field_A","Field_B",case
    when length("Field_C") < 200 then "Field_C"
    when length("Field_C") > 199 then substr("Field_C",length("Field_C")-200,length("Field_C"))
  end as NewName
from "Table_A"

Thanks,
yodaa

 

[SantaMufasa]

Yodaa,

First, does your organisation have a standard that says that you should use double quotes around your column/table names? I cannot see a business benefit for doing this. In fact, it causes an extra business expense by requiring that every reference to your column/table names include the double quotes.

Second, you did not follow Hoinz's suggestion for the column that you want limited to 255 characters:

select substr(long_col,1,255) as long_col, other_column from ...

is the correct way to implement the 255-character limitation.

Your altered code still results in a 4000-character max because your substr function has as its LENGTH indicator [argument 3 of the LENGTH function] length("Field_C"), which is still 4000 if you have even one entry that is 4000 characters long.

The bottom line is that you do not need the CASE statement...use Hoinz's original suggestion.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[yodaa]

SantaMufasa,

If I don't use the double quotes I get syntax errors. That's the only reason for why I'm using it. I'll see if there's a way of getting rid of those, thanks for noticing it.

My original code will never return a string > 255. The thing forcing me to use the case statement is that I need to get the last 255 (if there are that many) characters from Field_C since the data I need is to be found at the end of the string. This is the new code always returns a string < 255 characters long (verified by testing). I still get a VARCHAR2(4000). By the way, the field I'm reading the value from is a VARCHAR2(4000).

select "Field_A","Field_B",case
    when length("Field_C") < 200 then substr("Field_C",1,length("Field_C"))
    when length("Field_C") > 199 then substr("Field_C",length("Field_C")-200,length("Field_C"))
  end as NewName
from "Table_A"

Thanks,
yodaa

 

[SantaMufasa]

Yodaa,

The reason that you Oracle forces you to use double quotes is because whoever created the tables/columns did so using double quotes in the first place. As a result, every subsequent reference to the double-quoted object must a) contain double quotes, b) must be spelled the same what and c) must have absolutely identical upper- and lower-case characters as the original references.

If one creates Oracle objects without the double quotes, then so long as the spelling is correct, Oracle does not care about case and double quotes are extraneous.

(A phone call is coming in at this moment that I must take, but as soon as I'm done with it, I'll respond to the VARCHAR2(4000) issue.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[yodaa]

SantaMufasa,

Have you had the chance to look at the VARCHAR2(4000) issue?

Thankful for any help.

/yodaa

 

[Welshbird]

Yodaa,

Following on from Santa's comments re the double quotes:

I inherited a database with this issue as the person who had built it had used Toad or SQL_Tools or something similar, and had created the database via a GUI which had caused this.

We spent a few days re-naming the tables and columns on a test version then making sure that every report/query/etc still ran properly; then re-named everything on the live version.

It was time really well spent as now everything is named using a more standard convention, so writing new queries (especially Ad-hocs) is so much faster.

I'd recommend this same course of action if possible.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[yodaa]

Solved it!

select "Field_A","Field_B",substr(case
    when length("Field_C") < 256 then substr("Field_C",1,length("Field_C"))
    when length("Field_C") > 255 then substr("Field_C",length("Field_C")-255,length("Field_C"))
  end,1,255) as NewName
from "Table_A"

Thanks for all the help.

/yodaa

 

[Beilstwh]

Why not something like

SQL> create table a (str varchar2(4000));

Table created.

SQL> create view a_v as
2 select cast(substr(str,-1,255) as varchar2(255)) new_name
3 from a;

View created.

SQL> desc a;
Name Null? Type
----------------------------------- -------- ---------------
STR VARCHAR2(4000)

SQL> desc a_v;
Name Null? Type
------------------------------------ -------- ---------------
NEW_NAME VARCHAR2(255)

The negative on the substr will grab the LAST 255 characters from the field. If there are less then 255, it will only return that subset.

Bill
Oracle DBA/Developer
New York State, USA

 

[yodaa]

Beilwish,

Sweet.. Did not know you could use negation in substr(). That will make my select statement more clean. I can't create a new table as I need to have the 4000 character capacity.

Thanks for the help, all of you.

/yodaa

 

[Beilstwh]

There is no new table involved, only a view which in effect in only a stored query.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Yodaa, Bill used the CREATE TABLE... code simply to illustrate that he had created a VARCHAR2(4000) column, then to show you how he trimmed down the width of the counterpart column in the VIEW...He was not expecting you to actually create a new table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[yodaa]

Yeah, sorry about that. I didn't look at the whole picture there.

Thanks again guys, will try this first thing at work tomorrow.

/yodaa