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


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

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:                          <->
<->             email:                    <->

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