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
Solved

FileMaker Pro Advanced 11 interface and sync with enterprise Oracle SQL

Posted on 2011-03-07
10
1,155 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
  • 6
  • 4
10 Comments
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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 76

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 76

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
A lot of new and distinct gadgets are making their appearance every other day. The latest gadget that has wooed the attention of all gadget lovers and non gadget lovers alike is the Smartwatch. This tiny gadget is capable of offering live access to …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

791 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