Hello Robert - The use of TO_DATE and TO_TIMESTAMP is to not have to know what the specific date format is on the Source Database. If TO_DATE & TO_TIMESTAMP cannot be used, is there another method I could use that does the same thing? I have no issues getting the date; just how to format it and send it so it is not a specific date format that could change. 

I have a link generator written that is reading Source (Oracle) tables and that uses the %Library.ResultSet class. for each it gets column name, type, maxsize, precision, lais, readonly, required & data type name and builds Cache Linked Table.

Are you referring to this logic where I need to apply scale? I do not see any SCALE references in logic.

 if (jdbc) {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFieldsJ")
 } else {
  s rs= ##class(%Library.ResultSet).%New("%GTWCatalog:SQLFields")
 s sc = rs.Execute(gateway, table, schema)
 if ($$$ISERR(sc)) {
  d $system.OBJ.DisplayError(sc)
  quit sc
 s tname = schema _ "." _ table
 s cols = ""
 while rs.Next() {
  s externalcolumnname=rs."COLUMN_NAME",
    columnname = externalcolumnname,
    columnalias="" /* defaults to externalcolumnname */,
    readonly=0 /* hardcoded */,
   if prikey="",(externalcolumnname["_cd")!(externalcolumnname["_id") SET prikey=externalcolumnname
  s col=$LTS($LB( externalcolumnname,
    datatypename ),"^")
  s cols = cols_$LB(col)
 s cols=$LTS(cols,"@")
 d rs.Close()

Hi Robert,

When I review the existing DDL Mapping for NUMBER, the datatype states: %Library.Numeric(SCALE=0)

This does not make any sense my generated SQL Table code(.cls & .int) is validating with EXTERNALSQLTYPE=2 and $isvalidnum(%val,2,,)

I thought SCALE=0 allowed any decimal value but it appears somewhere else there is logic that is defaulting this SCALE to 2.

After discussing this with Intersystems, they stated that the existing Cache Object Methods and/or SQL Queries do not change when switching from an ODBC to JDBC connection, which was my main concern. After switching, though, I had to recompile/regenerate the SQL Object Classes to change to the new SQL Gateway being used. So thanks everyone for responding. All is working and I appreciate your input & help!

Hello Kevin Chan,

I know how to setup jdbc in SQL Gateway; that is not the Question. The Question is can I code in Cache Object Script to an external Oracle database using jdbc SQL Gateway in place? And if I can, do you or anyone else have examples of doing this. I have Cache Object Script working with an External Oracle database using ODBC but wanted to see how to do this (or replace this) with jdbc?