Re: SQL question: Views

blair <blair_at_mda.ca> writes:

>Is there any way to construct a view in SQLplus with derived columns
>where you get to determine the length of the datatype. It seems
>that in the construction of our view our dervied columns are being
>set as varchar2(2000). However our columns only use up 10 spaces.
>We get a problem with buffer overflow, and we don't want to reduce
>arraysize and we are operating on Oracle7 so MAXDATA doesn't make
>any differenc.
>Any Answers
>Blair

Blair,

There is a way to do this. Suppose the base table is as follows:

desc TEST_TABLE;

A    VARCHAR2(10),
B    VARCHAR2(2000),
C    VARCHAR2(2000)

To create a view where each column is length 10, do the following:

CREATE VIEW test_view AS
SELECT a, SUBSTR(b,1,10) B, SUBSTR(c,1,10) C FROM test_table;

This will create a three-column table with types VARCHAR2(10). Be sure to include column aliases in the view to correspond with the base table's column names.

Good luck.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->