blair  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: www.arikaplan.com                          <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page ">Back to Ari Kaplan's Home Page