Solved

FileMaker Pro Advanced 11 interface and sync with enterprise Oracle SQL

Posted on 2011-03-07
10
1,172 Views
Last Modified: 2012-08-13
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
Comment
Question by:MarkJulie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35057786
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
 

Author Comment

by:MarkJulie
ID: 35057977
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058022
>>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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:MarkJulie
ID: 35058130
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058209
>>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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058291
>>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
 

Author Comment

by:MarkJulie
ID: 35058443
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35058561
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
 

Author Closing Comment

by:MarkJulie
ID: 35058792
Thorough, rapid, courteous, and neither condescending nor trivial.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058847
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

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question