Is it possible to specify the data type of an Oracle 8i pseudocolumn when Selecting from Dual

Question

In Oracle, it is possible to generate pseudo-colums and "pseudo-tables" (not sure if that is an official term) on the fly like this:

(SELECT 'abc' AS pseudo_col_1,  123 AS pseudo_col_2 FROM dual) pseudo_table

I am looking to do the same, while specifying the datatype of pseudo_col_1 and/or pseudo_col_2. The following seems to work in Oracle 11g, where pseudo_col_2 is specified as a CLOB datatype :

(SELECT 'abc' AS pseudo_col_1,  TO_CLOB(123) AS pseudo_col_2 FROM dual) pseudo_table

However, I need to achieve the same result on Oracle 8i, which has no TO_CLOB function.

Disclaimer: I know 8i is outdated, no need to comment on that aspect as I have zero control over this, sadly.

Answer 1

You can cast() to the type you want:

SELECT cast('abc' as varchar2(255)) AS pseudo_col_1,
       cast(123 as decimal(10, 2)) AS pseudo_col_2
FROM dual;