Re: To force type CHAR in a view

cf wrote:
> (excuse me, my inglish is not very good)
>
> When i create the view V1 I will want to create a column NEW_COL1 CHAR(10)
> and not VARCHAR2(10)
>
> Because I have a utility which at the time of the joint of the view V1 and a
> table with a column in char(10),
> detects the difference in type.
>
> SQL> create table t1 (col1 varchar2(8));
>
> SQL> desc t1
> Name Type
> ---------------------- -----------------
> COL1 VARCHAR2(8)
>
> SQL> CREATE OR REPLACE VIEW V1 AS
> select col1,
> rpad(col1,10,' ') new_col1
> from t1
> /
>
> SQL> desc v1
> Name Type
> --------------------- -----------------
> COL1 VARCHAR2(8)
> NEW_COL1 VARCHAR2(10)
>
> I will want to create a column NEW_COL1 ==> CHAR(10)
>
> Thanks in Advance..
> With My Best Regards...
> Christophe
>
>

I don't think you can do it (directly, at least), since the syntax of the view statement does not allow you to specify data types (such as CHAR).

Your idea of using a function does not work because all the built-in character functions (except upper() and lower() sometimes) return a VARCHAR2: concat(), initcap(), lpad(), rpad(), ltrim(), rtrim(), replace(), soundex(), and translate(). Both upper() and lower() will return a CHAR if a CHAR is passed as an argument, but will return a VARCHAR2 otherwise.

Of course, taking your function idea to the next step, you might be able to create a 'hack' that will work. Can you create a view on the other table (where you have a CHAR datatype)? Something like:

create view v2 as
select rtrim(char_column_name) varchar2_column_name from other_table;

That will convert your CHAR column to a VARCHAR2 in the view and your join won't detect a difference in datatype. You would join table t1 with view v2 in your utility.

Actually, I don't know if this little 'hack' will even work. You'll have to try it with your 'utility' program. Received on Mon Oct 07 2002 - 12:13:22 CDT