Re: To force type CHAR in a view

Well, it is true that querying string literals in a view creates a column of type CHAR, with length matching that of the literal, e.g.

CREATE OR REPLACE VIEW view_name AS
SELECT ' ' column_name
FROM dual
WHERE 1 = 0;
...results in column of CHAR (10). However, using UNION ALL to
concatenate the results with those from another query does not (perhaps surprisingly) give rise to a CHAR (10) column in the result set, e.g.

CREATE OR REPLACE VIEW view_name AS
SELECT ' ' column_name
FROM dual
WHERE 1 = 0
UNION ALL
SELECT column_name
FROM table_name;

...results in column of VARCHAR2 (10). Instead I would use the built-in
function CAST to modify the datatype of the column, e.g.

CREATE VIEW view_name AS
SELECT CAST (column_name AS CHAR (10)) column_name FROM table_name;

...results in column of CHAR (10), as required.

For info on CAST see:

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server. 920/a96540/functions15a.htm#1017549

--
Posted via http://dbforums.com