Anyone,

When trying to create a synonym for an oracle table that contains this datatype:

TIMESTAMP(3) WITH LOCAL TIMEZONE

The process fails. After much troubleshooting, we discovered that if I create the table
without the timestamp columns, the synonym creation works fine.

It gives a FOC1400 and FOC1405 error.

Anyone had this issue before?

Thanks,
--wg

This message has been edited. Last edited by: Kerry,

Manual "Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS, Version 7 Release 6.8" (DN4500931.1208) states that TIMESTAMP WITH LOCAL TIME ZONE is supported. I would open a case with Tech Support.

Thanks Francis,

I did open a case and have since discovered that I can create the synonym via a focexec, but not thru the webconsole (which we prefer).

However, the webconsole will create a synonym on the same db adapter, if I remove the TIMESTAMP(3) WITH LOCAL TIME ZONE columns.

So, this is something very sneaky.

--wg

Did you ever resolve this?

We appear to be running into the same issue with the results of a prepared statement containing a TIMESTAMP(0) WITH LOCAL TIME ZONE field:

SQL SQLORA PREPARE SQLSTMT FOR
SELECT CAST(current_timestamp AS TIMESTAMP(0) WITH LOCAL TIME ZONE) AS logdate FROM dual
;
END
?FF SQLSTMT

 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
 L    (FOC1405) SQL PREPARE ERROR.
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLSTMT
 NO DATA FILE IS CURRENTLY ACTIVE

Of course the query runs just fine from Oracle SQL Developer:

 LOGDATE
-----------------------------
 04-09-12 13:36:53,000000000

If you really only need the date-part of the timestamp anyway, you can convert the value in the SQL query:

SQL SQLORA PREPARE SQLSTMT FOR
WITH tmp AS (
	SELECT CAST(current_timestamp AS TIMESTAMP(0) WITH LOCAL TIME ZONE) AS aDate
	FROM dual
)
SELECT
	to_char(aDate, 'YYYY-MM-DD') AS date1,
	trunc(aDate) AS date2
FROM tmp
;
END
?FF SQLSTMT

Which results in:

 FILENAME=  SQLSTMT
 DATE1         E01           A10V
 DATE2         E02           HYYMDS