Cast 1

[hilbrink]

What is wrong with the following:
select cast(sysdate as varchar2(20)) from dual;

Te error message i get is:

select cast(sysdate as varchar2(20)) from dual
*
ORA-00905: missing keyword

?? please help
(What i try to do is to cast several things (the above is just an example) but don't want to use the to_date, to_number etc. functions but the cast function)

 

[lewisp]

What version of the RBDMS are you running? I've tried this on 8.1.7 and it's ok, but obviously doesnt work on 7.3.2.

 

[alexhu]

Nor 8.0.5

SQL> select cast(sysdate as varchar2(20)) from dual;
select cast(sysdate as varchar2(20)) from dual
*
ERROR at line 1:
ORA-00905: missing keyword

Alex

 

[hilbrink]

I'm using 8.0.6

 

[karluk]

According to my Oracle 8.0.6 documentation, the CAST function exists in this version of Oracle, but doesn't support the type of conversion you're trying to do. The "cast...as" syntax requires a collection type after "as". It looks as if you will need to use a different conversion method.

 

[CRoberts]

What are do trying to do? Usually, to convert a date into a character, you would use TO_CHAR, with an optional format mask.

SELECT TO_CHAR(Sysdate, 'MM/DD/YYYY HH24:MI:SS') Today
FROM Dual;

 

[hilbrink]

The situation is that we have an application that runs on different database servers such as Oracle, SQL Server, Interbase.

In order to communicate with these databases we use SQL. First standard SQL and translate this to the SQL dialect that is needed. Our standard SQL includes the cast function which can be used in Interbase and SQL Server. According to the syntax description of Oracle it could be used too.
It is easier (and faster in the application) for us to translate the standard SQL cast function direct to a Oracle cast function, because that way there are not as many changes needed as when translating to 'to_number', 'to_char', etc.

I hope the problem is clear now, and perhaps somebody has a solution.

 

[lewisp]

The following is from the Oracle Metalink site:

"This is a new feature in Oracle 8i. In 8i its possible to cast built-in datatype or collection type to another built-in datatype or collection type. This feature is not available in 8.0.5."

....and also 8.0.6 I would assume.