Re: Oracle Views with PL/SQL Calls alway return VARCHAR(2000), why ?

In article <3294FB54.28DA_at_ix.netcom.com>, thielm <thielm_at_ix.netcom.com> writes
>LS,
>
>I ran into a little problem using views in oracle.
>
>Imagine the following simplified scenario
>
>CREATE OR REPLACE
>FUNCTION Test RETURN CHAR AS
>BEGIN
> RETURN 'ABC';
>END;
>/
>
>CREATE OR REPLACE
>VIEW TestView1 AS
>SELECT (Test) TEST from dual;
>
>CREATE OR REPLACE
>VIEW TestView2 AS
>SELECT ('ABC') TEST from dual;
>
>In TestView1 the field TEST is represented as a VARCHAR2(2000) where in
>TestView2 the field TEST is a CHAR(3). Reporting tools like crystal
>reports or any
>other tool using ODBC cannot handle more than VARCHAR2(256), i.e. we can
>not use
>views that make any PL/SQL calls.
>
>Does anyone know of a way to force oracle to return a CHAR type or a
>VARCHAR2(256)
>or less in this scenario ?
>
>Thanks
>
>Mike.

CREATE OR REPLACE
VIEW TestView1 AS
SELECT SUBSTR(Test,1,3) TEST from dual;

Then :

desc TestView1

Name                    Null?   Type
----------------------- ------- ----
TEST                            VARCHAR2(3)

-- 
Chris Dipple, Production DBA, Royal Bank of Scotland