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?

1

2 Answers

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
1

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.