Solved

FileMaker Pro Advanced 11 interface and sync with enterprise Oracle SQL

Posted on 2011-03-07
10
1,140 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
 

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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to get sorting resultset 15 68
Apple licensing for business 3 45
Get the parent node - XMLTYPE 9 56
use lov values 2 32
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 shows how to recover a database from a user managed backup
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

910 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

26 Experts available now in Live!

Get 1:1 Help Now