Skip to main content
Solved

Oracle Writer - Error with dates

  • September 6, 2018
  • 9 replies
  • 103 views
roberto
Contributor
Forum|alt.badge.img+6

Hello,

I’m getting an error that I don’t know how to handle.

Working with dates in Oracle writer, it seems that something wrong happens that convert in NULL the date provided. The date field (CREATEDTIME) is non nullable, so the workflow ends with the following error:

error was `ORA-01400: cannot insert NULL into ("CAD_OWNER"."MAPPIN"."CREATEDTIME")'

The field is of type “TIMESTAMP(0) WITH TIME ZONE”. So, using sqlExecutor, it accepts value like

'05-SEP-18 11:00:00.25 AM' and ends properly inserting the record.

I tried to do the same, placing an attribute with the same format in the Oracle writer without success. Also other dateFormatter seems not to obtain good results. I only need to insert a DateTimeNow().

Where I’m wrong?

Thanks!

Roberto

Best answer by steveatsafe

I tried to reproduce issues... But couldn't with what I know so far.  What version of FME are you using?

Here's what my attempt looked lie: 

I created a table in Oracle:

CREATE TABLE SM_TEST_DATE2  (   ID1 NUMBER  , MYCOMMENT VARCHAR2(50)  , MYDATE TIMESTAMP WITH TIME ZONE );

Then I imported the feature type into FME 2018.1.  From there I created a simple workspace and added the DateTimeStamper and set the UTC Offset to yes. This created a record in the table.  

However, if I didn't set the "UTC Offset" to "Yes" I got this error in 2018.1:

20180910151849.323846' is not an FME datetime. YYYYMMDD, HHMMSS[.X+][(+/-)zz] or YYYYMMDDHHMMSS[.X+][(+/-)zz] expected. Use a DateFormatter transformer to adjust the format of datetime strings.

In this image, you can see the error in the translation log.  Note that the transformer parameters in this image are correct and will create a valid timestamp for data type "TIMESTAMP with TIME ZONE" in FME 2018.1.

0684Q00000ArLX9QAN.png

Also, I did notice that if the UTC Offset was No, a record was still created in the table but the Date attribute (MYDATE) was NULL.  This matches what you are reporting.

Hope this helps.  

View original
Did this help you find an answer to your question?

9 replies

ebygomm
Influencer
Forum|alt.badge.img+32
  • ebygommInfluencer
  • Influencer
  • September 6, 2018

Try sending the date into the writer formatted as an FME datetime

takashi
Influencer
  • takashiInfluencer
  • September 6, 2018

I guess it's the same issue as this Q&A;: Problems to write field date in Postgis Database

takashi
Influencer
  • takashiInfluencer
  • September 6, 2018
takashi wrote:

I guess it's the same issue as this Q&A;: Problems to write field date in Postgis Database

And the same as @egomm's suggestion.
roberto
Contributor
Forum|alt.badge.img+6
  • robertoContributor
  • Author
  • Contributor
  • September 6, 2018

Hi,

Thanks for the immediate answers.

Unfortunately this doesn't solve. I will continue with formatting.

In the meantime, waiting to solve, I will use sqlExecutor.

In your experience, which is the impact in terms of performances, writing/updating/deleting approx 3000 records every 3 minutes?

Is the writer faster (I think so) than multiple SqlExecutors?

Sorry for the question based on my reduced know-how on the product.

Thanks

Regards

Roberto

roberto
Contributor
Forum|alt.badge.img+6
  • robertoContributor
  • Author
  • Contributor
  • September 6, 2018
takashi wrote:

I guess it's the same issue as this Q&A;: Problems to write field date in Postgis Database

Thanks for the immediate answers.

 

Unfortunately this doesn't solve. I will continue with formatting.

 

In the meantime, waiting to solve, I will use sqlExecutor.

 

In your experience, which is the impact in terms of performances, writing/updating/deleting approx 3000 records every 3 minutes?

 

Is the writer faster (I think so) than multiple SqlExecutors?

 

Sorry for the question based on my reduced know-how on the product.

 

Thank

 

Regards

 

Roberto

 

 

 

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • September 6, 2018

You could also solve this on the Oracle side, by creating a trigger on the table:

CREATE OR REPLACE TRIGGER trigger_name

BEFORE INSERT ON table_name

FOR EACH ROW

BEGIN

IF ::NEW.CREATEDTIME IS NUll Then

::NEW.CREATEDTIME := Sysdate;

END IF;

END;

roberto
Contributor
Forum|alt.badge.img+6
  • robertoContributor
  • Author
  • Contributor
  • September 7, 2018
erik_jan wrote:

You could also solve this on the Oracle side, by creating a trigger on the table:

CREATE OR REPLACE TRIGGER trigger_name

BEFORE INSERT ON table_name

FOR EACH ROW

BEGIN

IF ::NEW.CREATEDTIME IS NUll Then

::NEW.CREATEDTIME := Sysdate;

END IF;

END;

Erik,

 

I agree and I've done like this. Default value in the created time (sysdate) and trigger for modification. It solves my problem. Thanks.

 

 

steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • Best Answer
  • September 10, 2018

I tried to reproduce issues... But couldn't with what I know so far.  What version of FME are you using?

Here's what my attempt looked lie: 

I created a table in Oracle:

CREATE TABLE SM_TEST_DATE2  (   ID1 NUMBER  , MYCOMMENT VARCHAR2(50)  , MYDATE TIMESTAMP WITH TIME ZONE );

Then I imported the feature type into FME 2018.1.  From there I created a simple workspace and added the DateTimeStamper and set the UTC Offset to yes. This created a record in the table.  

However, if I didn't set the "UTC Offset" to "Yes" I got this error in 2018.1:

20180910151849.323846' is not an FME datetime. YYYYMMDD, HHMMSS[.X+][(+/-)zz] or YYYYMMDDHHMMSS[.X+][(+/-)zz] expected. Use a DateFormatter transformer to adjust the format of datetime strings.

In this image, you can see the error in the translation log.  Note that the transformer parameters in this image are correct and will create a valid timestamp for data type "TIMESTAMP with TIME ZONE" in FME 2018.1.

0684Q00000ArLX9QAN.png

Also, I did notice that if the UTC Offset was No, a record was still created in the table but the Date attribute (MYDATE) was NULL.  This matches what you are reporting.

Hope this helps.  

SteveatSafe
roberto
Contributor
Forum|alt.badge.img+6
  • robertoContributor
  • Author
  • Contributor
  • September 12, 2018
steveatsafe wrote:

I tried to reproduce issues... But couldn't with what I know so far.  What version of FME are you using?

Here's what my attempt looked lie: 

I created a table in Oracle:

CREATE TABLE SM_TEST_DATE2  (   ID1 NUMBER  , MYCOMMENT VARCHAR2(50)  , MYDATE TIMESTAMP WITH TIME ZONE );

Then I imported the feature type into FME 2018.1.  From there I created a simple workspace and added the DateTimeStamper and set the UTC Offset to yes. This created a record in the table.  

However, if I didn't set the "UTC Offset" to "Yes" I got this error in 2018.1:

20180910151849.323846' is not an FME datetime. YYYYMMDD, HHMMSS[.X+][(+/-)zz] or YYYYMMDDHHMMSS[.X+][(+/-)zz] expected. Use a DateFormatter transformer to adjust the format of datetime strings.

In this image, you can see the error in the translation log.  Note that the transformer parameters in this image are correct and will create a valid timestamp for data type "TIMESTAMP with TIME ZONE" in FME 2018.1.

0684Q00000ArLX9QAN.png

Also, I did notice that if the UTC Offset was No, a record was still created in the table but the Date attribute (MYDATE) was NULL.  This matches what you are reporting.

Hope this helps.  

Steve,

 

thanks for your suggestion. I confirm that your hint solved the problem!

 

Best regards

 

Roberto

 

 

 

Reply


Helpful Members This Week

Community Stats

31,134
Posts
118,036
Replies
38,876
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.