Solved

FileMaker Pro Advanced 11 interface and sync with enterprise Oracle SQL

Posted on 2011-03-07
10
1,133 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)
Comment Utility
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
Comment Utility
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)
Comment Utility
>>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
 

Author Comment

by:MarkJulie
Comment Utility
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)
Comment Utility
>>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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
Thorough, rapid, courteous, and neither condescending nor trivial.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now