'ORA-02070: database %s%s does not support %s in this context

I have an Oracle database from which I am selecting a table from a remote postgres database, pg. The column mydate is of type date.

select to_char(mydate,'mm-dd-yyyy') from "pg_table"@pg

For the above query, I am getting an error like

ORA-02070: database PG does not support TO_CHAR in this context
*Cause: The remote database does not support the named capability in the context in which it is used.
*Action: Simplify the SQL statement.

Why is this happening?

sqloraclepostgresql


Solution 1:[1]

Try to use to_date to first make it an oracle date then use to_char to transform

      select to_char(to_date(mydate),'mm-dd-yyyy' ) as "Date"
     from "pg_table"@pg

Solution 2:[2]

Some functions are not supported by the linked server driver to the backend server. I'm having this happen right now where regex_replace is not supported by links to MSSQL. This is just an unfortunate byproduct of using a linked server.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Himanshu
Solution 2 Chance

Related Questions