• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

FileMaker Pro Advanced 11 interface and sync with enterprise Oracle SQL

I have a 200 field FMDB that links via Actual ODBC driver to an external enterprise data source in Oracle. FM fields include  text, number, date, and timestamp data. Manage Database > Sync correctly uploads/syncs to the same fields in Oracle with a problem exception. In Oracle, the fields can be defined as date, number, or varchar2 but not as timestamp (per the dba). The Oracle dba says that timestamp data from FM in a date field in Oracle should display out to mm/dd/yyyy HH:mm:ss but not to fractional seconds. After syncing, when I view the FM data uploaded to Oracle, the timestamp data gets truncated after the date portion. The date, number, and text/varchar2 data is accurate.

The enterprise dba suspects a Filemaker setting but only supports TOAD, SQL Developer, SQL Loader, and Access.
0
MarkJulie
Asked:
MarkJulie
  • 6
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
Is there a question here?

Oracle has had a timestamp datatype since I believe 9i.  This datatype will allow fractional seconds.

The DBA is correct that the old DATE datatype will not.

That said, what is the question?
0
 
MarkJulieAuthor Commented:
Sorry for omitting the '?' character. After syncing, when I view the FM data uploaded to Oracle, the timestamp data gets truncated after the date portion. Is there a way to upload FM Timestamp data into an Oracle Date field? I don't need fractional seconds (just HH:mm is fine). Since the dba says an Oracle DATE field can display mm/dd/yyyy HH:mm:ss, can I accomplish that with FM? A poorer solution will be to put the timestamp data into a varchar2 field as a string.

You are correct that there is an Oracle Timestamp datatype, but the sandbox staging table I have privileges to create and modify only gives me privileges with DATE, NUMBER, and VARCHAR2.
0
 
slightwv (䄆 Netminder) Commented:
>>Is there a way to upload FM Timestamp data into an Oracle Date field?

Not that I'm aware of, Oracle dates cannot store fractional seconds and does not know what to do with them when you try to convert a 'string' to a date.

You might be able to strip the fractional seconds off before you convert them to a date but I'm not sure what coding functions you have available and at that level.

>>but the sandbox staging table I have privileges to create and modify only gives me privileges

Ask for timestamp.  Why jump through a lot of hoops just because your DBAs won't let you access things the 'proper' way.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MarkJulieAuthor Commented:
Since the CIO tells the DBAs what privileges to allow, I can't kill the messenger, but you make a good point.

More basic question: can Oracle DATE even display 'mm/dd/yyyy HH:mm'? If so, what would I do on the FMPA side to massage the timestamp data to make it acceptable to Oracle?

Plan B: If I send FM Timestamp to Oracle VARCHAR2, can it later be cast to a timestamp in a SQL query?
0
 
slightwv (䄆 Netminder) Commented:
>>Oracle DATE even display 'mm/dd/yyyy HH:mm'?

Yes.  Oracle dates are stored as an internal format but it stores seconds.

How they are displayed are based on a format mask that basically turns it into a string.

Implicit type conversions are done when oracle is passed a 'string' that matches the default format mask for Oracle dates.

The default format is: DD-MON-YY
This is over-ridden with a parameter NLS_DATE_FORMAT.  This parameter can be set at various levels.

Even with these, Oracle will not like fractional seconds with DATEs.

I never suggest implicit data type conversions.

I would suggest explicitly converting types whenever possible.

To 'massage' it on the FMPA side you need to know what 'default' format the DBAs are expecting.

Oracle has a TO_DATE sql function to explicitly convert strings to dates.  Just not sure if you can use those from FMPA.
0
 
slightwv (䄆 Netminder) Commented:
>>Since the CIO tells the DBAs what privileges to allow

Been there...  Someone needs to inform the CIO that without valid/good and accurate data do you really need DBAs?

Depending on what the date/timestamp columns are used for, an argument can easily be made for the inclusion of timestamps:

-No need for additional PK columns to get around DATE limitations where fractional seconds would make records unique.

-If appA captures the data, why would you want to incure data loss because of a simple permission issue?  Oracle has had the ability to capture fractional seconds since version 9i (release around 2000...  if you make this argument, look up the exact date).

We can probably come up with more if you feel like proposing it...
0
 
MarkJulieAuthor Commented:
Yes, but we are moving now from 'How do I shoot this gun to stay alive?' to war policy decisions :)

Yes or no: Do I abandon sending timestamp data to an Oracle DATE field since all time data will be lost (minutes and seconds)?

If the time portion of the data is not lost but merely masked, does the end user (not a DBA) have access to it in SQL SELECT? If not,

Can a FM text string of the format "DD-MON-YY HH:MM" be sent to an Oracle VARCHAR2 and later cast to an Oracle timestamp by a competent DBA?

(Pardon my frustration, but the other policy I deal with is Windows-only platform support at the enterprise level (though it is SLOWLY changing).
0
 
slightwv (䄆 Netminder) Commented:
I think I should add: Oracle date datatypes ALWAYS have a time portion.  It might be zero'd out if not provided but it is always there.

>>Do I abandon sending timestamp data to an Oracle DATE field since all time data will be lost (minutes and seconds)?

Sorry but this isn't a Y/N question.  DATE's will handle hours and minutes.  Just not fractional seconds.

>> does the end user (not a DBA) have access to it in SQL SELECT?

Yes.  If the time portion of a date is stored, it is there.  It is up to those default 'masks' I mentioned earlier that dictate how it is displayed.

For example:  
select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS' from dual;
select to_char(sysdate,'MM/DD/YYYY HH:MI:SS AM' from dual;
select to_char(sysdate,'Month Year' from dual;
etc...


>>Can a FM text string of the format "DD-MON-YY HH:MM" be sent to an Oracle VARCHAR2 and later cast to an Oracle timestamp by a competent DBA?

Yes.  You can also add seconds:  'DD-MON-YY HH24:MI:SS'.  Oracle can handle just about any string to date conversion.  It just needs to know the expected format and it can convert it implicitly and/or explicitly.

>>Pardon my frustration

No apology necessary.  I'm just trying to make sure I give you a complete answer.



0
 
MarkJulieAuthor Commented:
Thorough, rapid, courteous, and neither condescending nor trivial.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.

Good luck on the future battles!  I'm a DBA now but have been on the other side of the fence so am well aware of the battles.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now